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 => DateTime.Compare(e.FirstDate.Value, SecondDate) >= 0

Answer 1

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.

Answer 2

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
}

Answer 3

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:

        /// <summary>
    /// Check if two dates are same
    /// </summary>
    /// <typeparam name="TElement">Type</typeparam>
    /// <param name="valueSelector">date field</param>
    /// <param name="value">date compared</param>
    /// <returns>bool</returns>
    public Expression<Func<TElement, bool>> IsSameDate<TElement>(Expression<Func<TElement, DateTime>> 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<Func<TElement, bool>>(body, p);
    }

then you can use it in this way.

 var today = DateTime.Now;
 var todayPosts = from t in turnos.Where(IsSameDate<Turno>(t => t.MyDate, today))
                                      select t);

Answer 4

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 >= date
                     && client.BirthDate < date.AddDays(1)
               select client;

Answer 5

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 
              && // month and day

Ugly, but it works, and it's done on the DB server.

Answer 6

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 => e.FirstDate.Value >= startDate && e.FirstDate.Value <= endDate

I think that should work

Answer 7

You can also use this:

DbFunctions.DiffDays(date1, date2) == 0

Answer 8

you can use DbFunctions.TruncateTime() method for this.

e => DbFunctions.TruncateTime(e.FirstDate.Value) == DbFunctions.TruncateTime(SecondDate);

Answer 9

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;

Answer 10

This is how I do this.

DateTime date_time_to_compare = DateTime.Now;
//Compare only date parts
context.YourObject.FirstOrDefault(r =>
                EntityFunctions.TruncateTime(r.date) == EntityFunctions.TruncateTime(date_to_compare));

Answer 11

//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 >= startdate.Date &&
        ((DateTime)ri.RequestX.DateSatisfied).Date <= enddate.Date

where startdate and enddate are values from a date picker.

Answer 12

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) >= 0;
        }

private bool DateLessOrEqual(DateTime dt1, DateTime dt2)
        {
            return DateTime.Compare(dt1.Date, dt2.Date) <= 0;
        }

the Compare function return 3 different values: -1 0 1 which means dt1>dt2, dt1=dt2, dt1

Answer 13

Without time than try like this:

TimeSpan ts = new TimeSpan(23, 59, 59);
toDate = toDate.Add(ts);
List<AuditLog> resultLogs = 
    _dbContext.AuditLogs
    .Where(al => al.Log_Date >= fromDate && al.Log_Date <= toDate)
    .ToList();
return resultLogs;

Answer 14

I have resolved error using EfCore FromSqlRaw method.

 var sql =
            $"select * from \"ProgressBooks\" where date(\"Date\") = date('{today.Date.ToString("yyyy-MM-dd")}') and \"GroupId\" = {groupId}";
        var todayProgressBook = _context.ProgressBooks.FromSqlRaw(sql).FirstOrDefault();

Answer 15

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 => x.FirstDate.Date == SecondDate.Date)
             .AsQueryable();