>>>>>Maybe just retrieve the SQL data using the simple startdate-endate query then use linq to objects to filter the results on the remaining criteria.
>>>>>If the number of records returned from the simple query is excessive then perhaps a CLR proc in SQLServer ?
>>>>
>>>>I found solution here http://stackoverflow.com/questions/10000338/using-datetime-value-timeofday-in-linq-query which I am trying now.
>>>
>>>Interesting. Hadn't come across canonical functions. Let us know how you get on.....
>>
>>It did work out (although I replaced them to DbFunctions as the other ones were deprecated). I didn't find anything for WeekDay, though, so I kept that logic separate after getting result back.
>
>And how much more efficient was the SQL execution compared to the original attempt ?

Slightly shorter. I am wondering how can I specify to use LINQ to SQL rather than LINQ to Entities? In LinqPad it obviously used LINQ to SQL and everything worked by itself (although resulted in complex SQL). In the application it used LINQ to Entities where TimeOfDay is not supported natively.