TruncateTime(System.Nullable`1[System.DateTime])' has no supported translation to SQL.'

Question

My data class is

public class Data
{
    public DateTime? Date { get; set; }
    public int Count { get; set; } 
}

And the following query

var queryable = db.MemberStats.Where(ms => ms.MemberId == User.Identity.GetUserId())
                .GroupBy(n => TruncateTime(n.Commited))
                .Select(g => new Data()
                    {
                         Date = g.Key,
                         Count = g.Count()
                    }
                ).ToList();

returns

System.NotSupportedException: 'Method 'System.Nullable`1[System.DateTime] 
TruncateTime(System.Nullable`1[System.DateTime])' has no supported translation to SQL.'

What is wrong with that? How can i fix this one?

update: if i add ToList() before GroupBy() i have

   at System.Data.Entity.Core.Objects.EntityFunctions.TruncateTime(Nullable`1 dateValue)
   at .Controllers.ChartController.<>c.<Index>b__1_0(MemberStat n) in \Controllers\ChartController.cs:line 30
   at System.Linq.Lookup`2.Create[TSource](IEnumerable`1 source, Func`2 keySelector, Func`2 elementSelector, IEqualityComparer`1 comparer)
   at System.Linq.GroupedEnumerable`3.GetEnumerator()
   at System.Linq.Enumerable.WhereSelectEnumerableIterator`2.MoveNext()
   at System.Linq.SystemCore_EnumerableDebugView`1.get_Items()

Answer 1

EnityFunctions.TruncateTime and DbFunctions.TruncateTime are Entity Framework (prior EF Core) specific methods which compensate the lack of support of DateTime.Date property when translating LINQ query to SQL. Think of them as placeholders inside the query expression tree which are recognized by the corresponding query provider during the query translation. But they are not directly executable (as you already discovered by switching to LINQ to Objects context in your second attempt) nor recognizable (hence not supported) by other query providers.

For LINQ to SQL queries (as it seems to be in your case according to the first exception message), and in general for query providers which support DateTime.Date property (LINQ to Objects, EF Core etc.) you should use that property instead - either directly, or for nullable DateTime properties combined with conditional operator and null check.

In your case, either

.GroupBy(n => n.Commited.Date)

or

.GroupBy(n => n.Commited != null ? n.Commited.Value.Date : (DateTime?)null)

Answer 2

can you please try this:

var queryable = db.MemberStats.Where(ms => ms.MemberId == User.Identity.GetUserId())
                .GroupBy(n => EntityFunctions.TruncateTime(n.Commited))
                .Select(g => new Data()
                    {
                         Date = g.Key,
                         Count = g.Count()
                    }
                ).ToList();

To use EntityFunctions.TruncateTime you'll need to reference the assembly System.Data.Entity and then include using System.Data.Objects;