Efcore: Introduce an IQueryable ToDictionary method (to align with async) and translate it

[kccsf]

The following query is not working:
```c#
var test = await this.dbcontext.MyEntity
.GroupBy(x => x.Status)
.ToDictionaryAsync(x => x.Key.Name, y => y?.Count() ?? 0);

If you are seeing an exception, include the full exceptions details (message and stack trace).

Exception message: EF.Property called with wrong property name
Stack trace: Microsoft.EntityFrameworkCore.Query.RelationalSqlTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)\r\n at Microsoft.EntityFrameworkCore.Query.RelationalSqlTranslatingExpressionVisitor.VisitBinary(BinaryExpression binaryExpression)\r\n at Microsoft.EntityFrameworkCore.SqlServer.Query.Internal.SqlServerSqlTranslatingExpressionVisitor.VisitBinary(BinaryExpression binaryExpression)\r\n at Microsoft.EntityFrameworkCore.Query.RelationalSqlTranslatingExpressionVisitor.Translate(Expression expression)\r\n at Microsoft.EntityFrameworkCore.Query.RelationalQueryableMethodTranslatingExpressionVisitor.WeakEntityExpandingExpressionVisitor.Expand(Expression source, MemberIdentity member)\r\n at Microsoft.EntityFrameworkCore.Query.RelationalQueryableMethodTranslatingExpressionVisitor.WeakEntityExpandingExpressionVisitor.VisitMember(MemberExpression memberExpression)\r\n at Microsoft.EntityFrameworkCore.Query.RelationalQueryableMethodTranslatingExpressionVisitor.WeakEntityExpandingExpressionVisitor.Expand(SelectExpression selectExpression, Expression lambdaBody)\r\n at Microsoft.EntityFrameworkCore.Query.RelationalQueryableMethodTranslatingExpressionVisitor.RemapLambdaBody(ShapedQueryExpression shapedQueryExpression, LambdaExpression lambdaExpression)\r\n at Microsoft.EntityFrameworkCore.Query.RelationalQueryableMethodTranslatingExpressionVisitor.TranslateGroupBy(ShapedQueryExpression source, LambdaExpression keySelector, LambdaExpression elementSelector, LambdaExpression resultSelector)\r\n at Microsoft.EntityFrameworkCore.Query.QueryableMethodTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)\r\n at Microsoft.EntityFrameworkCore.Query.RelationalQueryableMethodTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)\r\n at Microsoft.EntityFrameworkCore.Query.QueryCompilationContext.CreateQueryExecutorTResult\r\n at Microsoft.EntityFrameworkCore.Storage.Database.CompileQueryTResult\r\n at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.CompileQueryCoreTResult\r\n at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.<>c__DisplayClass12_01.<ExecuteAsync>b__0()\r\n at Microsoft.EntityFrameworkCore.Query.Internal.CompiledQueryCache.GetOrAddQueryCore[TFunc](Object cacheKey, Func1 compiler)\r\n at Microsoft.EntityFrameworkCore.Query.Internal.CompiledQueryCache.GetOrAddQuery[TResult](Object cacheKey, Func1 compiler)\r\n at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.ExecuteAsync[TResult](Expression query, CancellationToken cancellationToken)\r\n at Microsoft.EntityFrameworkCore.Query.Internal.EntityQueryProvider.ExecuteAsync[TResult](Expression expression, CancellationToken cancellationToken)\r\n at Microsoft.EntityFrameworkCore.Query.Internal.EntityQueryable1.GetAsyncEnumerator(CancellationToken cancellationToken)\r\n at System.Runtime.CompilerServices.ConfiguredCancelableAsyncEnumerable1.GetAsyncEnumerator()\r\n at Microsoft.EntityFrameworkCore.EntityFrameworkQueryableExtensions.<ToDictionaryAsync>d__1553.MoveNext()\r\n at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()\r\n at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)\r\n at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)\r\n at System.Runtime.CompilerServices.TaskAwaiter`1.GetResult()\r\n at Test.Service.DataService.d__31.MoveNext() in C:\Test\Visual Studio Projects\Test\Test.Service\DataService.cs:line 821

