How to Compare two dates in LINQ in Entity Framework in .NET 5

Question

using DbFunctions = System.Data.Entity.DbFunctions;

Using the above namespace I tried below ways but nothing worked. This code is throwing an exception which states...

public async Task<int> SomeFunction(){
var count = await _context.Drives.CountAsync(c => DbFunctions.TruncateTime(c.CreatedOn) == DateTime.Today);

var count1 = await _context.Drives.Where(c => DbFunctions.TruncateTime(c.CreatedOn) == DateTime.Today).CountAsync();

var data =  _context.Drives.Where(c => !c.IsDeleted).ToList();

//This throw an exception 
// "This function can only be invoked from LINQ to Entities."
var count2 = data.Count(x=> DbFunctions.TruncateTime(c.CreatedOn) == DateTime.Today)
}

The LINQ expression 'DbSet().Where(d => DbFunctions.TruncateTime((Nullable)d.CreatedOn) == (Nullable)DateTime.Today)' could not be translated

Can someone help me out how can I compare two dates (only date not with time) in LINQ and Entity Framework?

Answer 1

The problem is, that you are applying DbFunctions.TruncateTime to data. data is of type List<Drive> on not IQueryable<Drive>, because you already called ToList(). So you Count would be evaluated in memory and not on the database.

If that is really, what you want, then you can just use it like this:

var count2 = data.Count(x=> x.CreatedOn.Day == DateTime.Today);

If you want to invoke your query on the database, then you can use

var count2 = _context.Drives.Count(x=> !x.IsDeleted && DbFunctions.TruncateTime(x.CreatedOn) == DateTime.Today);

Depending on the versions and database, you are using, solution 1 may also work on the database

var count2 = _context.Drives.Count(x=> !x.IsDeleted && c.CreatedOn.Day == DateTime.Today);

Another solution would be to just use a time range, which will definetily work on the database and in memory

var start = DateTime.Today;
var end = start.AddDays(1);

var count2InMemory = data.Count(x => x.CreatedOn >= start && x.CreatedOn < end);
var count2InDatabase = _context.Drives.Count(x=> !x.IsDeleted && x.CreatedOn >= start && x.CreatedOn < end);

A final side-note: You should use async and await when you query the database.