linq query distinct date

Question

On the query below how can remove de time part of the datetime column to get only the distinct dates?

using (var ctx = new DBConn())
{
  var q = ctx.Table.Select(r => r.datetimefield).Distinct();
}

Answer 1

You are stumbling over a fault with linq-to-sql or EF (I don't know which one you are using). The straight forward solution would be to use the DateTime.Date property, however, this is unsupported in both ORMs, it doesn't map to an SQL method.

You have two solutions:

1. eager evaluation of the query by using ToList:

   var q = ctx.Table.ToList().Select(r => r.datetimefield.Date).Distinct();
   

2. create a field detailing only the date component in the database. This is the way I usually go as you can stay in the database and execute the query there.