实体框架 6 SqlFunctions DateDiff 未在 nuget 包中实现

Question

我刚刚将实体框架 6（实体框架.dll版本 6.0.0.0 运行时版本 v4.0.30319

和 EntityFramework.SqlServer 版本 6.0.0.0 运行时版本 v4.0.30319）的 nuget 包下载到我的 4.5 框架解决方案（它在多个项目中）

在其中一个项目中，我既引用又尝试实现DateDiff Sql 函数

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();

令我惊讶的是，我在运行代码时收到了不受支持的异常。当我在System.Data.Entity.SqlServer.SqlFunctions中查看源代码时，所有DateDiff函数都没有实现，它们只抛出NotSupportedException s。下面是一个示例：

    /// <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);
}

我也尝试使用已实现的System.Data.Linq.SqlClient.SqlMethods.DateDiffSecond，但出现以下异常：

LINQ to Entities 无法识别方法"Int32 DateDiffSecond（System.DateTime， System.DateTime）"方法，并且此方法无法转换为存储表达式。

Answer 1

我找错了地方。正确的函数在System.Data.Entity.SqlServer.SqlFunctions中。正确的查询结果是

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();