Not supported exception with TruncateTime entity framework function

Question

I'm getting an exception when trying to use TruncateTime() in my linq expresion and I'm not sure why?

Here is my statement

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

and here is the exception

System.NotSupportedException: This function can only be invoked from LINQ to Entities.

at System.Data.Entity.DbFunctions.TruncateTime(Nullable`1 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.WhereListIterator`1.MoveNext()

at System.Collections.Generic.List'1..ctor(IEnumerable`1 collection)

at System.Linq.Enumerable.ToList[TSource](IEnumerable`1 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

Answer 1

System.NotSupportedException: This function can only be invoked from LINQ to Entities.

You have this exception because the result of yogaProfile variable is not an IQueryable which is used by Linq To Entities to perform query on server side. To make your variable to be an IQueryable you need to remove the extension method First() you use on the query at the end and replace it with Take(1).

So instead of

var yogaProfile = dbContext.YogaProfiles.Where(i => i.ApplicationUserId == userId).First();

You should have this:

var yogaProfile = dbContext.YogaProfiles.Where(i => i.ApplicationUserId == userId).Take(1);

Note that with the first statement you are dealing with Linq To Objects when you perform Linq on the variable. By removing First() extension method and replace it with Take() extension method you'll perform Linq To Entites.