### Steps to reproduce

```c#
var test = await this.dbcontext.MyEntity
   .GroupBy(x => x.Status)
   .ToDictionaryAsync(x => x.Key.Name, y => y?.Count() ?? 0);

Further technical details

EF Core version: 3.0.0-preview8.19374.2
Database Provider: Microsoft.EntityFrameworkCore.SqlServer
Operating system: Windows 10
IDE: 16.2.0 Preview 4.0

[divega]

@kccsf do you expect the aggregate function (COUNT) to be evaluated in memory or on the server?

[kccsf]

@divega - Thanks for the response. I would expect it to be evaluated server side.

[ajcvickers]

@kccsf Do you have the same expectation for ToDictionary (i.e. non-async)?

[kccsf]

@ajcvickers - Yes; IMHO it is less confusing if async/non async functions perform in the same manner (server side or client side) unless there is a compelling reason that they shouldn't?

My preference of server side is purely down to not wishing to retrieve a potentially large amount of records when all I need is a count for a high level summary.

[divega]

@kccsf It is important for you to know that the lambda expressions passed to either ToDictionary or ToDictionaryAsync are never passed to EF Core, and therefore are never translated to the server.

This is because ToDictionary and ToDictionaryAsync are always evaluated on the client and the lambdas passed to them are of type Func<T> rather than Expression<Func<T>> expressions which could be parsed and translated by EF Core.

For ToDictionary this is a bit more "by-design" because it is actually a method on System.Linq.Enumerable that uses the IEnumerable<T> aspect of the query to enumerate all the results and then create the dictionary, but we defined ToDictionaryAsync as an operator over IQueryable<T> in EF Core, and we did not consider making its lambda arguments translatable.

There is an ongoing discussion on what to do about this in the long term. See https://github.com/aspnet/EntityFrameworkCore/issues/16838 for one possible way to address it.

[divega]

In the meantime, the correct way to write this query is:

var test = await this.dbcontext.MyEntity
   .GroupBy(x => x.Status)
   .Select(x => new {Name = x.Key.Name, Count = x.Count()})
   .ToDictionaryAsync(p => p.Name, q => q.Count);

(Note that I haven't tried this on a real application, so you could still run into other issues).

[kccsf]

@divega - Thanks for the explanation and the corrected query. An analyzer such as that mentioned in the thread to which you linked could indeed be helpful.

Unfortunately, the rewritten query still suffers from the original issue - EF.Property called with wrong property name.

```c#
var test = await this.dbcontext.MyEntity
.GroupBy(x => x.Status)
.Select(x => new { Name = x.Key.Name, Count = x.Count() })
.ToDictionaryAsync(x => x.Name, y => y.Count);

Slightly different stack trace:

