EntityFunctions.TruncateTime alternative when using EF CTP5 with Sql Server Compact Edition 4.0

Question

I'm using EF CTP5 code-first with Sql CE 4.0. I am trying to perform a time-insensitive date comparison between two DateTime properties.

As linq to entites cannot parse the DateTime.Date property (which would have been nice) the solution is to use EntityFunctions.TruncateTime, however this doesn't appear to work with Sql CE 4.0.

I can manually compare the Year, Month and Day of the two dates, this works but makes the code a lot more verbose than it should be. Ideally I don't want to change my queries to use the long-hand version to make them work with both full sql server and sql ce 4.0.

Therefore does anyone have any clean and clever solutions to this? I think the solution will probably involve expression re-writing but I'm not quite sure where to start.

Any help is greatly appreciated,

Paul

Allegorist

Answer 1

Do you have the option of changing the structure of your database?

If so you could add an extra column with just the date part of the datetime, and then use that column in your query.

It will use a little more space in your database, but should run a little faster since you avoid any conversions.

Floating

Hi, this is actually what I've done in the meantime. Like you say it means that as a db level the queries are actually better. In this instance it's not too much of a compromise. I don't want to get into a habit of not keeping my entities as pure business objects. If I find I need to add other 'helper' columns for querying then I think I'll have to look seriously at a reporting database designed to reduce these kinds of mismatch. Thanks for the answer I appreciate it – Allegorist