Entity Framework Core GroupBy Date

Question

In C# you can group by .Date:

db.History.GroupBy(x => x.Timestamp.Date)
          .Select(g => new { key = g.Key, aggregate = g.Count() })

However, the equivalent F# does not work:

db.History.GroupBy(fun x -> x.Timestamp.Date)
          .Select(fun g -> { Date = g.Key; Count = g.Count()} )

The relevant record:

type DateCount = {
    Date: DateTime
    Count: int
}

It throws the following error:

System.InvalidOperationException: The LINQ expression 'DbSet<HistoryEntity> .GroupBy( source: h => copyOfStruct => copyOfStruct.Date.Invoke(h.Timestamp), keySelector: h => h)' could not be translated. Either rewrite the query in a form that can be translated, or switch to client evaluation explicitly by inserting a call to either AsEnumerable(), AsAsyncEnumerable(), ToList(), or ToListAsync().

How can I group by date?

Asti · Accepted Answer · 2020-02-02T05:07:21.843

So in C#, when you use LINQ-to-SQL queries, you are using the extension methods on IQueryable<T>. If we look at the signature of say, the GroupBy method, you will see that the function signature is actually

IQueryable<TSource>.GroupBy<TSource,TKey>(Expression<Func<TSource,TKey>> keySelector)

What's going on? Expression<> is a special type - when the C# compiler spots an Expression<> type, the compiler builds an AST, and passes the AST object (of type Expression<Func<>>), instead of the usual delegate. The underlying functions are expected to inspect the AST and build whatever query expression is finally needed, like SQL for querying the database.

You can try it yourself:

Expression<Func<int>> getRandom = () => 4; //random, chosen by fair dice roll

You can inspect the properties of getRandom to see the AST. Since the magic happens in the C# compiler, when you do it in F#, this won't cut it.

To go into more detail, the F# compiler can recognize the Expression<>, but it does so by applying an implicit F# quotation - so you get an F# quotation wrapped method call that translates to the C# expression tree. (Sorry if, that was run on.)

F# has its own query comprehension builder for SQL. It lets you write the computation expressions like that of seq which translate to SQL queries. This works like you'd expect.

query {
    for record in db do
    select record
}

Great answer. Thanks Asti. – dharmatech Dec 10 '20 at 18:16 — dharmatech, Dec 10 '20 at 18:16

score 0 · Answer 2 · answered Jan 31 '20 at 14:13

Group by .Date works when using in a query expression.

query {
    for h in db.History do
    groupValBy h h.Timestamp.Date into g
    select {
        Date  = g.Key
        Count = g.Count()
    }
}

Code stolen from here.

If someone could explain why the query expression works but the LINQ version doesn't, I'd appreciate it :)

Entity Framework Core GroupBy Date

2 Answers2