C# - Compare DateTime without Time on LINQ?

Question

[Route("api/sensors/{id}/{date}/Measurements")]
public IEnumerable<Measurement> GetMeasurements(int id, DateTime date)//date == "11:01:2016 00:00:00"
{
    return _unitOfWork.MeasurementRepository.Get(orderBy: e => e.Where(ex => ex.SensorId == id).Where(a => a.Time == date).OrderBy(tx => tx.Time));
    //a.Time = 11:01:2016 00:00:00, ..., 11:01:2016 00:15:00, ...,11:01:2016 00:30:00, ...,11:01:2016 00:45:00, ...
}

I have to return all measurement of the searched day (DateTime date), but I don't know how to compare the two DateTimes without the Time...

If I use a.Time.Date == date.Date, this error appears :

LINQ to what? In LINQ to Objects you can simply compare the `.Date` properties. In LINQ to EF you need to use [DbFunctions.TruncateTime](https://msdn.microsoft.com/en-us/library/system.data.entity.dbfunctions.truncatetime(v=vs.113).aspx) to generate the equivalent SQL — Panagiotis Kanavos, Feb 03 '16 at 08:27
Is the query going to the database or only on a list of pocos? — Thorarins, Feb 03 '16 at 08:27
See [The specified type member 'Date' is not supported in LINQ to Entities. Only initializers, entity members, and entity navigation properties](http://stackoverflow.com/a/14601775/2594597) — Leonid Vasilev, Feb 03 '16 at 08:49
@LeonidVasilyev that's [the older name of DbFunctions](http://stackoverflow.com/questions/23911301/what-are-the-difference-between-entityfunctions-truncatetime-and-dbfunctions-tru) — Panagiotis Kanavos, Feb 03 '16 at 08:55

score 2 · Answer 1 · answered Feb 03 '16 at 08:24

2

I have come across this issue before too. Luckily there is a fairly easy solution.

The DateTime struct provides a Date property for just this occasion.

Property Value
Type: System.DateTime
A new object with the same date as this instance, and the time value set to 12:00:00 midnight (00:00:00).
https://msdn.microsoft.com/en-us/library/system.datetime.date(v=vs.110).aspx

Which means you can simply do this

Where(a => a.Time.Date == date.Date)

answered Feb 03 '16 at 08:24

Travis J

81,153
41
202
273

1

This would only work on LINQ to Objects. In LINQ to EF it would throw an exception as the comparison can't be translated to SQL as-is. Even worse, in LINQ to SQL all data would be loaded in memory and the Date comparison would be performed in memory – Panagiotis Kanavos Feb 03 '16 at 08:29
I edited my question.. – ALSTRA Feb 03 '16 at 08:34

Amarnath · Accepted Answer · 2016-02-03T09:34:57.643

1

You can do like this

.Where(a => a.Time >= date.From() && a.Time <= date.To())

Using Helper Methods

 public static DateTime From(this DateTime value)
 {
     return new DateTime((value.Year, value.Month, value.Day);
 }

 public static DateTime To(this DateTime value)
 {
     return new DateTime(value.Year, value.Month, value.Day, 23, 59, 59);
 }

edited Feb 03 '16 at 09:34

answered Feb 03 '16 at 08:34

Amarnath

62
4

You can change `From` to `return value.Date;` (or just use `value.Date` directly instead of having an extension method) and `To` to `return value.Date.AddDays(1).AddSeconds(-1);` -- or simply `return value.Date.AddDays(1);` and then filter `a.Time < date.To()` (instead of `<=`). - Or `a.Time < date.Date.AddDays(1)`. – Corak Feb 03 '16 at 08:38
Btw.: all the `DateTime.AddXXX` methods return a **new** `DateTime` object and **don't change** the value they're used on. See also: [DateTime.AddDays](https://msdn.microsoft.com/library/system.datetime.adddays.aspx) – Corak Feb 03 '16 at 08:44
That's simply wrong. You *can* compare dates, just as you can in T-SQL, by truncating time or casting to date. There are multiple duplicate questions in SO. In fact, if the underlying column type was `date` a date comparison wouldn't need any conversions – Panagiotis Kanavos Feb 03 '16 at 08:56
This is simply wrong on many levels. Firstly, the Expression does not compile (at least not on EF5, not sure if this was fixed in EF7). Secondly `DbFunction.TruncateTime` was included in EF specifically for this situation. – Aron Feb 03 '16 at 08:58

score -1 · Answer 3 · answered Feb 03 '16 at 08:24

-1

Just use the Date property of the DateTime. This property does not entirely ignore time but will assume that the time is midnight.

answered Feb 03 '16 at 08:24

Manuel Zelenka

1,616
2
12
26

I edited my question.. – ALSTRA Feb 03 '16 at 08:33

Jesse Milan · Answer 4 · 2016-02-18T17:10:19.263

-1

Try using EntityFunctions.TruncateTime or in EF 6.0 it has been replaced with DbFunctions.TruncateTime, and then compare that with the known date converted to short date string (date.ToShortDateString()).

So:

[Route("api/sensors/{id}/{date}/Measurements")]
public IEnumerable<Measurement> GetMeasurements(int id, DateTime date)//date == "11:01:2016 00:00:00"
{
    return _unitOfWork.MeasurementRepository.Get(orderBy: e =>
        e.Where(ex => ex.SensorId == id).Where
        (a => EntityFunctions.TruncateTime(a.Time) == date.ToShortDateString())
        .OrderBy(tx => tx.Time));
}

edited Feb 18 '16 at 17:10

answered Feb 03 '16 at 08:51

Jesse Milan

53
5

Your code does the *opposite* of what you propose. Instead of comparing dates, it compares date with an arbitrary string – Panagiotis Kanavos Feb 03 '16 at 08:52
What is the arbitrary string? The date.ToShortDateString() is specifically a parameter passed into the OP's function. This compares ONLY the date of the 'Time' property to ONLY the date of the date parameter. – Jesse Milan Feb 03 '16 at 18:03

C# - Compare DateTime without Time on LINQ?

4 Answers4