How do LINQ queries against the Entity Framework communicate dates to a SQL Server?

Question

I'm using two LINQ queries to access my database using Entity Framework:

The first stores a token in the database and gives it a 60 second timeout:

string tokenHash = GetTokenHash();
Token token = new Token()
{
    Client = client,
    Expiry = DateTime.Now.AddSeconds(60),
    UserId = userId,
    TokenHash = tokenHash,
};
context.AddToToken(token);
context.SaveChanges();

The second checks for a matching token that hasn't expired yet:

var item = (from t in datasource.SmsToken
            where t.Client.Id == clientId
            && t.UserId == userId
            && t.TokenHash == tokenHash
            && t.Expiry > DateTime.Now
            select t);
bool success = item.Count() >= 1;

The issue I'm having is that this was working perfectly on a test server. Now that it's moved to a different environment, it no longer works.

I've dumped a lot of debug information and everything seems to match. If I remove the t.Expiry > DateTime.Now condition, it works fine. So the problem is in the date comparison.

The new server has been set up with different date format and globalization settings in Windows. I assume this is the problem, and this confuses me.

I would have thought that the dates would be stored and retrieved consistently using LINQ and EF. I shouldn't have any formatting issues with this should I? Can anyone tell me what's going wrong here?

---

Update:

Interestingly, I get correct behaviour by replacing the retrieval code with the following:

var item = (from t in datasource.SmsToken
            where t.Client.Id == clientId
            && t.UserId == userId
            // && t.TokenHash == tokenHash
            // && t.Expiry > DateTime.Now
            select t).ToList();

var matchingToken = (from t in item
                     where t.TokenHash == tokenHash
                     && t.Expiry > DateTime.Now
                     select t).FirstOrDefault();

bool success = matchingToken != null;

That suggests to me that the problem is to do with date comparisons inside Linq-to-entities. Linq-to-objects works fine!

Answer 1

Do you insert the token and retrieve the comparison on servers with the same time zone?

It is better if you never store local times in the database. For one, it eliminates the issue of enqueue in one time zone and dequeue into another. And secondly, the code works all year around: as it is right now your code will fail 'by design' two nights every year: when daylight savings start and and when daylight savings end.

Better use DateTime.UtcNow both for enqueue and dequeue operation.

Now that being said, it is unlikely that this is the problem you're facing. But to know the type of comparison that occurs in your case, we would need to know the type of the Expiry column, as declared in the database and as declared in EF. Is it a string, is it datetime or is it datetimeoffset?

Answer 2

Thanks to Kristian's suggestion, I've worked it out:

This code:

var item = (from t in datasource.SmsToken
            where t.Client.Id == clientId
            && t.UserId == userId
            && t.TokenHash == tokenHash
            && t.Expiry > DateTime.Now
            select t);
bool success = item.Count() >= 1;

renders this SQL:

exec sp_executesql N'SELECT 
1 AS [C1], 
[Extent1].[Id] AS [Id], 
[Extent1].[UserId] AS [UserId], 
[Extent1].[TokenHash] AS [TokenHash], 
[Extent1].[Expiry] AS [Expiry], 
[Extent1].[ClientId] AS [ClientId]
FROM [dbo].[Token] AS [Extent1]
WHERE ([Extent1].[ClientId] = @p__linq__16)
AND ([Extent1].[UserId] = @p__linq__17)
AND ([Extent1].[TokenHash] = @p__linq__18)
AND ([Extent1].[Expiry] > (GetDate()))',
N'@p__linq__16 nvarchar(7),@p__linq__17 nvarchar(9),@p__linq__18 nvarchar(16)',@p__linq__16=N'OTPTest',@p__linq__17=N'Test User',@p__linq__18=N'7?????:??????'

Note the line: AND ([Extent1].[Expiry] > (GetDate()))',

What this means is that LINQ-to-Entities translates a comparison using DateTime.Now to GetDate() in the database. Therefore, any time differences between servers can cause problems.

My solution was to leave the date comparison out of the original query and do it with LINQ-to-Objects when everything came back.