Compiled query returns the same thing every time

Question

I have this compiled query:

private static Func<DBContext, Foo> FooQuery = CompiledQuery.Compile<DBContext, Foo>(
    _db => _db.FooTable.SingleOrDefault(f => f.DeletionDate == null || f.DeletionDate > DateTime.UtcNow)
);

When I run it once, it returns the expected Foo object.

But then, even after that object's DeletionDate is set in the db, it still returns the same object -- I am expecting null. (It returns null, as expected, after recycling the app pool.)

For some reason, it works when I use the following compiled query instead (and pass in DateTime.UtcNow), but I'm not sure why.

private static Func<DBContext, DateTime, Foo> FooQuery = CompiledQuery.Compile<DBContext, DateTime, Foo>(
    (_db, now) => _db.FooTable.SingleOrDefault(f => f.DeletionDate == null || f.DeletionDate > now)
);

Answer 1

When you pass in DateTime.UtcNow in the compiled query, you are setting a constant date as part of the SQL. But when you pass in a parameter, you are creating a parameterized query in which the parameter (i.e. the date) can change each time you call it.

When you recycle the App Pool, you're recompiling the query all over again.

Check out the generated SQL and you'll see what I'm talking about.