score:-1

-You can use the TimeOfDay property on the dates to compare them.

string timeAsString = "13:00";

from f in TableRows
where f.Date.TimeOfDay > DateTime.Parse("11-12-2012 "+timeAsString).TimeOfDay
select f

EDIT

here is some code you can test that runs for me:

  DateTime d = DateTime.Parse("12-12-2012 13:00");
  List<DateTime> dates = new List<DateTime>();
  dates.Add(DateTime.Parse("12-12-2012 13:54"));
  dates.Add(DateTime.Parse("12-12-2012 12:55"));
  dates.Add(DateTime.Parse("12-12-2012 11:34"));
  dates.Add(DateTime.Parse("12-12-2012 14:53"));

  var result = (from f in dates where f.TimeOfDay > d.TimeOfDay select f);

EDIT 2

Yea it seems that you needed to .ToList(), which, tbh you should have been able to figure out. I had no way of knowing what of collection you had. Not sure either of us deserve a downvote for trying to help you when you don't supply an awful amount of information on the problem

score:-1

If you had a date and a time column in your table (as possible with SQL Server 2008), you could do this directly in SQL.

As that's not the case, you have to do it like this:

// the first two lines can be omitted, if your bounds are already timespans
var start = startDate.TimeOfDay;
var end = endDate.TimeOfDay;
var filteredItems = context.Items.ToList()
    .Where(x => x.DateTimeColumn.TimeOfDay >= start
        && x.DateTimeColumn.TimeOfDay <= end);

score:3

var filteredTimes = myContext.MyTable
   .Where(r => SqlFunctions.DatePart("hour", r.DateField) >= 11 &&
               SqlFunctions.DatePart("hour", r.DateField) <= 13);

You need to include System.Data.Objects.SqlClient to get SqlFunctions.

score:0

If you convert the date value to a double and use the fractional part of if, you get a number between 0 and 1 that represents a time in a day. Having that, it is trivial to test for time intervals, where e.g. 13:45 would be 0,5729166667 (or more precise: 13:45.345 is 0,572920679).

You can do this because EF (i.e. 4.3, the version I use) translates Cast and even Math functions into SQL:

mycontext.Data.Where(dt => dt.DateTime.HasValue)
    .Select(dt => dt.DateTime.Value).Cast<double>()
    .Select(d => d -  Math.Floor(d))
    .Where(... your comparisons

This translates to Sql containing CAST([date column] AS float) and Sql's FLOOR function.


After your comments:

It looked so easy, but I can't find a way to instruct EF to do a CAST on a single property in a Select(). Only the Cast<>() function is translated to CAST (sql), but that operates on a set.

Well, fortunately, there is another way:

mycontext.Data.Where(dt => dt.DateTime.HasValue)
.Select(dt => new
    {
      Date = DbFunctions.TruncateTime(dt.DateTime.Value),
      Ms = DbFunctions.DiffMilliseconds(
               DbFunctions.TruncateTime(dt.DateTime.Value), dt.DateTime.Value)
    })
.Where(x => x.Ms > lower && x.Ms < upper)

where lower and upper are the TotalMilliseconds property of TimeSpan objects.

Note that this is horribly inefficient in sql! The date functions are repeated for each comparison. So make sure that you do the comparisons on a set of data that has been confined as much as possible by other criteria. It may even be better to fetch data in memory first and then do the comparisons.

Note: prior to EF6, DbFunctions was EntityFunctions.


Related Articles