CODEWITHSUNDEEP
c#linqdatetime
Traffy
Traffy

Reputation: 2861

Compare 2 dates within a Linq query

I'm trying to compare 2 dates (only dates) of 2 DateTime values within a LINQ query doing this : 

var appointmentsWithPatch = (from ap in context.DBAppointment where dtStart.Date ==  ap.StartDate.Value.Date select ap).ToList();

Knowing that dtStart contains 27/03/2015 and I've one record in my DBAppointment table which has that date as StartDate, it should return one element. However, it does not work and I'm getting null.

Any idea about what's happening here?

Upvotes: 1

Views: 2640

Answers (2)

Rob Lyndon
Rob Lyndon

Reputation: 12651

You need to use Linq to Entities.

var appointmentsWithPatch = (from ap in context.DBAppointment where DbFunctions.DiffDays(dtStart, ap.StartDate) == 0 select ap).ToList();

Farhad's answer works as well; the point is that Entity Framework needs to convert your Linq Expression into the corresponding SQL, and DbFunctions provides the tools to enable it to do that.

Upvotes: 0

Farhad Jabiyev
Farhad Jabiyev

Reputation: 26635

Try to truncate time from dtStart.Date:

 var appointmentsWithPatch = 
          (from ap in context.DBAppointment 
             where EntityFunctions.TruncateTime(dtStart.Date) == ap.StartDate.Value.Date 
             select ap)
         .ToList();

If you are using EF6, then change EntityFunctions to DbFunctions.

Additional info:

EntityFunctions methods are called canonical functions. And these are a set of functions, which are supported by all Entity Framework providers. These canonical functions will be translated to the corresponding data source functionality for the provider. Canonical functions are the preferred way to access functionality outside the core language, because they keep the queries portable.

You can find all canonical functions here and all Date and Time Canonical Functions here.

Don't forget to add a reference to System.Data.Objects and to System.Data.Entity.

Upvotes: 2

Related Questions