How to generate better SQL from Linq2Sql query

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开发者_Go百科? 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.

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.

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;