DbFunctions.TruncateTime LINQ equivalent in EF CORE

Question

I have the following functioning LINQ in my .net app

    public ActionResult Index()
    {
        Dictionary<DateTime?, List<Event>> result;
        result = (from events in db.Events.Include("Activity")
                      where events.IsActive
                      group events by DbFunctions.TruncateTime(events.DateTimeFrom) into dateGroup
                      select new { EventDate = dateGroup.Key, Events = dateGroup.ToList() }).ToDictionary(x => x.EventDate, x => x.Events);

        return View(result);
    }

When I use this in EF Core, I can't use DbFunctions. How can I rewrite this to make it work in Microsoft.EntityFrameworkCore ? I am using SQLite if that makes a difference.

Answer 1

In EF6 DbFunctions.TruncateTime is used instead of DateTime.Date property because for some reason the later is not supported.

In EF Core the former is not needed simply because DateTime.Date now is recognized and translated correctly.

group events by events.DateTimeFrom.Date into dateGroup

Unfortunately there is no documentation (yet) of what is supported, so as a general rule of thumb, always try the corresponding CLR method/property (if any) and check if it translates to SQL and how.

Answer 2

To use DbFunctions in ASP.NET CORE You must create an object.

var DbF = Microsoft.EntityFrameworkCore.EF.Functions;

Now you can easily use it.

var now = DateTime.Now;

int count = db.Tbl.Count(w => DbF.DateDiffDay(now, w.RegisterDate) <= 3);

more detail on github

Answer 3

DbFunctions are not supported yet for EF Core. However you can use "Raw Sql Queries".

You can find documentation of "Raw Sql Queries" here

And also you can track here for DbFunctions for EF Core