efcore: Including reference navigation and projecting collection navigation throws

When using .Include().ThenInclude() and selecting a collection, EF throws an InvalidOperationException with error EF.Property called with wrong property name.

Note that also using .Include() on the selected collection still throws.

The only “workaround” is to avoid .ThenInclude() which is not a solution for me (I need SomeCustomerDataFkNavigation)

Steps to reproduce

using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.Logging;
using Microsoft.Extensions.Logging.Debug;
using System.Collections.Generic;
using System.Diagnostics;
using System.Linq;

namespace _18090Repro
{
    class Program
    {
        static void Main(string[] args)
        {
            using var context = new MyContext();

            context.Database.EnsureDeleted();
            context.Database.EnsureCreated();
            context.SomeCustomerData.Add(new SomeCustomerData { });
            context.SaveChanges();

            context.Customer.Add(new Customer() { SomeCustomerDataFk = 1 });
            context.Customer.Add(new Customer() { });
            context.SaveChanges();

            context.Invoice.Add(new Invoice { CustomerFk = 1 });
            context.SaveChanges();

            context.InvoiceLine.Add(new InvoiceLine { InvoiceFk = 1 });
            context.SaveChanges();

            var invoices = context.Invoice
                .Include(i => i.CustomerFkNavigation)
                    .ThenInclude(c => c.SomeCustomerDataFkNavigation)
                .Select(i => new Invoice()
                {
                    Id = i.Id,
                    CustomerFkNavigation = i.CustomerFkNavigation,
                    InvoiceLine = i.InvoiceLine // This throws
                })
                .ToList();
        }
    }

    public partial class MyContext : DbContext
    {
        public virtual DbSet<Customer> Customer { get; set; }
        public virtual DbSet<Invoice> Invoice { get; set; }
        public virtual DbSet<InvoiceLine> InvoiceLine { get; set; }
        public virtual DbSet<SomeCustomerData> SomeCustomerData { get; set; }

        private static readonly LoggerFactory Logger = new LoggerFactory(new[] { new DebugLoggerProvider() });

        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            string connectionString = "Server=.;Database=Repro18090;Trusted_Connection=True;MultipleActiveResultSets=true";

            optionsBuilder.UseSqlServer(connectionString)
                .EnableSensitiveDataLogging()
                .UseLoggerFactory(Logger);
        }

        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            modelBuilder.Entity<Customer>(entity =>
            {
                entity.HasIndex(e => e.SomeCustomerDataFk);

                entity.Property(e => e.Id).HasColumnName("id");

                entity.Property(e => e.SomeCustomerDataFk).HasColumnName("someCustomerData_fk");

                entity.HasOne(d => d.SomeCustomerDataFkNavigation)
                    .WithMany(p => p.Customer)
                    .HasForeignKey(d => d.SomeCustomerDataFk)
                    .HasConstraintName("FK_Customer_SomeCustomerData");
            });

            modelBuilder.Entity<Invoice>(entity =>
            {
                entity.HasIndex(e => e.CustomerFk);

                entity.Property(e => e.Id).HasColumnName("id");

                entity.Property(e => e.CustomerFk).HasColumnName("customer_fk");

                entity.HasOne(d => d.CustomerFkNavigation)
                    .WithMany(p => p.Invoice)
                    .HasForeignKey(d => d.CustomerFk)
                    .OnDelete(DeleteBehavior.ClientSetNull)
                    .HasConstraintName("FK_Invoice_Customer");
            });

            modelBuilder.Entity<InvoiceLine>(entity =>
            {
                entity.Property(e => e.Id).HasColumnName("id");

                entity.Property(e => e.InvoiceFk).HasColumnName("invoice_fk");

                entity.HasOne(d => d.InvoiceFkNavigation)
                    .WithMany(p => p.InvoiceLine)
                    .HasForeignKey(d => d.InvoiceFk)
                    .OnDelete(DeleteBehavior.ClientSetNull)
                    .HasConstraintName("FK_InvoiceLine_Invoice");
            });

            modelBuilder.Entity<SomeCustomerData>(entity =>
            {
                entity.Property(e => e.Id).HasColumnName("id");
            });
        }
    }

    public partial class Customer
    {
        public Customer()
        {
            Invoice = new HashSet<Invoice>();
        }

        public int Id { get; set; }
        public int? SomeCustomerDataFk { get; set; }

        public virtual SomeCustomerData SomeCustomerDataFkNavigation { get; set; }
        public virtual ICollection<Invoice> Invoice { get; set; }
    }

    public partial class Invoice
    {
        public Invoice()
        {
            InvoiceLine = new HashSet<InvoiceLine>();
        }

        public int Id { get; set; }
        public int CustomerFk { get; set; }

        public virtual Customer CustomerFkNavigation { get; set; }
        public virtual ICollection<InvoiceLine> InvoiceLine { get; set; }
    }

    public partial class InvoiceLine
    {
        public int Id { get; set; }
        public int InvoiceFk { get; set; }

        public virtual Invoice InvoiceFkNavigation { get; set; }
    }

    public partial class SomeCustomerData
    {
        public SomeCustomerData()
        {
            Customer = new HashSet<Customer>();
        }

        public int Id { get; set; }

        public virtual ICollection<Customer> Customer { get; set; }
    }
}

Further technical details

EF Core version: Database provider: Microsoft.EntityFrameworkCore.SqlServer Target framework: .NET Core 3.0 Operating system: Windows 10 x64 IDE: Visual Studio 2019 16.3.1

About this issue

  • Original URL
  • State: closed
  • Created 5 years ago
  • Comments: 16 (4 by maintainers)

Commits related to this issue

Most upvoted comments

I get same error in ubuntu18 and EFCore 3.0.0 there was a workaround for me and i think this should work for you too, first convert your IQueryable to IEnumerable and then call Select method on that:

var invoices = context.Invoice
                .Include(i => i.CustomerFkNavigation)
                    .ThenInclude(c => c.SomeCustomerDataFkNavigation)
                .ToList()
                .Select(i => new Invoice()
                {
                    Id = i.Id,
                    CustomerFkNavigation = i.CustomerFkNavigation,
                    InvoiceLine = i.InvoiceLine
                });

@heyligengregory 3.1 will come start of December 2019

@guftall This only works for small datasets as all you’re doing is storing the entire table in memory, which would result in very poor performance with a lot of data.