How do I perform Date Comparison in EF query?

Question

Please help. I am trying to figure out how to use DATE or DATETIME for comparison in a linq query.

Example: If I wanted all Employee names for those who started before today, I would do something like this in SQL:

SELECT EmployeeNameColumn
FROM EmployeeTable
WHERE StartDateColumn.Date <= GETDATE() //Today

But what about linq?

DateTime startDT = //Today

var EmployeeName =  
from e in db.employee
where e.StartDateColumn <= startDT 

The above WHERE doesn't work:

Exception Details: System.NotSupportedException: The specified type member 'Date' is not supported in LINQ to Entities. Only initializers, entity members, and entity navigation properties are supported.

Harwood

Answer 1

That should work. Are you sure that there isn't another part of the query that triggered the exception? I have several instances of queries of the form

var query = from e in db.MyTable
            where e.AsOfDate <= DateTime.Now.Date
            select e;

in my code.

Undershot

This worked: WHERE StartDateColumn <= GETDATE() //Today This did not: WHERE StartDateColumn.Date <= GETDATE() //Today I had .Date in my statement - I must have been over thinking the issue. Thank you. kam – Harwood

This is not the problem - e.AsofDate.Date is the problem (compare only the date portion - on both sides... – Pendent

Answer 2

Use the class DbFunctions for trimming the time portion.

using System.Data.Entity;

var bla = (from log in context.Contacts
           where DbFunctions.TruncateTime(log.ModifiedDate) 
                              ==  DbFunctions.TruncateTime(today.Date)
           select log).FirstOrDefault();

Source: http://social.msdn.microsoft.com/Forums/en-US/csharpgeneral/thread/84d4e18b-7545-419b-9826-53ff1a0e2a62/

Proctoscope

Was struggling with this, decided to use an extension method, just to find later on about this -.- – Lenny

This should have been the answer – Conidiophore

EntityFunctions was obsoleted, replaced by DbFunctions, updated answer to reflect this – Emia

EntityFunctions was replaced in EF 6.0. So if you're still on 4.5 or earlier (like myself) you should still use EntityFunctions. – Wesle

how you seen what sql query is DbFunctions.TruncateTime generating: ((convert (datetime2, convert(varchar(255), [Extent1].[InspectionReportInfo_Date], 102), 102)) = '2015-02-10T00:00:00' this kills any index and I would avoid it... social.msdn.microsoft.com/Forums/en-US/… – Futility

Agree. This should have been the answer – Piddock

Answer 3

It may be due to the date in the database being nullable. Try this:

var EmployeeName =
from e in db.employee
where e.StartDateColumn.Value <= startDT 

Birthstone

The ".Value" was the epiphany for me. – Benito

Answer 4

You can check the condition like this

var nextDay = DateTime.Today.AddDays(1);

var query = from e in db.MyTable
            where e.AsOfDate >= DateTime.Today && e.AsOfDate < nextDay 
            select e;

here you'll get the records on AsOfDate date as we checking between today(00:00:00) and tommorow(00:00:00) we'll get today's date record only what ever may be the time...

Leptospirosis

Answer 5

You can not use .Date

If you would like to check for today you can create a datetime with no time

DateTime myDate = new DateTime(DateTime.Now.Year, DateTime.Now.Month, DateTime.Now.Day);
var e = (from mds in myEntities.Table
         where mds.CreateDateTime >= myDate
         select mds).FirstOrDefault();

Abeokuta

Answer 6

I'm curious to the error message saying 'Date', when you're passing a 'DateTime'. Could it be that 'StartDateColumn' is actually a 'Date', rather than a 'DateTime' in the database? That might mess up the comparison...

Lucic

Answer 7

try this:

DateTime dd = DateTime.Parse("08/13/2010 00:00:00");
var data = from n in ContributionEligibilities
           where n.ModifiedDateTime.Date >= DateTime.Parse("08/13/2010").Date
           select n; 
data.Dump("Result") ;

Saying

Answer 8

I am using a LinqDataSource and I had problems getting my query with a Date Comparison in it to execute without errors. The answer is to use the WhereAddParameters function and add the test value as a strongly typed parameter.

See the example below where I am matching a groupid and checking to see if the StopDate in my record is greater that or equal to a Date/Time stamp of now.

I am using this code fragment currently and it works like a charm.

LinqCampaigns.WhereParameters.Add("StopDate", System.Data.DbType.Date, DateTime.Now.ToString())
LinqCampaigns.Where = "GroupId = " & myGrp & " &&  " & "StopDate >= @StopDate"

Works like a charm....

Resignation

Answer 9

use a local variable to store the Date value and then use that variable in the query:

DateTime today = DateTime.Now.Date; from scheme in context.schemes where scheme.EndDate > today select scheme

Bowl

Answer 10

.Date did not work, but .Day did for me.

var query = from o in Payments
    where o.Order.OrderDate.Day != o.PaymentDate.Day
    orderby o.Order.OrderDate
    select new
    {
     o.Order.OrderID,
     o.Order.OrderDate,
     o.PaymentDate,      
     o.Order.FirstName,
     o.Order.LastName,
     o.Order.CustomerID
    };


query.Dump();

Frady

This is probably not what you want. It will simply get the Day of the Month. So, for instance, Jan 01 & Feb 01 both have the same 'Day' component value, despite actually being different days in real time. – Abstain

Answer 11

ensure that you check null value like this :

 '(from mm in _db.Calls 
   where mm.Professionnal.ID.Equals(proid)
   && mm.ComposedDate.HasValue &&
   (mm.ComposedDate.Value >= datemin) && (mm.ComposedDate.Value <= date)
   select mm).ToArray();'

Tatouay