Wrong DateTime comparing in EF Core when column type date-CodePudding

I have entity class:

public class Test
{
  public Guid Id { get; set; }
  
  [Column(TypeName = "date")]
  [DataType(DataType.Date)]
  public DateTime Date {get; set;}
}

I use PostgreSQL. In database Date column have type date. And without this attribute ([Column(TypeName = "date")]) column have type timestamp.

Problems starts when i try get entities from dbContext:

public List<Test> GetEntities([DataType(DataType.Date)] DateTime date)
{
  var list = _context
      .Where(x => x.Date == date)
      .ToList()

    return list;
}

This method always returns empty list. But if in linq write:

_context.Where(x => x.Date.Year == date.Year 
                        && x.Date.Month == date.Month 
                        && x.Date.Day == date.Day)

It`s works correct and return some value. Why in first version it's always returns empty list?

CodePudding user response：

Dot net doesn't have a Date data type, so .Where(x => x.Date == date) compares the date and time details.
Since you only want to compare the date portion of the DateTime, use should make sure the time portion is always equal - you can do that by using the Date property (which gives you a DateTime value with 00:00:00 as its time portion:

public List<Test> GetEntities([DataType(DataType.Date)] DateTime date)
  => _context
        .Where(x => x.Date.Date == date.Date)
        .ToList();

CodePudding user response：

Try to compare by date range:

public List<Test> GetEntities([DataType(DataType.Date)] DateTime date)
{
    var startDate = date.Date;
    var endDate = startDate.AddDays(1);

    var list = _context
       .Where(x => x.Date >= startDate && x.Date < endDate)
       .ToList();

    return list;
}