LINQ pass in UTC date or use DateTime.UtcNow?

Question

I have a Entity Framework query that has a condition as follows:

var filtered = collection.Where(sp => sp.SubscriptionStartDate < DateTime.UtcNow && 
    sp.SubscriptionEndDate >= DateTime.UtcNow)

Vs.

var currentUtcDate = DateTime.UtcNow;
var filtered = collection.Where(sp => sp.SubscriptionStartDate < currentUtcDate  && 
    sp.SubscriptionEndDate >= currentUtcDate)

The first one will convert to SYSUTCDATETIME() and the other will pass in the date via a parameter. I've always done the 2nd scenario to have the dates exactly the same for both conditions and not off by nano seconds but I'm making that assumption.

Will this impact performance since I'm passing a different parameter into the query each run? Which is best practice?

Answer 1

As much as possible, I always try to push date-setting and date-checking to the database, because I've had projects where more than one piece of software was accessing the database, but none where there was a single piece of software accessing databases on separate machines, where those databases weren't kept in sync (e.g. if the database was load-balanced). (Or where I didn't actually care about timestamps anyway).

As such, the database has always been the obvious place for where the One-True-UTC-to-Rule-them-All can live.

This also gives me more flexibility in terms of what I do or do not put in triggers, column defaults, clean-up code that operates out of band, etc.

However, other applications could have the exact opposite, with a single application using several completely separate databases.

If (as is most common) I have a single app and single DB tend to still have the database as the sole provider of time, even if they are running on the same machine. As a rule, such systems turn into single-DB, multiple app situations down the line more often than they turn into single-app, multiple DB. (Again, I'm not counting cases where there is a cluster or load-balancing happening with the DB; I solve the problem of keeping them in sync separately, so they're still a single DB as far as the outside goes).

Either way, the basic principle is to assume that only one part of the system knows what time it is, at least for matters related to both parts (if the app does other time-related stuff that doesn't relate to the entities, then a reasonable amount of synchronisation is sufficient).

In summary:

1. Multiple apps, single DB: Use the time on the DB. (Code example 1)
2. Single app, multiple DB: Use the time from the app. (Code example 2)
3. Single app, single DB: Use the time on the DB (because you're more likely to evolve into case 1 than into case 2).
4. Multiple apps, multiple DB: Such a widely distributed system needs rules about time built into the design in a very robust and fault-tolerant manner, and I can't answer with just suggesting one or the other, though I'd still be more likely to base my solution on the first approach than the second.

As for performance, such matters are pretty much trivial. One will be faster than the other, because of any two things that take time in the real world one is faster than the other, but it likely won't be consistent which is which and it most certainly will be to such a small degree that you won't care. It might begin to matter if you've got a vast number of such parameters in a given query, but if it does begin to matter you will want to check and find out which works better for you, rather than do some computer-science theory to know which will work better for the average developer most of the time. You'll also likely want to change the approach further to avoid that vast number in the first place.

Answer 2

Variables in a query are translated to parameterized SQL. This must be so to achieve plan reuse in the database. Your date variable is like any other variable in this regard. It ends up as a datetime parameter.

I agree with what Jon Hanna said about the best-practice part of the question and there is nothing to add to it. From a performance standpoint it does not matter.