使用Entity Framework的TruncateTime函数触发NotSupportedException问题

我在LINQ表达式里用DbFunctions.TruncateTime()的时候遇到了异常，代码如下：

var yogaProfile = dbContext.YogaProfiles.Where(i => i.ApplicationUserId == userId).First(); 
var yogaEvents = yogaProfile.RegisteredEvents.Where(j => (j.EventStatus == YogaSpaceEventStatus.Active || j.EventStatus == YogaSpaceEventStatus.Completed) 
    && DbFunctions.TruncateTime(j.UTCEventDateTime) > DbFunctions.TruncateTime(yesterday) 
    && DbFunctions.TruncateTime(j.UTCEventDateTime) <= DbFunctions.TruncateTime(todayPlus30) ).ToList();

异常信息：

System.NotSupportedException: This function can only be invoked from LINQ to Entities.
at System.Data.Entity.DbFunctions.TruncateTime(Nullable1 dateValue) at YogaBandy2017.Services.Services.YogaSpaceService.<>c__DisplayClass9_1.b__1(YogaSpaceEvent j) in C:\Users\chuckdawit\Source\Workspaces\YogaBandy2017\YogaBandy2017\Yogabandy2017.Services\Services\YogaSpaceService.cs:line 256 at System.Linq.Enumerable.WhereListIterator1.MoveNext()
at System.Collections.Generic.List'1..ctor(IEnumerable1 collection) at System.Linq.Enumerable.ToList[TSource](IEnumerable1 source)
at YogaBandy2017.Services.Services.YogaSpaceService.GetUpcomingAttendEventCounts(String userId) in C:\Users\chuckdawit\Source\Workspaces\YogaBandy2017\YogaBandy2017\Yogabandy2017.Services\Services\YogaSpaceService.cs:line 255
at YogaBandy2017.Controllers.ScheduleController.GetEventCountsForAttendCalendar() in C:\Users\chuckdawit\Source\Workspaces\YogaBandy2017\YogaBandy2017\YogaBandy2017\Controllers\ScheduleController.cs:line 309

这个报错的原因很明确：你调用DbFunctions.TruncateTime的时机不对。

当你执行dbContext.YogaProfiles.Where(...).First()的时候，EF已经把yogaProfile对象从数据库加载到内存里了，这时候yogaProfile.RegisteredEvents是一个内存中的集合（属于LINQ to Objects的范畴）。而DbFunctions.TruncateTime是EF专门为LINQ to Entities设计的方法——它的作用是把C#代码转换成对应的SQL日期截断函数，只能在还没执行的数据库查询（也就是IQueryable类型的查询）里使用，直接在内存集合上调用就会抛出这个异常。

给你两种解决思路：

方案一：把查询逻辑推到数据库执行（推荐） #

直接在EF的IQueryable层面完成关联和过滤，这样整个查询都会转换成SQL在数据库端执行，DbFunctions.TruncateTime就能正常工作，而且性能更好（因为数据库会提前过滤掉不需要的数据，减少内存加载量）：

var yogaEvents = dbContext.YogaProfiles
    .Where(i => i.ApplicationUserId == userId)
    .SelectMany(i => i.RegisteredEvents) // 关联查询RegisteredEvents
    .Where(j => 
        (j.EventStatus == YogaSpaceEventStatus.Active || j.EventStatus == YogaSpaceEventStatus.Completed) 
        && DbFunctions.TruncateTime(j.UTCEventDateTime) > DbFunctions.TruncateTime(yesterday) 
        && DbFunctions.TruncateTime(j.UTCEventDateTime) <= DbFunctions.TruncateTime(todayPlus30)
    )
    .ToList();

方案二：在内存中处理日期截断 #

如果你确实需要先把yogaProfile加载到内存（比如后续还要用到它的其他属性），那就在内存里用.NET原生的DateTime.Date属性来实现截断时间的效果——这个属性对内存中的DateTime对象是完全可用的：

var yogaProfile = dbContext.YogaProfiles.Where(i => i.ApplicationUserId == userId).First();
// 提前截断日期，避免重复计算
var truncatedYesterday = yesterday.Date;
var truncatedTodayPlus30 = todayPlus30.Date;

var yogaEvents = yogaProfile.RegisteredEvents
    .Where(j => 
        (j.EventStatus == YogaSpaceEventStatus.Active || j.EventStatus == YogaSpaceEventStatus.Completed) 
        && j.UTCEventDateTime.Date > truncatedYesterday 
        && j.UTCEventDateTime.Date <= truncatedTodayPlus30
    )
    .ToList();

内容的提问来源于stack exchange，提问作者chuckd