Uncategorized

Linq to SQL, Linq to EF, DateTime.Now and GETDATE() Woes

A client of ours requires that each one of their cruise ships have different timezones they exist in. When they create gaming records for their ships, the records created have to fall in their timezone in order for the game to close at the right time. They called us up today and mentioned that the games were closing an hour early for a particular set of ships.

That’s the backstory, what was found was that a Linq to EF query with DateTime.Now in it gets converted to GETDATE() in the expression tree, while L2S does not.

The example here EFDataStore.GetActiveGamesByDateTime(DateTime.Now) would be converted to SELECT * FROM Games WHERE CloseTime >= GETDATE(), and on the DB server, daylight savings time cannot be disabled, and on the app server, DST is turned off.

Changing the C# code to this solved the problem:

DateTime dt = DateTime.Now;

EFDataStore.GetActiveGamesByDateTime(dt)

When EF walks the expression tree using reflection, it won’t see DateTime.Now in there and won’t convert it to GETDATE(), but it will use the actual date and time stored in the variable.

We never had problems with other ships because the servers that run these particular ships have a separate database server than the application server, where the other ships have application and DB servers on a single box.

Advertisements

One thought on “Linq to SQL, Linq to EF, DateTime.Now and GETDATE() Woes

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s