How can I change my API controller to return results without throwing an error?

Question

I am using .NET Core 5 / Entity Framework Core as a backend for my app.

I have a controller from which I want to return experiments that are set to expire soon.

The logic is, I want the controller to return experiments that are set to expire in 31 days or 7 days.

Visual Studio builds the project with no errors, but when I try to hit the controller, I get this error:

Message=The LINQ expression 'DbSet()
.Where(p => (int)(p.DateOfExpiration - __currentDate_0).TotalDays == 31 || (int)(p.DateOfExpiration - __currentDate_0).TotalDays == 7)' could not be translated.
Either rewrite the query in a form that can be translated, or switch to client evaluation explicitly by inserting a call to 'AsEnumerable', 'AsAsyncEnumerable', 'ToList', or 'ToListAsync'.
Source=Microsoft.EntityFrameworkCore

Here is my controller:

// GET: api/ExpiringExperiments
[HttpGet("ExpiringExperiments")]
public  List<Experiment> ExpiringExperiments()
{
    var currentDate = DateTime.Now;

    var experiments =  _context.Experiment
        .Include(p => p.ExperimentType)
        .Include(p => p.Location)
        .Where(p => ((int)(p.DateOfExpiration - currentDate).TotalDays == 31) || ((int)(p.DateOfExpiration - currentDate).TotalDays == 7))
        .ToList();

    return experiments;
}

I tried following its suggestions to use AsEnumerable, but that throws a new error when trying to build the project:

Cannot implicitly convert Systems.Collections.Generic.IEnumerable to Systems.Collections.Generic.List

I'm not sure what to do here.

Is there something in my query I'm doing wrong?

Thanks!

Does this answer your question? [LINQ to Entities for subtracting 2 dates](https://stackoverflow.com/questions/570858/linq-to-entities-for-subtracting-2-dates) — gunr2171, Feb 25 '22 at 15:54
@gunr2171 do you mean the part about using `DateTime.Now.Subtract`? — SkyeBoniwell, Feb 25 '22 at 15:56
No, I mean using EntityFunctions, which is now called DbFunctions. — gunr2171, Feb 25 '22 at 15:58
@gunr2171 Oh ok, I haven't heard of those, but I can try to see if that may fix my issue — SkyeBoniwell, Feb 25 '22 at 15:59
@gunr2171 for some reason Visual Studio can't find DbFunctions even though I have this `using Microsoft.EntityFrameworkCore`. Is this a nuget package? — SkyeBoniwell, Feb 25 '22 at 16:05
`EF.Functions.DateDiffDay`, requires reference to `Microsoft.EntityFrameworkCore.SqlServer` — Svyatoslav Danyliv, Feb 25 '22 at 16:28
`TotalDays` is not translatable to the SQL, So you have to get difference between dates in other way: [documentation](https://learn.microsoft.com/en-us/dotnet/api/microsoft.entityframeworkcore.sqlserverdbfunctionsextensions.datediffday?view=efcore-6.0) — Svyatoslav Danyliv, Feb 25 '22 at 18:01

score 1 · Accepted Answer · answered Feb 25 '22 at 19:43

try this

var currentDate = DateTime.Now;

return  _context.Experiment
        .Include(p => p.ExperimentType)
        .Include(p => p.Location)
        .Where(p => EF.Functions.DateDiffDay(currentDate,p.DateOfExpiration) == 31) 
        || EF.Functions.DateDiffDay(currentDate,p.DateOfExpiration) == 7)
        .ToList();

How can I change my API controller to return results without throwing an error?

1 Answers1