Querying for the date part of a DateTime value with Entity Framework in C#

Your task with .NET: take a SQL Server database table full of DateTime values (with dates and times), and figure out distinct dates from that data using LINQ and Entity Framework.

How would you solve this issue? Your initial attempt might be something along these lines:

var dateData =
    (from tbl in entities.SomeTableWithDateTimes
     orderby tbl.DateTimeField
     select tbl.DateTimeField.Value.Date).Distinct();

This looks like a fine query, and the select clause refers to the DateTime value's Date property, which contains the date value without any time. However, if you try to run the above query, you will get the following runtime exception:

NotSupportedException: The specified type member 'Date' is not
supported in LINQ to Entities. Only initializers, entity members,
and entity navigation properties are supported.

The Date property is one of those properties listed as not supported on MSDN documentation. Luckily, you can use something called entity functions to truncate (strip out) a time value from a date.

The good news is that using an entity function called TrunaceTime is easy. You need to simple change the way your select clause is written, like this:

var dateData =
      (from tbl in entities.SomeTableWithDateTimes
       orderby tbl.DateTimeField
       select DbFunctions.TruncateTime(
           tbl.DateTimeField).Value).Distinct();

The DbFunctions class lives in the System.Data.Entity namespace, so you will need to add the following using statement as well:

using System.Data.Entity;

Note that the DbFunctions class is part of Entity Framework 6.0. In previous EF versions, these functions were called EntityFunctions, and they lived in the namespace System.Data.Entity.Core.Objects. This older class is now marked as deprecated, so use DbFunctions from now on.