Microsoft.EntityFrameworkCore.Query.RelationalSqlTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)
at Microsoft.EntityFrameworkCore.Query.RelationalSqlTranslatingExpressionVisitor.VisitBinary(BinaryExpression binaryExpression)
at Microsoft.EntityFrameworkCore.SqlServer.Query.Internal.SqlServerSqlTranslatingExpressionVisitor.VisitBinary(BinaryExpression binaryExpression)
at System.Linq.Expressions.BinaryExpression.Accept(ExpressionVisitor visitor)
at Microsoft.EntityFrameworkCore.Query.RelationalSqlTranslatingExpressionVisitor.Translate(Expression expression)
at Microsoft.EntityFrameworkCore.Query.RelationalQueryableMethodTranslatingExpressionVisitor.WeakEntityExpandingExpressionVisitor.Expand(Expression source, MemberIdentity member)
at Microsoft.EntityFrameworkCore.Query.RelationalQueryableMethodTranslatingExpressionVisitor.WeakEntityExpandingExpressionVisitor.VisitMember(MemberExpression memberExpression)
at Microsoft.EntityFrameworkCore.Query.RelationalQueryableMethodTranslatingExpressionVisitor.WeakEntityExpandingExpressionVisitor.Expand(SelectExpression selectExpression, Expression lambdaBody)
at Microsoft.EntityFrameworkCore.Query.RelationalQueryableMethodTranslatingExpressionVisitor.RemapLambdaBody(ShapedQueryExpression shapedQueryExpression, LambdaExpression lambdaExpression)
at Microsoft.EntityFrameworkCore.Query.RelationalQueryableMethodTranslatingExpressionVisitor.TranslateGroupBy(ShapedQueryExpression source, LambdaExpression keySelector, LambdaExpression elementSelector, LambdaExpression resultSelector)
at Microsoft.EntityFrameworkCore.Query.QueryableMethodTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)
at Microsoft.EntityFrameworkCore.Query.RelationalQueryableMethodTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)
at Microsoft.EntityFrameworkCore.Query.QueryableMethodTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)
at Microsoft.EntityFrameworkCore.Query.RelationalQueryableMethodTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)
at Microsoft.EntityFrameworkCore.Query.QueryCompilationContext.CreateQueryExecutorTResult
at Microsoft.EntityFrameworkCore.Storage.Database.CompileQueryTResult
at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.CompileQueryCoreTResult
at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.<>c__DisplayClass12_01.<ExecuteAsync>b__0() at Microsoft.EntityFrameworkCore.Query.Internal.CompiledQueryCache.GetOrAddQueryCore[TFunc](Object cacheKey, Func1 compiler)
at Microsoft.EntityFrameworkCore.Query.Internal.CompiledQueryCache.GetOrAddQueryTResult
```

[smitpatel]

Is Status a navigation? Then #15249

[kccsf]

@smitpatel - it is indeed. I'll keep an eye on that issue. Thanks

[divega]

@smitpatel wouldn't it be possible to workaround #15249 by explicitly grouping on the key of Status?

[smitpatel]

Yes, that works. If you project scalar property out of navigation (or even anonymous type if more than one property it would work.

[kccsf]

@divega - Do you mean like so?
c# var test = await this.dbcontext.MyEntity .GroupBy(x => x.Status.Id) ............
If so that does not help as I need the non grouped property x.Key.Name which would then not be available.

[smitpatel]

var test = await this.dbContext.MyEntity
    .GroupBy(x => x.Status.Id)
    .Select(g => new { g.Key, Count = g.Count()}
    .Join(this.dbContext.MyEntity.Select(x => x.Status), x => x.Key, y => y.Id, (x, y) => new { x.Name, y.Count})

[divega]

@kccsf, @smitpatel understood, thanks for the additional details. I am guessing that if the Name of Status is guaranteed somehow to be unique, you can use it directly as the key of the groups, simplifying the query to:

var test = await this.dbcontext.MyEntity
    .GroupBy(x => x.Status.Name)
    .Select(x => new { Name = x.Key, Count = x.Count() })
    .ToDictionaryAsync(x => x.Name, y => y.Count);

You may need to create additional database indexes for this query to be evaluated more efficiently on the server. I hope this helps.

[kccsf]

@divega / @smitpatel - Very kind but I already have a workaround in the form of FromSqlRaw, so unless you wish to have something here for others who hit this issue please don't waste your precious time on it!

@divega - Thanks but name is not unique.

@smitpatel - Thanks but fails in the same manner (EF.Property called with wrong property name):
c# var test = await this.dbcontext.MyEntity .GroupBy(x => x.Status.Id) .Select(g => new { g.Key, Count = g.Count() }) .Join( this.dbcontext.MyEntity.Select(x => x.Status), x => x.Key, y => y.Id, (x, y) => new { y.Name, x.Count }) .ToListAsync();

It is still the GroupBy failing (EF.Property called with wrong property name):
```c#
var test = await this.dbcontext.MyEntity
.GroupBy(x => x.Status.Id)
.Select(x => new { Id = x.Key, Count = x.Count() })
.ToListAsync();

[smitpatel]

Missed that part. GroupBy does not expand navigation so you would have to generate manual join.

[Shane32]

