Difference in LINQ query in memory vs on SQL

LINQ and Entity Framework are fantastic! but sometimes you can end up in situations that are weird. One of those cases is when LINQ is translated into SQL in a way that is different from what you would expect. This can happen sometimes with dates. What is the difference with these two snippets?

Well if you are running “in memory” they are the same. But if you are connected to a SQL database, the first expression will translate DateTime.Now to the appropriate SQL function, while in the second case, it will plugin the value of DateTime.Now.

So? Why does it matter?

Well, those two could be on different timezones, and the result would be different from what you expect.

I’m sure this could be handled better using DateTimeOffset instead of DateTime, but if your column isn’t compatible, you need to be aware of that Entity Framework is doing under the hood

Author: Maurizio Pozzobon

Maurizio has 5+ years developing solutions in the insurance industry. He is passionate about doing the right thing right, so he works in a tight loop with his clients to deliver the best solution possible.