Why we should avoid DateTime.UtcNow inside a Linq to Entities query?

Why we should avoid DateTime.UtcNow inside a Linq to Entities query?

Because we can get unexpected results!
After read this tip, will be expected results 😉

What is the diference between these two Linq queries?

Query 1

Query 2

SQL generated

The first one will generate a SQL with WHERE clause like this: Where @p__linq__1 is the value of our filterDate variable.

The second one will generate this WHERE clause:

What is the problem?

post image

Imagine that we’re using the second query inside some sync algorithm in our C# code, this algorithm is very sensitive about time, now imagine that the server where our C# code is running has a difference about seconds or minutes with the database server?

YES, UNEXPECTED RESULTS!

Conclusion

post image

Linq to Entities is very smart and it is able to translate our DateTime.Now or DateTime.UtcNow to a matching command on database side.

The important here is: we should remember that it can do this and we should use features like these with parsimony.



Loading comments...


Tutorials

Articles

Labs