I'd like to see ToDictionary added and the arguments translated. I would never have expected that IQueryable.ToDictionaryAsync would not translate the expression. (I don't typically examine the parameters enough to notice that it accepts a Func rather than an Expression.) It would seem to be extremely simple to convert this to .Select.ToListAsync.ToDictionary within the EF library, if that's the ideal path at present, allowing for performance improvements in the future.

If, on the other hand, IQueryable.ToDictionaryAsync was not present, I would notice and write .Select.ToListAsync.ToDictionary in my user code. So I would consider it a poor design decision to add IQueryable.ToDictionaryAsync without it accepting expressions as parameters.

Here is a working sample of an enhanced ToDictionaryAsync function:

    public static class EnhancedExtensions
    {
        public static async Task<Dictionary<TKey, TElement>> EnhancedToDictionaryAsync<TSource, TKey, TElement>(this IQueryable<TSource> source, Expression<Func<TSource, TKey>> keySelector, Expression<Func<TSource, TElement>> elementSelector, CancellationToken cancellationToken = default)
        {
            if (source == null) throw new ArgumentNullException(nameof(source));
            if (keySelector == null) throw new ArgumentNullException(nameof(keySelector));
            if (elementSelector == null) throw new ArgumentNullException(nameof(elementSelector));
            var param = keySelector.Parameters[0];
            var elementSelectorBody = elementSelector.Body.Replace(elementSelector.Parameters[0], param);
            var type = typeof(ValueTuple<TKey, TElement>);
            var item1member = type.GetMember(nameof(ValueTuple<TKey, TElement>.Item1))[0];
            var item2member = type.GetMember(nameof(ValueTuple<TKey, TElement>.Item2))[0];
            var memberInit = Expression.MemberInit(
                Expression.New(typeof(ValueTuple<TKey, TElement>)),
                Expression.Bind(item1member, keySelector.Body),
                Expression.Bind(item2member, elementSelectorBody));
            var expr = Expression.Lambda<Func<TSource, ValueTuple<TKey, TElement>>>(
                memberInit,
                param);
            var results = await source.Select(expr).ToListAsync(cancellationToken);
            return results.ToDictionary(x => x.Item1, x => x.Item2);
        }

        private static Expression Replace(this Expression expression, ParameterExpression oldParameter, Expression newBody)
        {
            if (expression == null) throw new ArgumentNullException(nameof(expression));
            if (oldParameter == null) throw new ArgumentNullException(nameof(oldParameter));
            if (newBody == null) throw new ArgumentNullException(nameof(newBody));
            if (expression is LambdaExpression) throw new InvalidOperationException("The search & replace operation must be performed on the body of the lambda.");
            return (new ParameterReplacerVisitor(oldParameter, newBody)).Visit(expression);
        }

        private class ParameterReplacerVisitor : ExpressionVisitor
        {
            private ParameterExpression _source;
            private Expression _target;

            public ParameterReplacerVisitor(ParameterExpression source, Expression target)
            {
                _source = source;
                _target = target;
            }

            protected override Expression VisitParameter(ParameterExpression node)
            {
                // Replace the source with the target, visit other params as usual.
                return node.Equals(_source) ? _target : base.VisitParameter(node);
            }
        }
    }

I also ran a few tests on one of my production Azure databases which has 66,000 records and a bunch of columns:

            var a = (await _db.Parts.ToListAsync(this.HttpContext.RequestAborted)).ToDictionary(x => x.PartId, x => x.PartNumber);
            var b = (await _db.Parts.ToDictionaryAsync(x => x.PartId, x => x.PartNumber, this.HttpContext.RequestAborted));
            var c = (await _db.Parts.EnhancedToDictionaryAsync(x => x.PartId, x => x.PartNumber, this.HttpContext.RequestAborted));

As you can imagine, 'a' and 'b' took 3.5 to 4.5 seconds each, while 'c' took 60 to 300ms. It might be even faster if the PartNumber field was indexed.

For bonus points, I'd like to see ToHashSet and ToHashSetAsync added as well.