I just downloaded the nuget packages for entity framework 6 (EntityFramework.dll Version 6.0.0.0 Runtime Version v4.0.30319 and EntityFramework.SqlServer Version 6.0.0.0 Runtime Version v4.0.30319) to my 4.5 Framework solution (it is in multiple projects)
In one of the projects I have both referenced and tried to implement the DateDiff
Sql Function
RepositoryFactory.CreateReadOnly<MyEnity>()
.Where(at => at.AccessedDate.HasValue
&& at.CreatedDate >= startDate && at.CreatedDate <= endDate
&& System.Data.Entity.SqlServer.SqlFunctions.DateDiff("ss", at.CreatedDate, at.AccessedDate.Value) > 0)
.GroupBy(at => at.Participant.Id)
.Select(at => new TimeOnSite
{
TimeSpentInSeconds = at.Sum(p => p.AccessedDate.Value.Subtract(p.CreatedDate).TotalSeconds),
ParticipantId = at.Key
}).ToList();
To my surprise, I received a not supported exception when running the code. When I looked at the source in System.Data.Entity.SqlServer.SqlFunctions
, all of the DateDiff
functions had no implementation, they only throw NotSupportedException
s. An example is below:
/// <summary>
/// Returns the count of the specified datepart boundaries crossed between the specified start date and end date.
/// </summary>
///
/// <returns>
/// The number of time intervals between the two dates.
/// </returns>
/// <param name="datePartArg">The part of the date to calculate the differing number of time intervals.</param><param name="startDate">The first date.</param><param name="endDate">The second date.</param>
[SuppressMessage("Microsoft.Usage", "CA1801:ReviewUnusedParameters", MessageId = "datePartArg")]
[SuppressMessage("Microsoft.Usage", "CA1801:ReviewUnusedParameters", MessageId = "endDate")]
[SuppressMessage("Microsoft.Usage", "CA1801:ReviewUnusedParameters", MessageId = "startDate")]
[DbFunction("SqlServer", "DATEDIFF")]
public static int? DateDiff(string datePartArg, DateTime? startDate, DateTime? endDate)
{
throw new NotSupportedException(Strings.ELinq_DbFunctionDirectCall);
}
I also tried using System.Data.Linq.SqlClient.SqlMethods.DateDiffSecond
which is implemented, but get the following exception:
LINQ to Entities does not recognize the method 'Int32 DateDiffSecond(System.DateTime, System.DateTime)' method, and this method cannot be translated into a store expression.
I was looking in the wrong place. The correct function was in System.Data.Entity.SqlServer.SqlFunctions. The correct query turned out to be
RepositoryFactory.CreateReadOnly<MyEntity>()
.Where(at => at.AccessedDate.HasValue
&& at.CreatedDate >= startDate && at.CreatedDate <= endDate
&& SqlFunctions.DateDiff("ss", at.CreatedDate, at.AccessedDate.Value) > 0)
.GroupBy(at => at.Participant.Id)
.Select(at => new TimeOnSite {
TimeSpentInSeconds = at.Sum(p =>
SqlFunctions.DateDiff("ss", p.CreatedDate, p.AccessedDate.Value)
),
// p.AccessedDate.Value.Subtract(p.CreatedDate).TotalSeconds),
ParticipantId = at.Key
})
.ToList();