How do I perform Date Comparison in EF query?

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.

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/

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.

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 

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...

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

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...

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

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....

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

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

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