ToDictionary won`t work, if called on navigation property

[riberk]

Exception message and stack trace

System.InvalidOperationException : When called from 'VisitLambda', rewriting a node of type 'System.Linq.Expressions.ParameterExpression' must return a non-null value of the same type. Alternatively, override 'VisitLambda' and change it to not visit children of this type.
   at System.Linq.Expressions.ExpressionVisitor.VisitAndConvert[T](T node, String callerName)
   at System.Dynamic.Utils.ExpressionVisitorUtils.VisitParameters(ExpressionVisitor visitor, IParameterProvider nodes, String callerName)
   at System.Linq.Expressions.ExpressionVisitor.VisitLambda[T](Expression`1 node)
   at System.Linq.Expressions.Expression`1.Accept(ExpressionVisitor visitor)
   at System.Linq.Expressions.ExpressionVisitor.Visit(Expression node)
   at Microsoft.EntityFrameworkCore.Query.Internal.RelationalProjectionBindingExpressionVisitor.Visit(Expression expression)
   at System.Dynamic.Utils.ExpressionVisitorUtils.VisitArguments(ExpressionVisitor visitor, IArgumentProvider nodes)
   at System.Linq.Expressions.ExpressionVisitor.VisitMethodCall(MethodCallExpression node)
   at System.Linq.Expressions.MethodCallExpression.Accept(ExpressionVisitor visitor)
   at System.Linq.Expressions.ExpressionVisitor.Visit(Expression node)
   at Microsoft.EntityFrameworkCore.Query.Internal.RelationalProjectionBindingExpressionVisitor.Visit(Expression expression)
   at Microsoft.EntityFrameworkCore.Query.Internal.RelationalProjectionBindingExpressionVisitor.Translate(SelectExpression selectExpression, Expression expression)
   at Microsoft.EntityFrameworkCore.Query.RelationalQueryableMethodTranslatingExpressionVisitor.TranslateSelect(ShapedQueryExpression source, LambdaExpression selector)
   at Microsoft.EntityFrameworkCore.Query.QueryableMethodTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)
   at Microsoft.EntityFrameworkCore.Query.RelationalQueryableMethodTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)
   at System.Linq.Expressions.MethodCallExpression.Accept(ExpressionVisitor visitor)
   at System.Linq.Expressions.ExpressionVisitor.Visit(Expression node)
   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__DisplayClass9_0`1.<Execute>b__0()
   at Microsoft.EntityFrameworkCore.Query.Internal.CompiledQueryCache.GetOrAddQueryCore[TFunc](Object cacheKey, Func`1 compiler)
   at Microsoft.EntityFrameworkCore.Query.Internal.CompiledQueryCache.GetOrAddQuery[TResult](Object cacheKey, Func`1 compiler)
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.Execute[TResult](Expression query)
   at Microsoft.EntityFrameworkCore.Query.Internal.EntityQueryProvider.Execute[TResult](Expression expression)
   at Microsoft.EntityFrameworkCore.Query.Internal.EntityQueryable`1.GetEnumerator()
   at System.Collections.Generic.LargeArrayBuilder`1.AddRange(IEnumerable`1 items)
   at System.Collections.Generic.EnumerableHelpers.ToArray[T](IEnumerable`1 source)
   at System.Linq.Enumerable.ToArray[TSource](IEnumerable`1 source)
   at MyContextTests.ContextTests.InnerToDictionary() in /path/to/file.cs

Steps to reproduce

public class ContextTests
{
    public class SimpleCtx : DbContext
    {
        public SimpleCtx([NotNull] DbContextOptions options) : base(options)
        {
        }

        public DbSet<ParentEntity> Parents { get; set; }
        public DbSet<ChildEntity> Children { get; set; }
    }

    public class ParentEntity
    {
        public string Id { get; set; }

        public ICollection<ChildEntity> Children { get; set; }
    }

    public class ChildEntity
    {
        public string Id { get; set; }

        public string ParentId { get; set; }

        public ParentEntity Parent { get; set; }
    }

    [Fact]
    public void InnerToDictionary()
    {
        var dbContextOptionsBuilder = new DbContextOptionsBuilder<SimpleCtx>()
            .UseNpgsql("con");
        var ctx = new SimpleCtx(dbContextOptionsBuilder.Options);
        var res = ctx.Parents.Select(p => p.Children.ToDictionary(c => c.Id, c => c.ParentId)).ToArray();
    }

}

Further technical details

EF Core version: 3.0.0 Database provider: Npgsql.EntityFrameworkCore.PostgreSQL Target framework: NET Core 3.0 Operating system: Manjaro linux IDE: JetBrains Rider

[ajcvickers]

@smitpatel Is the the suggested workaround?

var res = ctx.Parents.AsEnumerable().Select(p => p.Children.ToDictionary(c => c.Id, c => c.ParentId)).ToArray();

Note that these both throw in the same way:

var res = ctx.Parents.Select(p => p.Children.AsEnumerable().ToDictionary(c => c.Id, c => c.ParentId)).ToArray();
var res = ctx.Parents.Select(p => p.Children.ToList().ToDictionary(c => c.Id, c => c.ParentId)).ToArray();

[smitpatel]

Lambda's inside ToDictionary causes the error. May be we can fix that some day.

Work-around:

// The easiest to understand
var res = ctx.Parents.Include(p => p.Children).AsEnumerable().Select(p => p.Children.ToDictionary(c => c.Id, c => c.ParentId)).ToArray();

// The most accurate data transfer
var res = ctx.Parents.Select(p => p.Children.Select(c => new { c.Id, c.ParentId}).ToList()).AsEnumerable().Select(e => e.ToDictionary(c => c.Id, c => c.ParentId)).ToArray();

[ajcvickers]

Notes for team: I converted this to run against EF6 to check the behavior there. EF6 also throws. There's also a Stack Overflow thread that indicates L2S also throws: https://stackoverflow.com/questions/245168/linq-to-sql-todictionary

/usr/share/dotnet/dotnet /home/ajcvickers/AllTogetherNow/SixFour/bin/Debug/netcoreapp3.1/SixFour.dll
Unhandled exception. System.NotSupportedException: LINQ to Entities does not recognize the method 'System.Collections.Generic.Dictionary`2[System.String,System.String] ToDictionary[ChildEntity,String,String](System.Collections.Generic.IEnumerable`1[ChildEntity], System.Func`2[ChildEntity,System.String], System.Func`2[ChildEntity,System.String])' method, and this method cannot be translated into a store expression.
   at System.Data.Entity.Core.Objects.ELinq.ExpressionConverter.MethodCallTranslator.DefaultTranslator.Translate(ExpressionConverter parent, MethodCallExpression call)
   at System.Data.Entity.Core.Objects.ELinq.ExpressionConverter.MethodCallTranslator.TypedTranslate(ExpressionConverter parent, MethodCallExpression linq)
   at System.Data.Entity.Core.Objects.ELinq.ExpressionConverter.TypedTranslator`1.Translate(ExpressionConverter parent, Expression linq)
   at System.Data.Entity.Core.Objects.ELinq.ExpressionConverter.TranslateExpression(Expression linq)
   at System.Data.Entity.Core.Objects.ELinq.ExpressionConverter.TranslateLambda(LambdaExpression lambda, DbExpression input)
   at System.Data.Entity.Core.Objects.ELinq.ExpressionConverter.TranslateLambda(LambdaExpression lambda, DbExpression input, DbExpressionBinding& binding)
   at System.Data.Entity.Core.Objects.ELinq.ExpressionConverter.MethodCallTranslator.OneLambdaTranslator.Translate(ExpressionConverter parent, MethodCallExpression call, DbExpression& source, DbExpressionBinding& sourceBinding, DbExpression& lambda)
   at System.Data.Entity.Core.Objects.ELinq.ExpressionConverter.MethodCallTranslator.SelectTranslator.Translate(ExpressionConverter parent, MethodCallExpression call)
   at System.Data.Entity.Core.Objects.ELinq.ExpressionConverter.MethodCallTranslator.SequenceMethodTranslator.Translate(ExpressionConverter parent, MethodCallExpression call, SequenceMethod sequenceMethod)
   at System.Data.Entity.Core.Objects.ELinq.ExpressionConverter.MethodCallTranslator.TypedTranslate(ExpressionConverter parent, MethodCallExpression linq)
   at System.Data.Entity.Core.Objects.ELinq.ExpressionConverter.TypedTranslator`1.Translate(ExpressionConverter parent, Expression linq)
   at System.Data.Entity.Core.Objects.ELinq.ExpressionConverter.TranslateExpression(Expression linq)
   at System.Data.Entity.Core.Objects.ELinq.ExpressionConverter.Convert()
   at System.Data.Entity.Core.Objects.ELinq.ELinqQueryState.GetExecutionPlan(Nullable`1 forMergeOption)
   at System.Data.Entity.Core.Objects.ObjectQuery`1.<>c__DisplayClass41_0.<GetResults>b__1()
   at System.Data.Entity.Core.Objects.ObjectContext.ExecuteInTransaction[T](Func`1 func, IDbExecutionStrategy executionStrategy, Boolean startLocalTransaction, Boolean releaseConnectionOnSuccess)
   at System.Data.Entity.Core.Objects.ObjectQuery`1.<>c__DisplayClass41_0.<GetResults>b__0()
   at System.Data.Entity.SqlServer.DefaultSqlExecutionStrategy.Execute[TResult](Func`1 operation)
   at System.Data.Entity.Core.Objects.ObjectQuery`1.GetResults(Nullable`1 forMergeOption)
   at System.Data.Entity.Core.Objects.ObjectQuery`1.<System.Collections.Generic.IEnumerable<T>.GetEnumerator>b__31_0()
   at System.Data.Entity.Internal.LazyEnumerator`1.MoveNext()
   at System.Collections.Generic.LargeArrayBuilder`1.AddRange(IEnumerable`1 items)
   at System.Collections.Generic.EnumerableHelpers.ToArray[T](IEnumerable`1 source)
   at System.Linq.Enumerable.ToArray[TSource](IEnumerable`1 source)
   at ThreeOne.Main() in /home/ajcvickers/AllTogetherNow/SixFour/SixFour.cs:l

