>>>Can not yet make it to work. That's my latest attempt which blows up in run-time:
>>>
>>>

>>> var startTime = new DateTime(1900, 1, 1, beginDateTime.Hour, beginDateTime.Minute, 0);
>>>                var endTime = new DateTime(1900, 1, 1, endDateTime.Hour, endDateTime.Minute, 0);
>>>                var daysOfWeek = dailyLimits.Where(dl => dl.Selected == true).Select(ds => ds.WeekDay).ToList();
>>>                if (daysOfWeek.Count() < 7) // not all days of the week selected
>>>                {
>>>                    query = query.Where(m4s => DateTime.Parse("01-01-1900 " + m4s.StartTime.Value.ToShortTimeString()) <= endTime &&
>>>                    DateTime.Parse("01-01-1900 " + m4s.EndTime.Value.ToShortTimeString()) >= startTime && daysOfWeek.Contains((short)m4s.StartTime.Value.DayOfWeek));
>>>                }

>>>
>>>about the Parse problem. Has anyone deal with this problem already of getting time portion of the date using LINQ?
>>>
>>>Thanks in advance.
>>>
>>>UPDATE. Tried TimeOfDay and it worked in my quick test, but the code it produced is not a clear conversion to time as I hoped. Still looking for better solutions.
>>
>>Don't use DateTime.Parse to add a date and time. Its a needless conversion and subject to regional settings. Create a DateTime object set to 1/1/1900 to use as your starting point. Add the StartTime or EndTime's TimeOfDay to your 1/1/1900 object's Date property to get the DateTime you are looking for.
>
>Thanks, Rob. The resulting SQL is still too complicated, e.g. here is the SQL I got in LINQPad:
>
>

>-- Region Parameters
>DECLARE @p0 Int = 1
>DECLARE @p1 Int = 2
>DECLARE @p2 DateTime = '1900-01-01 00:00:00.000'
>DECLARE @p3 DateTime = '1900-01-01 20:00:00.000'
>DECLARE @p4 DateTime = '1900-01-01 00:00:00.000'
>DECLARE @p5 DateTime = '1900-01-01 08:00:00.000'
>-- EndRegion
>SELECT TOP (300) [t0].[id] AS [Id], [t0].[type] AS [Type], [t0].[start_time] AS [Start_time], [t0].[end_time] AS [End_time]
>FROM [max4sale] AS [t0]
>WHERE ((CONVERT(Int,(DATEPART(dw, [t0].[start_time]) + (@@DATEFIRST) + 6) % 7)) IN (@p0, @p1)) AND (DATEADD(ms, ((CONVERT(BigInt,((CONVERT(BigInt,DATEPART(HOUR, [t0].[start_time]))) * 36000000000) + 
>((CONVERT(BigInt,DATEPART(MINUTE, [t0].[start_time]))) * 600000000) + 
>((CONVERT(BigInt,DATEPART(SECOND, [t0].[start_time]))) * 10000000) + ((CONVERT(BigInt,DATEPART(MILLISECOND, [t0].[start_time]))) * 10000))) / 10000) % 86400000, 
>DATEADD(day, (CONVERT(BigInt,((CONVERT(BigInt,DATEPART(HOUR, [t0].[start_time]))) * 36000000000) + 
>((CONVERT(BigInt,DATEPART(MINUTE, [t0].[start_time]))) * 600000000) + 
>((CONVERT(BigInt,DATEPART(SECOND, [t0].[start_time]))) * 10000000) + 
>((CONVERT(BigInt,DATEPART(MILLISECOND, [t0].[start_time]))) * 10000))) / 864000000000, @p2)) <= @p3) AND (DATEADD(ms, ((CONVERT(BigInt,((CONVERT(BigInt,DATEPART(HOUR, [t0].[end_time]))) * 36000000000) + 
>((CONVERT(BigInt,DATEPART(MINUTE, [t0].[end_time]))) * 600000000) + 
>((CONVERT(BigInt,DATEPART(SECOND, [t0].[end_time]))) * 10000000) + 
>((CONVERT(BigInt,DATEPART(MILLISECOND, [t0].[end_time]))) * 10000))) / 10000) % 86400000, DATEADD(day, (CONVERT(BigInt,((CONVERT(BigInt,DATEPART(HOUR, [t0].[end_time]))) * 36000000000) + 
>((CONVERT(BigInt,DATEPART(MINUTE, [t0].[end_time]))) * 600000000) + 
>((CONVERT(BigInt,DATEPART(SECOND, [t0].[end_time]))) * 10000000) + ((CONVERT(BigInt,DATEPART(MILLISECOND, [t0].[end_time]))) * 10000))) / 864000000000, @p4)) >= @p5)
>

>
>I think it's time to convert the above logic into SP. Part of the reason I didn't want to do so is that I construct query dynamically depending on the type. I would need to reproduce the same logic in SP then. But the above code looks too monstrous to me.

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 ?