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()

ToList will execute query. You should implement your `TruncateTime` inside using simple operations that can be translated to SQL — ASpirin, Aug 07 '17 at 12:26
@EpicKip It returns "This function can only be invoked from LINQ to Entities." without any records. — OrElse, Aug 07 '17 at 12:30
@OrElse Hmm alright, then I'm afraid I can't help further. Hope someone can — EpicKip, Aug 07 '17 at 12:31
Possible duplicate of [Can't get EntityFunctions.TruncateTime() to work](https://stackoverflow.com/questions/16217016/cant-get-entityfunctions-truncatetime-to-work) — mjwills, Aug 07 '17 at 12:48
Please attribute properly and specify in the question the type of the target query provider (LINQ is too broad). The first exception message seems to indicate LINQ to SQL, but you need to confirm that (or just include the exception stack like you did in the second case). Also update the sample query with the exact `TruncateTime` method used (from the second exception message it seems to be from `System.Data.Entity.Core.Objects.EntityFunctions`, but again it will be good to include that in the query itself). Doing that will allow you to get answers and not guesses. — Ivan Stoev, Aug 11 '17 at 13:04

Ivan Stoev · Accepted Answer · 2017-08-11T13:47:39.040

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)

Why do you need a null check if you are using entityframework, doesn't sql handle it the same way? — johnny 5, Aug 11 '17 at 15:28
@johnny5 The problem is that looks like it's **not** Entity Framework, and I can't say for sure whether LINQ to SQL or EF Core translation would handle it automatically or not, so the suggested method should theoretically work for any provider. — Ivan Stoev, Aug 11 '17 at 15:37

score 1 · Answer 2 · answered Aug 07 '17 at 12:52

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;

Actually tried that before, but vs suggested that it is obsolute — OrElse, Aug 07 '17 at 21:26

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

2 Answers2