How to compare only date components from DateTime in EF

Question

I am having two date values  one already stored in the database and the other selected by the user using DatePicker  The use case is to search for a particular date from the database   The value previously entered in the database always has time component of 12 00 00  where as the date entered from picker has different time component    I am interested in only the date components and would like to ignore the time component    What are the ways to do this comparison in C    Also  how to do this in LINQ   UPDATE  On LINQ to Entities  the following works fine   e   gt  DateTime Compare e FirstDate Value  SecondDate   gt   0

User · Answer

Here s a different way to do it  but it s only useful if SecondDate is a variable you re passing in   DateTime startDate   SecondDate Date  DateTime endDate   startDate AddDays 1  AddTicks -1       e   gt  e FirstDate Value  gt   startDate  amp  amp  e FirstDate Value  lt   endDate   I think that should work

User · Answer

If you use the Date property for DB Entities you will get exception     The specified type member  Date  is not supported in LINQ to Entities  Only initializers  entity members  and entity navigation properties are supported     You can use something like this     DateTime date   DateTime Now Date     var result   from client in context clients                where client BirthDate  gt   date                       amp  amp  client BirthDate  lt  date AddDays 1                 select client

User · Answer

Use the class EntityFunctions for trimming the time portion   using System Data Objects       var bla    from log in context Contacts            where EntityFunctions TruncateTime log ModifiedDate      EntityFunctions TruncateTime today Date             select log  FirstOrDefault      Source  http   social msdn microsoft com Forums en-US csharpgeneral thread 84d4e18b-7545-419b-9826-53ff1a0e2a62   UPDATE  As of EF 6 0 and later EntityFunctions is replaced by DbFunctions

User · Answer

you can use DbFunctions TruncateTime   method for this   e   gt  DbFunctions TruncateTime e FirstDate Value     DbFunctions TruncateTime SecondDate

User · Answer

You can also use this   DbFunctions DiffDays date1  date2     0

User · Answer

NOTE  at the time of writing this answer  the EF-relation was unclear  that was edited into the question after this was written   For correct approach with EF  check Mandeeps answer     You can use the DateTime Date property to perform a date-only comparison   DateTime a   GetFirstDate    DateTime b   GetSecondDate     if  a Date Equals b Date            the dates are equal

User · Answer

You can user below link to compare 2 dates without time     private bool DateGreaterOrEqual DateTime dt1  DateTime dt2                        return DateTime Compare dt1 Date  dt2 Date   gt   0             private bool DateLessOrEqual DateTime dt1  DateTime dt2                        return DateTime Compare dt1 Date  dt2 Date   lt   0              the Compare function return 3 different values  -1 0 1 which means dt1 dt2  dt1 dt2  dt1

User · Answer

To do it in LINQ to Entities  you have to use supported methods   var year   someDate Year  var month       var q   from r in Context Records         where Microsoft VisualBasic DateAndTime Year r SomeDate     year                 amp  amp     month and day   Ugly  but it works  and it s done on the DB server

User · Answer

I think this could help you   I made an extension since I have to compare dates in repositories filled with EF data and so  Date was not an option since it is not implemented in LinqToEntities translation   Here is the code                lt summary gt          Check if two dates are same          lt  summary gt           lt typeparam name  TElement  gt Type lt  typeparam gt           lt param name  valueSelector  gt date field lt  param gt           lt param name  value  gt date compared lt  param gt           lt returns gt bool lt  returns gt      public Expression lt Func lt TElement  bool gt  gt  IsSameDate lt TElement gt  Expression lt Func lt TElement  DateTime gt  gt  valueSelector  DateTime value                ParameterExpression p   valueSelector Parameters Single             var antes   Expression GreaterThanOrEqual valueSelector Body  Expression Constant value Date  typeof DateTime              var despues   Expression LessThan valueSelector Body  Expression Constant value AddDays 1  Date  typeof DateTime              Expression body   Expression And antes  despues            return Expression Lambda lt Func lt TElement  bool gt  gt  body  p           then you can use it in this way     var today   DateTime Now   var todayPosts   from t in turnos Where IsSameDate lt Turno gt  t   gt  t MyDate  today                                         select t

User · Answer

Just always compare the Date property of DateTime  instead of the full date time   When you make your LINQ query  use date Date in the query  ie   var results   from c in collection               where c Date    myDateTime Date               select c

User · Answer

Without time than try like this   TimeSpan ts   new TimeSpan 23  59  59   toDate   toDate Add ts   List lt AuditLog gt  resultLogs         dbContext AuditLogs      Where al   gt  al Log Date  gt   fromDate  amp  amp  al Log Date  lt   toDate       ToList    return resultLogs

User · Answer

Note for Linq Users Coders  This should give you the exact comparison for checking if a date falls within range when working with input from a user - date picker for example     DateTime ri RequestX DateSatisfied  Date  gt   startdate Date  amp  amp            DateTime ri RequestX DateSatisfied  Date  lt   enddate Date   where startdate and enddate are values from a date picker

User · Answer

Try this    It works fine to compare Date properties between two DateTimes type   PS  It is a stopgap solution and a really bad practice  should never be used when you know that the database can bring thousands of records     query   query ToList                 Where x   gt  x FirstDate Date    SecondDate Date                AsQueryable

User · Answer

This is how I do this   DateTime date time to compare   DateTime Now    Compare only date parts context YourObject FirstOrDefault r   gt                  EntityFunctions TruncateTime r date     EntityFunctions TruncateTime date to compare

[c#] How to compare only date components from DateTime in EF?

Examples related to c#

Examples related to linq

Examples related to entity-framework

Examples related to linq-to-entities

Examples related to datetime-comparison