Is possible to run a query with linq to search for

问题:

Problem details:

• SQL Server 2005;
• Entity Framework 4.0.

I'm trying with linq to run a query for a period of time, only. Exemple:

I have the following datetime data in my server:

30/03/2012 12:53:22
30/03/2012 17:23:29
04/04/2012 11:10:14
04/04/2012 19:06:55   

I want to run a query that will return me all data between the time (12:00 and 20:00) and the query have to return to me the following data:

30/03/2012 12:53:22
30/03/2012 17:23:29
04/04/2012 19:06:55  

Or between (11:00 and 13:00) and the query have to return to me the following data:

30/03/2012 12:53:22
04/04/2012 11:10:14  

How can i do this with linq? Is it possible (to ignore the date, use only the time)?

回答1:

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.

回答2:

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.

回答3:

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);

回答4:

-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