-1

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.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
M. Rogers
  • 367
  • 4
  • 18
  • Well - are you using EF 6.4.4 on the "classic", full (Windows-only) .NET framework - or do you want to use **EF Core** v5.0.9 on the **.NET Core** framework ? Both at the same time won't work well - pick one and use that exclusively! – marc_s Aug 24 '21 at 15:53
  • 1
    Why not do `e.DueDt.Value < thirtyDaysInTheFuture` where `thirtyDaysInTheFuture= DateTime.Now.AddDays(30);` That should work with any version of EF. – juharr Aug 24 '21 at 16:05
  • `e.DueDt < thirtyDaysInTheFuture` is not just easier, it's also a *lot* faster because it can use indexes. Calculating the difference using `DATEDIFF` prevents the use of indexes. – Panagiotis Kanavos Aug 24 '21 at 16:08

2 Answers2

2

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

Panagiotis Kanavos
  • 120,703
  • 13
  • 188
  • 236
0

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();
Serge
  • 40,935
  • 4
  • 18
  • 45
  • 2
    That will force a full table scan. Calculate the actual cutoff instead and compare it against the field. This will work with *every* database – Panagiotis Kanavos Aug 24 '21 at 16:02
  • @PanagiotisKanavos I am sorry but I am talking about MS SQL server only. DateDiff is a native function of MS Sql server. It works much better then any another syntax – Serge Aug 24 '21 at 16:06
  • 2
    No it doesn't. What you posted is a very common bug resulting in non-SARGable queries. In short, you can't use indexes if you filter using a function's results. – Panagiotis Kanavos Aug 24 '21 at 16:07