Query: Translate LINQ GroupBy to SQL GROUP BY where possible

[MarS0K]

I want to group some entities by uppercase string property. But:

The LINQ expression 'GroupBy([artist].Name.ToUpper(), [artist])' could not be translated and will be evaluated locally.

Query:

using (var musicContext = new MusicContext())
{
    var artistNames = musicContext.Artists
        .GroupBy(artist => artist.Name.ToUpper())
        .Select(group => group.Key)
        .ToList();
}

Am I wrong?

Steps to reproduce

Model:

public class Artist
{
    [Key]
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public int ID { get; set; }

    [Required]
    public string Name { get; set; }
}

public class MusicContext : DbContext
{
    public DbSet<Artist> Artists { get; set; }
}

Generated SQL (if needed):

SELECT "artist"."Name"
FROM "Artists" AS "artist"
ORDER BY upper("artist"."Name")

Expected SQL (something like that):

SELECT upper("artist"."Name") AS "Key"
FROM "Artists" AS "artist"
GROUP BY upper("artist"."Name")

Further technical details

EF Core version: Microsoft.EntityFrameworkCore 2.1.0
Database Provider: Microsoft.EntityFrameworkCore.Sqlite

[ajcvickers]

@smitpatel to de-dupe.

[smitpatel]

Easy work-around is to use anonymous type for key selector instead of scalar property like that.

[smitpatel]

Currently this does not get translated because we don't process keys other than EF property methods. We can remove that restrictions.

[MarS0K]

@smitpatel Ok, I got it. Pretty nice workaround. Thank you.
It's pretty simple if you create key selector expression like that:

.GroupBy(artist => artist.Name.ToUpper())

But if:

.GroupBy(ProcessGroupKey(m_groupKeyToken.DuplicateGroupKey))

public interface IDuplicateGroupKeyToken<T>
{
    Expression<Func<T, string>> DuplicateGroupKey { get; }
}
public class ArtistDuplicateGroupKeyToken : IDuplicateGroupKeyToken<Artist>
{
    public Expression<Func<Artist, string>> DuplicateGroupKey
        => artist => artist.Name;
}

private readonly IDuplicateGroupKeyToken<Artist> m_groupKeyToken;

private Expression<Func<T, string>> ProcessGroupKey(Expression<Func<T, string>> expression)
    => ExtendExpression(ProcessedGroupKeyExpression, expression);

private Expression<Func<string, string>> ProcessedGroupKeyExpression
    => groupKey => groupKey.Trim();

public Expression<Func<TContainer, TResult>> ExtendExpression<TContainer, TItem, TResult>(
    Expression<Func<TItem, TResult>> getResultExpression,
    Expression<Func<TContainer, TItem>> getItemExpression)
{
    var invokeExpression = Expression.Invoke(
        getResultExpression, // groupKey => groupKey.Trim();
        getItemExpression.Body // artist => artist.Name;
    );

    return Expression.Lambda<Func<TContainer, TResult>>(
        body: invokeExpression, // artist => artist.Name.Trim();
        parameters: getItemExpression.Parameters
    );
}

It's much harder.

[smitpatel]

@MarS0K - That is true hence issue is still open. This is some enhancement which we would make in future release. I gave work-around till this is fixed in public release.