public static class ThreeOne
{
    public static void Main()
    {
        using (var context = new SomeDbContext())
        {
            context.Parents.AddRange(new List<ParentEntity>
            {
                new ParentEntity
                {
                    Id = "P1", Children = new List<ChildEntity>
                    {
                        new ChildEntity
                        {
                            Id = "C1"
                        },
                        new ChildEntity
                        {
                            Id = "C2"
                        },
                        new ChildEntity
                        {
                            Id = "C3"
                        }
                    }
                },
                new ParentEntity
                {
                    Id = "P2", Children = new List<ChildEntity>
                    {
                        new ChildEntity
                        {
                            Id = "C4"
                        },
                        new ChildEntity
                        {
                            Id = "C5"
                        },
                        new ChildEntity
                        {
                            Id = "C6"
                        }
                    }
                }
            });

            context.SaveChanges();
        }

        using (var context = new SomeDbContext())
        {
            var res = context.Parents.Select(p => p.Children.ToDictionary(c => c.Id, c => c.ParentId)).ToArray();
        }
    }
}

public class SomeDbContext : DbContext
{
    static SomeDbContext()
    {
        Database.SetInitializer(new DropCreateDatabaseAlways<SomeDbContext>());
    }

    public SomeDbContext()
        : base(Your.SqlServerConnectionString)
    {
    }

    public DbSet<ParentEntity> Parents { get; set; }
    public DbSet<ChildEntity> Children { get; set; }
}

public class ParentEntity
{
    public string Id { get; set; }

    public ICollection<ChildEntity> Children { get; set; }
}

public class ChildEntity
{
    public string Id { get; set; }

    public string ParentId { get; set; }

    public ParentEntity Parent { get; set; }
}

[radistmorse]

Even easier workaround:

var res = ctx.Parents.Select(p => new Dictionary<int, int>(p.Children.Select(c => new KeyValuePair<int, int>(c.Id, c.ParentId)))).ToArray();

[TanvirArjel]

The following code also not working in EF Core 5.0:

List<UserDto> userList = await _dbContext.Set<ApplicationUser>()
            .Select(au => new UserDto
            {
                Id = au.Id,
                FullName = au.FullName,
                UserName = au.UserName,
                Email = au.Email,
                IsActive = au.IsActive,
                Roles = au.UserRoles.Select(ur => new { ur.RoleId, ur.Role.Name }).ToList().ToDictionary(q => q.RoleId, q => q.Name)
            }).OrderBy(au => au.UserName).ToListAsync();

Throwing the following error:

System.InvalidOperationException: The LINQ expression 'q' 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'.