Efcore: Query with nested select statement no longer work in EF Core 3.0 when using owned types

Created on 24 Oct 2019 · 7Comments · Source: dotnet/efcore

In EF Core 2.2, the following linq query was able to run but is no longer able to run in EF Core 3.0:

await db.Warehouses.Select(x => new WarehouseModel
{
    WarehouseCode = x.WarehouseCode,
    DestinationCountryCodes = x.DestinationCountries.Select(c => c.CountryCode).ToArray()
}).AsNoTracking().ToArrayAsync();

In EF Core 2.2 this produced the following SQL:

SELECT [t].[WarehouseCode], [x.DestinationCountries].[CountryCode], [x.DestinationCountries].[WarehouseCode]
FROM [Fulfillment].[WarehouseDestinationCountry] AS [x.DestinationCountries]
INNER JOIN (
    SELECT [x0].[WarehouseCode]
    FROM [Fulfillment].[Warehouse] AS [x0]
) AS [t] ON [x.DestinationCountries].[WarehouseCode] = [t].[WarehouseCode]
ORDER BY [t].[WarehouseCode]

In EF Core 3.0 this throws the following exception:

ArgumentException
Property 'System.String CountryCode' is not defined for type 'System.String' (Parameter 'property')
   at System.Linq.Expressions.Expression.Property(Expression expression, PropertyInfo property)
   at Microsoft.EntityFrameworkCore.Query.ReplacingExpressionVisitor.VisitMember(MemberExpression memberExpression)
   at Microsoft.EntityFrameworkCore.Query.ReplacingExpressionVisitor.Visit(Expression expression)
   at Microsoft.EntityFrameworkCore.Query.ReplacingExpressionVisitor.Replace(Expression original, Expression replacement, Expression tree)
   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 Microsoft.EntityFrameworkCore.Query.QueryableMethodTranslatingExpressionVisitor.TranslateSubquery(Expression expression)
   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 Microsoft.EntityFrameworkCore.Query.Internal.RelationalProjectionBindingExpressionVisitor.Visit(Expression expression)
   at Microsoft.EntityFrameworkCore.Query.Internal.RelationalProjectionBindingExpressionVisitor.VisitMemberAssignment(MemberAssignment memberAssignment)
   at System.Linq.Expressions.ExpressionVisitor.VisitMemberBinding(MemberBinding node)
   at Microsoft.EntityFrameworkCore.Query.Internal.RelationalProjectionBindingExpressionVisitor.VisitMemberInit(MemberInitExpression memberInitExpression)
   at System.Linq.Expressions.MemberInitExpression.Accept(ExpressionVisitor visitor)
   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 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.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.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 Microsoft.EntityFrameworkCore.EntityFrameworkQueryableExtensions.ToArrayAsync[TSource](IQueryable`1 source, CancellationToken cancellationToken)

Further technical details

EF Core version: 3.0
Database provider: Microsoft.EntityFrameworkCore.SqlServer and Microsoft.EntityFrameworkCore.InMemory
Target framework: .NET Core 3.0
Operating system:
IDE: Visual Studio 2019 16.4 Preview 2 and LINQPad

area-query customer-reported type-bug

Source

ChristopherHaws

Most helpful comment

Hi @ajcvickers, I apologize for not including a code sample in the original post, i was under a time pressure that day. Here is a code sample that repro's the issue. I believe it has something to do with owned types. I will update the title of the issue to reflect this.

async Task Main()
{
    using (var context = new BloggingContext())
    {
        context.Database.EnsureDeleted();
        context.Database.EnsureCreated();

        context.Add(new Warehouse
        {
            WarehouseCode = "W001",
            DestinationCountries =
            {
                new WarehouseDestinationCountry { CountryCode = "US" },
                new WarehouseDestinationCountry { CountryCode = "CA" }
            }
        });

        context.SaveChanges();
    }

    using (var context = new BloggingContext())
    {
        var results = await context.Warehouses.Select(x => new WarehouseModel
        {
            WarehouseCode = x.WarehouseCode,
            DestinationCountryCodes = x.DestinationCountries.Select(c => c.CountryCode).ToArray()
        }).AsNoTracking().ToArrayAsync();
    }
}

public sealed class Warehouse
{
    public String WarehouseCode { get; set; }
    public ICollection<WarehouseDestinationCountry> DestinationCountries { get; set; } = new HashSet<WarehouseDestinationCountry>();
}

public sealed class WarehouseDestinationCountry
{
    public String WarehouseCode { get; set; }
    public String CountryCode { get; set; }
}

public class WarehouseModel
{
    public String WarehouseCode { get; set; }

    public ICollection<String> DestinationCountryCodes { get; set; }
}

public class BloggingContext : DbContext
{
    private readonly ILoggerFactory Logger = LoggerFactory.Create(c => c.AddConsole());

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        optionsBuilder
            .UseLoggerFactory(Logger)
            .UseSqlServer(@"Server=(localdb)\mssqllocaldb;Database=Test;ConnectRetryCount=0");
    }

    public DbSet<Warehouse> Warehouses { get; set; }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Warehouse>(warehouse =>
        {
            warehouse.ToTable("Warehouse", "Fulfillment");
            warehouse.HasKey(x => x.WarehouseCode).IsClustered();

            warehouse.OwnsMany(x => x.DestinationCountries, destination =>
            {
                destination.ToTable("WarehouseDestinationCountry", "Fulfillment");
                destination.HasKey(x => x.CountryCode).IsClustered();
                destination.WithOwner().HasForeignKey(x => x.WarehouseCode);
            });
        });
    }
}

If I update the entity configuration to the following, the code works:

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<Warehouse>(warehouse =>
    {
        warehouse.ToTable("Warehouse", "Fulfillment");
        warehouse.HasKey(x => x.WarehouseCode).IsClustered();
        warehouse.HasMany(x => x.DestinationCountries).WithOne().HasForeignKey(x => x.WarehouseCode);
    });

    modelBuilder.Entity<WarehouseDestinationCountry>(destination =>
    {
        destination.ToTable("WarehouseDestinationCountry", "Fulfillment");
        destination.HasKey(x => x.CountryCode).IsClustered();
    });
}

ChristopherHaws on 29 Oct 2019

👍2

All 7 comments

@ChristopherHaws I have not been able to reproduce this--see my code below. Please post a small, runnable project/solution or complete code listing that demonstrates the behavior you are seeing.

```C#
public class Warehouse
{
public int Id { get; set; }
public int WarehouseCode { get; set; }
public ICollection DestinationCountries { get; set; }
}

public class Country
{
public int Id { get; set; }
public int CountryCode { get; set; }
}

public class WarehouseModel
{
public int WarehouseCode { get; set; }

public ICollection<int> DestinationCountryCodes { get; set; }

}

public class BloggingContext : DbContext
{
private readonly ILoggerFactory Logger = LoggerFactory.Create(c => c.AddConsole());

protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
    optionsBuilder
        .UseLoggerFactory(Logger)
        .UseSqlServer(@"Server=(localdb)\mssqllocaldb;Database=Test;ConnectRetryCount=0");
}

public DbSet<Warehouse> Warehouses { get; set; }

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
}

}

