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

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

Answer 2

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 3

I managed to rewrite this in Lambda as well and make it async. Seems to be working the same.

var temp = await _context.Events.Where(x => x.IsActive)
            .Include(a => a.Activity)
            .GroupBy(x => x.DateTimeFrom.Date)
            .Select(g => new { EventDate = g.Key, Events = g.ToList() }).ToDictionaryAsync(x => x.EventDate, x => x.Events);

Answer 4

EF Core 2.0 now supports mapping database functions to static methods on your context.

Check out the section 'Database scalar function mapping' here - https://learn.microsoft.com/en-us/ef/core/what-is-new/

Answer 5

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 6

EF Core 3.0

I finally found an answer that works. The issue is that I was wanting to Group by Date on a DateTime column in the database.

The key for me was to use the EF.Property function. This allows the class to have the DateTime property which is used for adding that level of data, but below allowed me to then redefine it as a Date. However.. I suspect if I decalared the property on the class, it would have already allowed me to use the .Date function which it was not allowing me todo.

So the solution may rather be to define the property on the model, or use the below to define it in your query.

EF.Property(s, "dt").Date

Full code

var myData = _context.ActivityItems
                            .GroupBy(a => new { nlid = a.lid, nsd = EF.Property<DateTime>(a, "dt").Date })
                            .Select(g => new
                            {
                                g.Key.nlid,
                                g.Key.nsd,
                                cnt = g.Count()
                            });

Answer 7

In my case, it is working in this way instead of DbFunctions.TruncateTime or EntityFunctions.TruncateTime-

result = _context.ActivityItems.Where(a =>  a.DateTimeFrom.Value.Date == paramFilter.DateTimeFrom.Value.Date);

It converts date first in the server side in where condition and then compare with parameter date value-

WHERE CONVERT(date, [a].[DateTimeFrom]) = @__paramFilter_DateTimeFrom_Value_Date_0