Group by property that has a converted value throws exception

[dccs-pwurzinger]

File a bug

This bug adresses a scenario, in which an entity has a property, whichs value is being converted between its CLR representation and its DB representation.

Consider the following model:

public class Entity
{
    public Guid Id { get; set; }
    public SomeEnum SomeEnum { get; set; }
}
public enum SomeEnum
{
    Yes,
    No
}

//OnModelCreating, save the string-representation of the enum only
builder.Entity<Entity>()
       .Property(p => p.SomeEnum)
       .HasConversion(
           v => v.ToString(),
           v => Enum.Parse<SomeEnum>(v)
);

with somewhere a DbSet<Entity> being available.

If you want to simply project SomeEnum, that works fine:

DbSet<Entity>.Select(e => e.SomeEnum)

But grouping by that property throws an exception:

//throws InvalidOperationException: 'The LINQ expression [...] could not be translated. 
DbSet<Entity>.GroupBy(e => e.SomeEnum);

To answer that question in advance: I expected the query to group by the DB representation of the property (the string value, in this case).

Include your code

The bug should be reproducible using the code below, with Microsoft.EntityFrameworkCore.Sqlite being installed as NuGet reference (version see below).

using Microsoft.Data.Sqlite;
using Microsoft.EntityFrameworkCore;

public class Entity
{
    public Guid Id { get; set; }
    public SomeEnum SomeEnum { get; set; }
}
public enum SomeEnum
{
    Yes,
    No
}

public class Context : DbContext
{
    public Context(DbContextOptions options)
        : base(options) { }

    public DbSet<Entity> Entities { get; set; }

    protected override void OnModelCreating(ModelBuilder builder)
    {
        var entityBuilder = builder.Entity<Entity>();
        entityBuilder.Property(f => f.Id);
        entityBuilder.HasKey(f => f.Id);

        entityBuilder.Property(p => p.SomeEnum)
            .HasConversion(
                v => v.ToString(),
                v => Enum.Parse<SomeEnum>(v)
        );
    }
}
public class Program
{
    public static async Task Main()
    {
        using var connection = new SqliteConnection("Data Source=:memory:");
        await connection.OpenAsync();

        var builder = new DbContextOptionsBuilder<Context>().UseSqlite(connection);
        using var context = new Context(builder.Options);
        await context.Database.EnsureCreatedAsync();

        var select = await context.Entities.Select(f => f.SomeEnum).ToListAsync(); //Works
        var groupby = await context.Entities.GroupBy(f => f.SomeEnum).ToListAsync(); //Doesn't work
    }
}

Include stack traces

System.InvalidOperationException: 'The LINQ expression 'DbSet<Entity>()
    .GroupBy(f => f.SomeEnum)' 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 'AsEnumerable', 'AsAsyncEnumerable', 'ToList', or 'ToListAsync'. See https://go.microsoft.com/fwlink/?linkid=2101038 for more information.'

Include provider and version information

EF Core version: 6.0.4, but tried with latest 7.0-preview as well
Database provider: Microsoft.EntityFrameworkCore.Sqlite
Target framework: .NET 6.0
Operating system: Win10 Enterprise
IDE: VS 2022

[smitpatel]

Duplicate of #19929

[dccs-pwurzinger]

@smitpatel could you please point me to the sub-issue mentioned int #19929 that would cover this one? As far as I can tell, there's only #17653 open, but I cannot really see how it would be related.

[smitpatel]

Your query is exactly same form.

[dccs-pwurzinger]

Oh, I wasn't aware, that GroupBy is not supported for properties in general when being applied as final operator. My bad then. Thanks!

[roji]

@dccs-pwurzinger note that GroupBy is supported for properties, but you currently need to apply an aggregate operator afterwards - this is necessary in order to translate to a SQL GROUP BY clause:

var foo = context.Orders
    .GroupBy(o => o.CustomerID)
    .Select(g => new {
        CustomerID = g.Key,
        Count = g.Count()
    });

What's not currently supported is GroupBy without a subsequent aggregate operator (that's what #19929 tracks).

[dccs-pwurzinger]

Yeah that's what I meant, I should have read my answer before sending it - I will edit it afterwards, just to make sure somebody stumbling across that issue does not make wrong assumptions.

With the solution you mentioned it's possible to group by properties that have a value conversion as well. Thanks!

[ajcvickers]

This still fails with EF7 RC2. but is closed as a duplicate of #19929.

Unhandled exception. System.ArgumentException: Expression of type 'Microsoft.EntityFrameworkCore.Query.Internal.GroupBySingleQueryingEnumerable`2[System.Nullable`1[SomeEnum],Entity]' cannot be used for return type 'System.Collections.Generic.IAsyncEnumerable`1[System.Linq.IGrouping`2[SomeEnum,Entity]]'
   at System.Linq.Expressions.Expression.ValidateLambdaArgs(Type delegateType, Expression& body, ReadOnlyCollection`1 parameters, String paramName)
   at System.Linq.Expressions.Expression.Lambda[TDelegate](Expression body, String name, Boolean tailCall, IEnumerable`1 parameters)
   at System.Linq.Expressions.Expression.Lambda[TDelegate](Expression body, Boolean tailCall, IEnumerable`1 parameters)
   at System.Linq.Expressions.Expression.Lambda[TDelegate](Expression body, ParameterExpression[] parameters)
   at Microsoft.EntityFrameworkCore.Query.QueryCompilationContext.CreateQueryExecutor[TResult](Expression query)
   at Microsoft.EntityFrameworkCore.Storage.Database.CompileQuery[TResult](Expression query, Boolean async)
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.CompileQueryCore[TResult](IDatabase database, Expression query, IModel model, Boolean async)
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.<>c__DisplayClass12_0`1.<ExecuteAsync>b__0()
   at Microsoft.EntityFrameworkCore.Query.Internal.CompiledQueryCache.GetOrAddQuery[TResult](Object cacheKey, Func`1 compiler)
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.ExecuteAsync[TResult](Expression query, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Query.Internal.EntityQueryProvider.ExecuteAsync[TResult](Expression expression, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Query.Internal.EntityQueryable`1.GetAsyncEnumerator(CancellationToken cancellationToken)
   at System.Runtime.CompilerServices.ConfiguredCancelableAsyncEnumerable`1.GetAsyncEnumerator()
   at Microsoft.EntityFrameworkCore.EntityFrameworkQueryableExtensions.ToListAsync[TSource](IQueryable`1 source, CancellationToken cancellationToken)
   at Program.Main() in C:\local\code\AllTogetherNow\Daily\Daily.cs:line 50
   at Program.<Main>()