public class Program
{
public static async Task Main()
{
using (var context = new BloggingContext())
{
context.Database.EnsureDeleted();
context.Database.EnsureCreated();

        context.Add(new Warehouse
        {
            WarehouseCode = 7,
            DestinationCountries = new List<Country>
            {
                new Country {CountryCode = 11},
                new Country {CountryCode = 22}
            }
        });

        context.SaveChanges();
    }

    using (var context = new BloggingContext())
    {
        var results = await context.Warehouses.Select(x => new WarehouseModel
        {
            WarehouseCode = x.WarehouseCode,
            DestinationCountryCodes = x.DestinationCountries.Select(c => c.CountryCode).ToArray()
        }).AsNoTracking().ToArrayAsync();
    }
}

}

```

ajcvickers on 29 Oct 2019

async Task Main()
{
    using (var context = new BloggingContext())
    {
        context.Database.EnsureDeleted();
        context.Database.EnsureCreated();

        context.Add(new Warehouse
        {
            WarehouseCode = "W001",
            DestinationCountries =
            {
                new WarehouseDestinationCountry { CountryCode = "US" },
                new WarehouseDestinationCountry { CountryCode = "CA" }
            }
        });

        context.SaveChanges();
    }

    using (var context = new BloggingContext())
    {
        var results = await context.Warehouses.Select(x => new WarehouseModel
        {
            WarehouseCode = x.WarehouseCode,
            DestinationCountryCodes = x.DestinationCountries.Select(c => c.CountryCode).ToArray()
        }).AsNoTracking().ToArrayAsync();
    }
}

public sealed class Warehouse
{
    public String WarehouseCode { get; set; }
    public ICollection<WarehouseDestinationCountry> DestinationCountries { get; set; } = new HashSet<WarehouseDestinationCountry>();
}

public sealed class WarehouseDestinationCountry
{
    public String WarehouseCode { get; set; }
    public String CountryCode { get; set; }
}

public class WarehouseModel
{
    public String WarehouseCode { get; set; }

    public ICollection<String> DestinationCountryCodes { get; set; }
}

public class BloggingContext : DbContext
{
    private readonly ILoggerFactory Logger = LoggerFactory.Create(c => c.AddConsole());

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        optionsBuilder
            .UseLoggerFactory(Logger)
            .UseSqlServer(@"Server=(localdb)\mssqllocaldb;Database=Test;ConnectRetryCount=0");
    }

    public DbSet<Warehouse> Warehouses { get; set; }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Warehouse>(warehouse =>
        {
            warehouse.ToTable("Warehouse", "Fulfillment");
            warehouse.HasKey(x => x.WarehouseCode).IsClustered();

            warehouse.OwnsMany(x => x.DestinationCountries, destination =>
            {
                destination.ToTable("WarehouseDestinationCountry", "Fulfillment");
                destination.HasKey(x => x.CountryCode).IsClustered();
                destination.WithOwner().HasForeignKey(x => x.WarehouseCode);
            });
        });
    }
}

If I update the entity configuration to the following, the code works:

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<Warehouse>(warehouse =>
    {
        warehouse.ToTable("Warehouse", "Fulfillment");
        warehouse.HasKey(x => x.WarehouseCode).IsClustered();
        warehouse.HasMany(x => x.DestinationCountries).WithOne().HasForeignKey(x => x.WarehouseCode);
    });

    modelBuilder.Entity<WarehouseDestinationCountry>(destination =>
    {
        destination.ToTable("WarehouseDestinationCountry", "Fulfillment");
        destination.HasKey(x => x.CountryCode).IsClustered();
    });
}

ChristopherHaws on 29 Oct 2019

👍2

@smitpatel Still repros for me on nghtlies.

ajcvickers on 29 Oct 2019

@ChristopherHaws the workaround you posted basically referenced the table for the owned complex type, correct?

klawrow on 7 Feb 2020

@klawrow If I remember correctly, I was pointing out that this works properly when not using owned types (i.e. WarehouseDestinationCountry is its own entity). If you use owned types, it no longer works. I just verified, this is still an issue in version 3.1.1.

ChristopherHaws on 12 Feb 2020

👍1

@ChristopherHaws Thanks for the clarification. Curious to know, are you using an in-memory database or localdb?

klawrow on 12 Feb 2020

@klawrow I use both, in-memory for unit tests and localdb/azure sql for runtime. This issue happens using both (albeit they have different exception messages). The in-memory exception is in the original post.

SQL Provider Exception:


EF.Property called with wrong property name.
   at Microsoft.EntityFrameworkCore.Query.RelationalSqlTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)
   at System.Linq.Expressions.MethodCallExpression.Accept(ExpressionVisitor visitor)
   at System.Linq.Expressions.ExpressionVisitor.Visit(Expression node)
   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 System.Linq.Expressions.ExpressionVisitor.Visit(Expression node)
   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 System.Linq.Expressions.ExpressionVisitor.Visit(Expression node)
   at Microsoft.EntityFrameworkCore.Query.RelationalSqlTranslatingExpressionVisitor.Translate(Expression expression)
   at Microsoft.EntityFrameworkCore.Query.RelationalQueryableMethodTranslatingExpressionVisitor.TranslateExpression(Expression expression)
   at Microsoft.EntityFrameworkCore.Query.RelationalQueryableMethodTranslatingExpressionVisitor.TranslateLambdaExpression(ShapedQueryExpression shapedQueryExpression, LambdaExpression lambdaExpression)
   at Microsoft.EntityFrameworkCore.Query.RelationalQueryableMethodTranslatingExpressionVisitor.TranslateWhere(ShapedQueryExpression source, LambdaExpression predicate)
   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.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.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.QueryableMethodTranslatingExpressionVisitor.TranslateSubquery(Expression expression)
   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.VisitMemberAssignment(MemberAssignment memberAssignment)
   at System.Linq.Expressions.ExpressionVisitor.VisitMemberBinding(MemberBinding node)
   at Microsoft.EntityFrameworkCore.Query.Internal.RelationalProjectionBindingExpressionVisitor.VisitMemberInit(MemberInitExpression memberInitExpression)
   at System.Linq.Expressions.MemberInitExpression.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__DisplayClass12_0`1.<ExecuteAsync>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.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 Microsoft.EntityFrameworkCore.EntityFrameworkQueryableExtensions.ToArrayAsync[TSource](IQueryable`1 source, CancellationToken cancellationToken)
   at UserQuery.Main(), line 23

ChristopherHaws on 12 Feb 2020

👍1

Was this page helpful?

0 / 5 - 0 ratings