LINQ to SQL inefficiency in handling DateTimes

Question

The following LINQ to SQL query:

Orders.Where (o => o.RequiredDate.Date == o.ShippedDate.Date)

translates to this:

...
WHERE DATEADD(HOUR, -DATEPART(HOUR, [t0].[RequiredDate]), DATEADD(MINUTE, -DATEPART(MINUTE, [t0].[RequiredDate]), DATEADD(SECOND, -DATEPART(SECOND, [t0].[RequiredDate]), DATEADD(MILLISECOND, -DATEPART(MILLISECOND, [t0].[RequiredDate]), [t0].[RequiredDate])))) = DATEADD(HOUR, -DATEPART(HOUR, [t0].[ShippedDate]), DATEADD(MINUTE, -DATEPART(MINUTE, [t0].[ShippedDate]), DATEADD(SECOND, -DATEPART(SECOND, [t0].[ShippedDate]), DATEADD(MILLISECOND, -DATEPART(MILLISECOND, [t0].[ShippedDate]), [t0].[ShippedDate]))))


I imagine there must be a significant performance hit in this.  Would it be possible for LINQ to SQL to translate the predicate to the following instead:

...
where
    datepart (yy, RequiredDate) = datepart (yy, ShippedDate) and
    datepart (dy, RequiredDate) = datepart (dy, ShippedDate)

or even:

...
where
    datepart (yy, RequiredDate) * 1000 + datepart (dy, RequiredDate) =
    datepart (yy, ShippedDate) * 1000 + datepart (dy, ShippedDate)


The same things happens when you project a datetime's Date property.  If you project o.RequiredDate.Date, then LINQ to SQL should perhaps select datepart (yy, RequiredDate) * 1000 + datepart (dy, RequiredDate) and then convert the integer back to a DateTime on the client side, rather than emitting this:

SELECT DATEADD(HOUR, -DATEPART(HOUR, [t0].[RequiredDate]), DATEADD(MINUTE, -DATEPART(MINUTE, [t0].[RequiredDate]), DATEADD(SECOND, -DATEPART(SECOND, [t0].[RequiredDate]), DATEADD(MILLISECOND, -DATEPART(MILLISECOND, [t0].[RequiredDate]), [t0].[RequiredDate])))) AS [value]
FROM [Orders] AS [t0]

With a query that involves a number of DateTimes, you otherwise end with pretty hideous SQL, especially when grouping and joining across the Date properties.

Cheers

Joe

Answer 1

Or at least use this, as a translation of RequiredDate.Date:

DATEADD (dd, DATEDIFF (dd, 0, RequiredDate), 0)

Joe