efcore: Intermittent Behavior With Linq .Any() / AnyAsync()

Please observe the code of conduct…

I can’t think of anything else that is translated to SQL when it can be evaluated client side.

There are case, Random/Guid etc. Anything which translating on client vs server gives different result due to the non-deterministic behavior of the API itself is not evaluated on client (barring the differences in case sensitivity in strings).

Whatever the issue it’s not the expected behavior and I think you’ll agree that both methods should yield the same result: It seems that DateTime.Now.Date in the Linq-lambda query is (intermittently) being translated as just DateTime.Now

Use #28151 if you really want to force creating parameter. We don’t plan to change current behavior.

Despite some unfortunate comments, I do think this is an interesting case, and it’s not just about whether something is in the lambda expression or not.

In a lot of cases, something like this inside the lambda would still be evaluated in .NET and sent as a parameter. Consider if one had their own very similar static helper which returned a date - MyDateTime.Now.Date - and included that in the lambda. It would be evaluated and parameterised.

Off the top of my head, I can’t think of anything else that is translated to SQL when it can be evaluated client side. To give one example, string.Concat is translatable to SQL, and is & must be translated if it involves columns, but is evaluated client side if it involves client values only.

I’d be interested to know of other cases that are like DateTime.Now and related - ie client evaluatable, but translated. I’m not necessarily saying it’s wrong that it’s translated, but that it’s understandably a point of confusion. Maybe some documentation can help?

Okay, wait I get it. I understand you want to support that SQL feature and how else to support that. I don’t have an answer for that but I would argue that the equivalence take precedence over accessing SQL feature. After all the value in code is the intended value.

External factors? We’re talking sub-millisecond difference in allocating the variable first.

There’s a fundamental thing about LINQ querying that you’re probably missing… In “regular LINQ” (LINQ to Objects), .NET code is executed as always, and so the following are exactly the same:

// Variant 1
DateTime dateTime = DateTime.Now.Date;
var x = data.Any(x => x.Date >= dateTime);

// Variant 2
var x = data.Any(x => x.Date >= DateTime.Now.Date);

However, EF Core is a (queryable) LINQ provider, and things work very different. The lambda inside the Any isn’t actually executed/evaluated in .NET (as it is with LINQ to Objects above); EF instead gets it as an expression tree, translates it to SQL and executes it against your database. This is how .NET LINQ providers work: although the code looks like regular C#, it actually isn’t executed as C#.

That means that the two variants above are completely different. In the 1st case, DateTime.Now.Date is outside the LINQ query, so it’s evaluated as usual in.NET; the result is then sent by EF Core as a simple parameter to the database, in SQL. In the 2nd case, DateTime.Now.Date is inside the lambda, so it’s included as a SQL function call inside your SQL (e.g. GETDATE()), and results may be different based than the 1st variant (e.g. because timezones differ in your client .NET machine and in your SQL database server).

I hope this clarifies how LINQ and EF operate.

Another good example is case sensitivity: comparing strings in .NET is case-sensitive by default, but SQL Server is case-insensitive by default. That’s just the reality of things: logic evaluated in the database won’t always match local evaluation 100%.

Whatever the issue it’s not the expected behavior and I think you’ll agree that both methods should yield the same result

That assumption isn’t correct. There are some cases where evaluation depends on some external factor (such as the machine time zone); in those cases, evaluating the logic locally or in the database will yield different results.

Think of it this way: when you’re including a piece of C# code inside an EF Core LINQ query, you’re asking EF Core to translate that to SQL. When the code is outside the LINQ query, you’re asking .NET to execute it locally. The two will sometimes yield different results - this is the expected behavior.

@JawzoD3TH When DateTime.Now is inside your LINQ query, EF translates that to the equivalent function in SQL, e.g. GETDATE(). On the other hand, when DateTime.Now is assigned to a variable outside your LINQ query, it is evaluated as usual in .NET, and the result is sent to SQL Server as a parameter. The results of the two could be different if e.g. a different time zone is configured locally and on your database server.

If this isn’t enough to clarify the situation, please submit a minimal code sample that shows the issue, and include the timezones on your .NET client machine and at your database.