Entity Framework 6 SqlFunctions DateDiff not implemented in nuget package

.net c# entity entity-framework linq

Question

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 NotSupportedExceptions. 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.

1
3
4/4/2014 10:03:51 PM

Popular Answer

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();
17
11/16/2017 10:34:05 AM


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow