Linq to entities compare datetime with milliseconds precision

Question

I have a specific linq to entity query that is written like the following:

var query = from orgs 
            in orgBaseQuery.Where(org => org.LastUpdated >= fromDate 
                                         && org.LastUpdated <= toDate)

The type of the toDate and fromDate are .net DateTime and the type of the LastUpdated is SQL DateTime. My problem is that the generated SQL query does not have the millisecond portion in it and that is causing some errors in our application. I was wondering if there is any solution to this problem without changing the data type of LastUpdated to datetime2?

Answer 1

If your column in the database is datetime, then you're not going to have the precision that you're looking for. It is a legacy data type. Starting with SQL Server 2008 (possibly R2), the better data type to use is datetime2. It is a perfect match for the C# DateTime type, including precision.