How to generate better SQL from Linq2Sql query

Question

I have the following query:

var data = from d in dc.GAMEs 
    where (d.GAMEDATE + d.GAMETIME.Value.TimeOfDay) >= DateTime.Now select d; 

This generates some horendous looking SQL, looking something like this:

SELECT {...} WHERE DATEADD(ms, ((CONVERT(BigInt,((CONVERT(BigInt,DATEPART(HOUR, [t0].[GAMETIME]))) * 36000000000) + ((CONVERT(BigInt,DATEPART(MINUTE, [t0].[GAMETIME]))) * 600000000) + ((CONVERT(BigInt,DATEPART(SECOND, [t0].[GAMETIME]))) * 10000000) + ((CONVERT(BigInt,DATEPART(MILLISECOND, [t0].[GAMETIME]))) * 10000))) / 10000) % 86400000, CONVERT(DateTime,DATEADD(day, (CONVERT(BigInt,((CONVERT(BigInt,DATEPART(HOUR, [t0].[GAMETIME]))) * 36000000000) + ((CONVERT(BigInt,DATEPART(MINUTE, [t0].[GAMETIME]))) * 600000000) + ((CONVERT(BigInt,DATEPART(SECOND, [t0].[GAMETIME]))) * 10000000) + ((CONVERT(BigInt,DATEPART(MILLISECOND, [t0].[GAMETIME]))) * 10000))) / 864000000000, [t0].[GAMEDATE]))) >= @p0

What is the reason for this massive amount of SQL? And is there a better way to deal with it?

EDIT:

I have no control over the schema. It is what it is, and I have to deal with it.

Answer 1

If you can change the schema then the best way to deal with it is to use a single datetime2 column instead of separate date and time fields. Your current query will not be able to use an index.

Otherwise you could try rewriting the query as follows:

DateTime now = DateTime.Now;
var data = from d in dc.GAMEs 
    where (d.GAMEDATE > now.Date) ||
          (d.GAMEDATE == now.Date && d.GAMETIME.Value.TimeOfDay >= now.TimeOfDay)
    select d; 

The SQL generated by this query might be slightly more readable and perhaps also more efficient. On the other hand, from a programmer's perspective it is more important that the source code is readable than the generated SQL is readable. If performance is not a concern you may want to leave your code as it is and just accept that the generated SQL is ugly and not worry about it.

Answer 2

As an alternative (since outermost OR is the bane of indexes)

DateTime now = DateTime.Now;
DateTime today = now.Date;
TimeSpan timeOfDay = now.TimeOfDay;

var data =
  from d in dc.GAMEs  
  where d.GAMEDATE >= today
    && (d.GAMEDATE > today || d.GAMETIME.Value.TimeOfDay >= timeOfDay) 
  select d;