Comparing dates in LINQ Entity Framework

Question

I'm trying to run the following query:

List<EPCRA_Events> x = 
    _dbContext.EPCRA_Events
              .Where(e => e.DueDt.HasValue && 
                          (e.DueDt.Value - DateTime.Now).Days < 30)
              .ToList();

But I keep getting an error

The LINQ expression ...xxx... could not be translated

After reviewing other posts I've seen a common solution is using DbFunctions class. However, after using Nuget to import Entity Framework 6.4.4 and Microsoft.EntityFrameworkCore 5.0.9, I don't have access to that class. Furthermore, the Entity class isn't in the System.Data namespace.

Any info on how I can get this query to run, or what I'm doing wrong using DbFunctions would be appreciated. Thanks.

Answer 1

Even if the query could be translated, it would be a bad query because it would have to calculate the difference for every single row before filtering. Indexing wouldn't help, because indexes are built from the stored values.

The solution is to calculate the cutoff date in advance and compare with the field directly.

This code will find records in the past 30 days

var cutoff=DateTime.Now.AddDays(-30);

List<EPCRA_Events> x =  _dbContext
              .Where(e => e.DueDt > cutoff)
              .ToList();

While this will find records up to 30 days in the future :

var cutoff=DateTime.Now.AddDays(30);

List<EPCRA_Events> x =  _dbContext
              .Where(e => e.DueDt < cutoff)
              .ToList();

While this will return records in the next 30 days, including today:

var cutoff=DateTime.Now.AddDays(30);

List<EPCRA_Events> x =  _dbContext
              .Where(e => e.DueDt>=DateTime.Today &&  e.DueDt < cutoff)
              .ToList();

This will ignore NULLs and use any indexes that cover DueDt

Answer 2

if you use MS Sql Server you can try this

var dateTimeNow=DateTime.Now;

var x = _dbContext.EPCRA_Events.Where(e => EF.Functions.DateDiffDay(e.DueDt, dateTimeNow) < 30).ToList();