I need to go through all my chemicals and send out a warning to users if the expiration date is either 2 days away or 15 days away. I have it set like that so the warning is sent out twice. Once 2 weeks before it expires, and once 2 days before it expires.

So I have this loop:

foreach (var chemical in _context.Chemical.Where(c => (c.DateOfExpiration - DateTime.Now).TotalDays == 15 || (c.DateOfExpiration - DateTime.Now).TotalDays == 2))
{
    // send out notices
    var base = _context.Base.Find(chemical.BaseId);
    var catalyst = _context.Catalyst.Find(chemical.CatalystId);

    _warningService.SendSafetyWarning(chemical, base, catalyst.Name);
}

But whenever I run it, I get this error:

System.InvalidOperationException: 'The LINQ expression 'DbSet<Chemical>
.Where(p => (p.DateOfExpiration - DateTime.Now).TotalDays == 15 || (p.DateOfExpiration - DateTime.Now).TotalDays == 2)'
could not be translated.

I'm not sure why it's giving me this error when I run it.

Is there a better way of doing what I'm trying to do?

Thanks!

CodePudding user response：

You can declare the variable before and then pass it to your expression. Like DateOfExpiration == nextFifteenDays …..

var nextFifteenDays = DateTime.Now.Adddays(15);


var nextTwoDays = DateTime.Now.AddDays(2);

Or you can use EntityFunctions.Diffdays

Reason for the error is well explained by @neil that DateTime.Now will keep on changing, so can’t be used.

CodePudding user response：

There are two solutions to this problem.

I'll first explain why it is throwing an error. EF is converting your code to an SQL Expression. When working with Expression you cannot parse in any C# code and expect it to work. EF needs to be able to translate it to SQL. That is why you are getting the exception saying that it can't translate your code.

When working directly in the Where clause on the DbSet or the DbContext you are actualy talking to the Where method for an IQueryable. Which has a parameter of Expression<Func<bool, T>> where T is your DbSet<T> type.

You either need to simplify your query so it can be translated to SQL. Or you can convert your IQueryable to an IEnumerable.

When you want to run your query on the SQL server, you need to use the first approach. But when you can run your query on the client you can use the second approach.

The first approached as mentioned by vivek nuna is using EntityFunctions. It might not be enough for your use case, because it has limited functionality.

The statement made that DateTime.Now is different on each iteratoion is not true. Because the query is converted once, it isn't running the query on every iteration. It simply doesn't know how to translate your query to SQL.

https://docs.microsoft.com/en-us/dotnet/api/system.data.objects.entityfunctions?view=netframework-4.8

The second approach means adding AsEnumerable after your DbSet. This will query all your data from your SQL server, and evaluate in C#. This is usually not recommended if you have a large data set. Example:

var chemicals = _context.Chemical.AsEnumerable(); // this will get the complete collection

chemicals.Where(i => i.Value == true); // everything will work now

And answer to a "better" approach. It's a bit cleaner code:

If you have navigation properties for your Base and Catalyst you can also include this in your query.

Note that you can still query server side before pulling everything client side using a Where on the _context.Chemical and then including and calling AsEnumerable.

var chemicals = _context.Chemical.Include(i => i.Base).Include(i => i.Catalyst).AsEnumerable();

foreach (var chemical in chemicals.Where(i => true)) // set correct where clause
{
    _warningService.SendSafetyWarning(chemical, chemical.Base, chemical.Catalyst);
}

CodePudding user response：

[DateTime.TotalDays])https://docs.microsoft.com/en-us/dotnet/api/system.timespan.totaldays?view=net-5.0) returns a double.

You might have more luck changing your code to:

foreach (var chemical in _context.Chemical.Where(c => ((int)(c.DateOfExpiration - DateTime.Now).TotalDays == 15) || ((int)(c.DateOfExpiration - DateTime.Now).TotalDays == 2)))
{
    // send out notices
    var base = _context.Base.Find(chemical.BaseId);
    var catalyst = _context.Catalyst.Find(chemical.CatalystId);

    _warningService.SendSafetyWarning(chemical, base, catalyst.Name);
}