Problem with many-to-many with composite key on one side needing to rename SQL columns

[bgoodearl]

Context

I have a large client solution that started life as ASP.NET MVC with .NET Framework 4.5 and EF 6.1 back in 2014. It has a large number of many-to-many relationships, and a fair percentage of those have a composite key on one side. The production database has more than 100 tables in it.

We've migrated that code to .NET Core 3.1 with EF 6.4, and are now in the process of figuring out how to rebuild the app in .NET 6 with EF Core 6 so that it can operate on the same database as the older code, and we're running into problems with the many-to-many relationships.

In the early part of this year, I created two solutions based on the models in the Contoso University code to look at multiple issues and possibilities, including migrating from a layered architecture toward a Clean architecture. The results of that work can be found in these two repositories: ContosoU_dnc31_MVCB_Clean (written with .NET Core 3.1 and EF 6.x) and ContosoU_dn6_MVCB_Clean (written with .NET 6 and EF Core 6).

The Problem - specifying Many-to-Many relationships to match the existing object model and schema

I added code to the "dnc31" version that has a couple of "lookup" models.

Here are some of the relevant classes from the "with_lookups" branch of ContosoU_dnc31_MVCB_Clean

    public abstract class LookupBaseWith2cKey
    {
        [MaxLength(2)]
        [Required]
        public string Code { get; set; }

        [Required]
        public short LookupTypeId { get; protected set; }

        [MaxLength(100)]
        [Required]
        public string Name { get; set; }
    }

    public class CoursePresentationType : LookupBaseWith2cKey
    {
        public CoursePresentationType()
        {
            LookupTypeId = (short)CULookupTypes.CoursePresentationType;
        }

        private ICollection<Course> _courses;
        public virtual ICollection<Course> Courses
        {
            get { return _courses ?? (_courses = new List<Course>()); }
            protected set { _courses = value; }
        }
    }

    public class DepartmentFacilityType : LookupBaseWith2cKey
    {
        public DepartmentFacilityType()
        {
            LookupTypeId = (short)CULookupTypes.DepartmentFacilityType;
        }

        private ICollection<Department> _departments;
        public virtual ICollection<Department> Departments
        {
            get { return _departments ?? (_departments = new List<Department>()); }
            protected set { _departments = value; }
        }
    }

    public class Course : EntityBaseT<int>
    {
        private Course()
        {
        }

        public Course(int courseID, string title, Department department)
        {
            Guard.Against.OutOfRange(courseID, nameof(courseID), 1, int.MaxValue);
            Guard.Against.NullOrWhiteSpace(title, nameof(title));
            Guard.Against.Null(department, nameof(department));
            Guard.Against.Zero(department.DepartmentID, nameof(department.DepartmentID));
            CourseID = courseID;
            Title = title;
            Department = department;
            DepartmentID = department.DepartmentID;
        }

        [DatabaseGenerated(DatabaseGeneratedOption.None)]
        [Display(Name = "Number")]
        public int CourseID { get; set; }

        [NotMapped]
        public override int Id { get { return CourseID; } }

        [StringLength(50, MinimumLength = 3)]
        public string Title { get; set; }

        [Range(0, 5)]
        public int Credits { get; set; }

        public int DepartmentID { get; set; }

        public virtual Department Department { get; set; }
        public virtual ICollection<Enrollment> Enrollments { get; set; } = new List<Enrollment>();
        public virtual ICollection<Instructor> Instructors { get; set; } = new List<Instructor>();

        private ICollection<CoursePresentationType> _coursePresentationTypes;
        public virtual ICollection<CoursePresentationType> CoursePresentationTypes
        {
            get { return _coursePresentationTypes ?? (_coursePresentationTypes = new List<CoursePresentationType>()); }
            protected set { _coursePresentationTypes = value; }
        }

    }

    public class Department : EntityBaseT<int>
    {
        private Department()
        {
            RowVersion = new byte[] { 0, 0, 0, 0 };
        }

        public Department(string name, decimal budget, DateTime startDate)
        {
            Guard.Against.NullOrWhiteSpace(name, nameof(name));
            Guard.Against.OutOfSQLDateRange(startDate, nameof(startDate));
            Name = name;
            Budget = budget;
            RowVersion = new byte[] { 0, 0, 0, 0 };
            StartDate = startDate;
        }

        public int DepartmentID { get; set; }

        [NotMapped]
        public override int Id { get { return DepartmentID; } }

        [StringLength(50, MinimumLength = 3)]
        public string Name { get; set; }

        [DataType(DataType.Currency)]
        [Column(TypeName = "money")]
        public decimal Budget { get; set; }

        [DataType(DataType.Date)]
        [DisplayFormat(DataFormatString = "{0:yyyy-MM-dd}", ApplyFormatInEditMode = true)]
        [Display(Name = "Start Date")]
        public DateTime StartDate { get; set; }

        public int? InstructorID { get; set; }

        [Timestamp]
        public byte[] RowVersion { get; set; }

        public virtual Instructor Administrator { get; set; }
        public virtual ICollection<Course> Courses { get; set; }

        private ICollection<DepartmentFacilityType> _departmentFacilityTypes;
        public virtual ICollection<DepartmentFacilityType> DepartmentFacilityTypes
        {
            get { return _departmentFacilityTypes ?? (_departmentFacilityTypes = new List<DepartmentFacilityType>()); }
            protected set { _departmentFacilityTypes = value; }
        }

    }

and relevant code from OnModelCreating:

    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
            modelBuilder.Conventions.Remove<PluralizingTableNameConvention>();

            modelBuilder.Entity<Course>()
                 .HasMany(c => c.Instructors).WithMany(i => i.Courses)
                 .Map(t => t.MapLeftKey("CourseID")
                     .MapRightKey("InstructorID")
                     .ToTable("CourseInstructor"));

            modelBuilder.Entity<Course>()
                .HasMany(c => c.CoursePresentationTypes).WithMany(cpt => cpt.Courses)
                .Map(m =>
                {
                    m.MapLeftKey("CourseID");
                    m.MapRightKey("LookupTypeId", "CoursePresentationTypeCode");
                    m.ToTable("_coursesPresentationTypes");
                });

            modelBuilder.Entity<Department>()
                .HasMany(d => d.DepartmentFacilityTypes).WithMany(df => df.Departments)
                .Map(m =>
                {
                    m.MapLeftKey("DepartmentID");
                    m.MapRightKey("LookupTypeId", "DepartmentFacilityTypeCode");
                    m.ToTable("_departmentsFacilityTypes");
                });

        //... code left out

    }

I then spent a considerable amount of time trying to figure out how to do the same thing with EF Core 6, and was unable to find anything in the documentation (such as it is) or online samples, blog posts, etc. to help me out. I reached out to a well-known expert in the field, and with much online searching, guesses, and trial-and-error, and more than 2 hours on zoom, we got as far as the following (which can be found in the "with_lookups" branch of ContosoU_dn6_MVCB_Clean):

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
            #region School Entities

            modelBuilder.Entity<Course>(e =>
            {
                e.HasKey(c => c.CourseID);
                e.Property(c => c.CourseID).ValueGeneratedNever();
                e.ToTable("Course");
                e.HasOne(e => e.Department).WithMany()
                    .HasForeignKey(e => e.DepartmentID).IsRequired(true).OnDelete(DeleteBehavior.Restrict);

                e.HasMany(c => c.Instructors).WithMany(i => i.Courses)
                    .UsingEntity<Dictionary<string, object>>(
                        "CourseInstructor",
                        l => l.HasOne<Instructor>().WithMany().HasForeignKey("InstructorID")
                        .OnDelete(DeleteBehavior.Restrict).HasConstraintName("FK_CourseInstructor_Instructor"),
                        r => r.HasOne<Course>().WithMany().HasForeignKey("CourseID")
                        .OnDelete(DeleteBehavior.Restrict).HasConstraintName("FK_CourseInstructor_Course"),
                        j =>
                        {
                            j.HasKey("CourseID", "InstructorID");
                            j.ToTable("CourseInstructor");
                        });

                e.HasMany(e => e.CoursePresentationTypes).WithMany(p => p.Courses)
                    .UsingEntity(
                        join =>
                        {
                            join.ToTable("_coursesPresentationTypes")
                                .Property<int>("CoursesCourseId").HasColumnName("CourseID");
                            //join.ToTable("_coursesPresentationTypes")
                            //    .Property<int>("CoursePresentationTypesLookupTypeId").HasColumnName("LookupTypeId");
                            //join.ToTable("_coursesPresentationTypes")
                            //    .Property<int>("CoursePresentationTypesCode").HasColumnName("CoursePresentationTypeCode");
                        }
                    );
            });

            modelBuilder.Entity<Department>(e =>
            {
                e.HasKey(c => c.DepartmentID);
                e.ToTable("Department");
                e.Property(g => g.RowVersion).IsRequired().IsRowVersion().IsConcurrencyToken();
                e.Property(d => d.StartDate).HasColumnType("datetime");
                e.HasOne(d => d.Administrator).WithMany()
                    .HasForeignKey(d => d.InstructorID).IsRequired(false).OnDelete(DeleteBehavior.Restrict);

                e.HasMany(d => d.DepartmentFacilityTypes).WithMany(dft => dft.Departments)
                    .UsingEntity(
                        join =>
                        {
                            join.ToTable("_departmentsFacilityTypes")
                                .Property<int>("DepartmentsDepartmentID").HasColumnName("DepartmentID");
                        }
                    );
            });

    //... Code left out

            #endregion School Entities

            //*******************************************
            #region LookupBaseWith2cKey Subclass Mappings

            modelBuilder.Entity<LookupType>(e =>
            {
                e.HasKey(x => x.Id);
                e.ToTable("xLookupTypes");
            });

            modelBuilder.Entity<LookupBaseWith2cKey>(e =>
            {
                e.HasKey(l => new { l.LookupTypeId, l.Code });
                e.ToTable("xLookups2cKey");

                e.Property(x => x.Code).HasMaxLength(2).IsRequired();
                e.Property(x => x.Name).HasMaxLength(100).IsRequired();

                e.HasIndex(l => new { l.LookupTypeId, l.Name }).IsUnique(true)
                    .HasDatabaseName("LookupTypeAndName");

                e.Property(l => l.SubType).HasColumnName("_SubType");

                e.HasDiscriminator<short>(x => x.SubType)
                    .HasValue<CoursePresentationType>((short)CULookupTypes.CoursePresentationType)
                    .HasValue<DepartmentFacilityType>((short)CULookupTypes.DepartmentFacilityType)
                ;

            });

            #endregion LookupBaseWith2cKey Subclass Mappings

    }

With the collections commented out in Department and DepartmentFacilityType, we were able to get a many-to-many defined for Course and CoursePresentationType, and figure out how to rename the simple key for CourseID. This afternoon, I was able to run the migration and then tweak the generated code in the snapshot and migration to get it to generated the schema I wanted, but when I uncommented the collections in Department and DepartmentFacilityType, the next migration I ran blew up.

PM> Add-Migration -Project CU.Infrastructure -StartupProject CU.EFDataApp CU6_M04b_Department_DFT
Build started...
Build succeeded.
System.InvalidOperationException: The property 'LookupTypeId' cannot be added to the type 'CourseCoursePresentationType (Dictionary<string, object>)' because no property type was specified and there is no corresponding CLR property or field. To add a shadow state property, the property type must be specified.
   at Microsoft.EntityFrameworkCore.Metadata.Internal.InternalEntityTypeBuilder.Property(Type propertyType, String propertyName, MemberInfo memberInfo, Nullable`1 typeConfigurationSource, Nullable`1 configurationSource)
   at Microsoft.EntityFrameworkCore.Metadata.Internal.InternalEntityTypeBuilder.Property(Type propertyType, String propertyName, Nullable`1 typeConfigurationSource, Nullable`1 configurationSource)
   at Microsoft.EntityFrameworkCore.Metadata.Internal.InternalEntityTypeBuilder.GetOrCreateProperties(IReadOnlyList`1 propertyNames, Nullable`1 configurationSource, IReadOnlyList`1 referencedProperties, Boolean required, Boolean useDefaultType)
   at Microsoft.EntityFrameworkCore.Metadata.Internal.InternalEntityTypeBuilder.PrimaryKey(IReadOnlyList`1 propertyNames, ConfigurationSource configurationSource)
   at Microsoft.EntityFrameworkCore.Metadata.Builders.EntityTypeBuilder.HasKey(String[] propertyNames)
   at CU.Infrastructure.Migrations.SchoolDbContextModelSnapshot.<>c.<BuildModel>b__0_8(EntityTypeBuilder b) in D:\_dev\GitHub\bgoodearl\Blazor\ContosoU_dn6_MVCB_Clean\src\CU.Infrastructure\Migrations\SchoolDbContextModelSnapshot.cs:line 242
   at Microsoft.EntityFrameworkCore.ModelBuilder.Entity(String name, Action`1 buildAction)
   at CU.Infrastructure.Migrations.SchoolDbContextModelSnapshot.BuildModel(ModelBuilder modelBuilder) in D:\_dev\GitHub\bgoodearl\Blazor\ContosoU_dn6_MVCB_Clean\src\CU.Infrastructure\Migrations\SchoolDbContextModelSnapshot.cs:line 228
   at Microsoft.EntityFrameworkCore.Infrastructure.ModelSnapshot.CreateModel()
   at Microsoft.EntityFrameworkCore.Infrastructure.ModelSnapshot.get_Model()
   at Microsoft.EntityFrameworkCore.Migrations.Design.MigrationsScaffolder.ScaffoldMigration(String migrationName, String rootNamespace, String subNamespace, String language)
   at Microsoft.EntityFrameworkCore.Design.Internal.MigrationsOperations.AddMigration(String name, String outputDir, String contextType, String namespace)
   at Microsoft.EntityFrameworkCore.Design.OperationExecutor.AddMigrationImpl(String name, String outputDir, String contextType, String namespace)
   at Microsoft.EntityFrameworkCore.Design.OperationExecutor.AddMigration.<>c__DisplayClass0_0.<.ctor>b__0()
   at Microsoft.EntityFrameworkCore.Design.OperationExecutor.OperationBase.<>c__DisplayClass3_0`1.<Execute>b__0()
   at Microsoft.EntityFrameworkCore.Design.OperationExecutor.OperationBase.Execute(Action action)
The property 'LookupTypeId' cannot be added to the type 'CourseCoursePresentationType (Dictionary<string, object>)' because no property type was specified and there is no corresponding CLR property or field. To add a shadow state property, the property type must be specified.
PM> 

Is there a way to specify the database column names for the composite key in the join table?

Include provider and version information

EF Core version: 6.0.2 Database provider: Microsoft.EntityFrameworkCore.SqlServer Target framework: .NET 6.0 Operating system: Windows 10 IDE: Visual Studio 2022 17.0.10

[AndriySvyryd]

I didn't get any exceptions after uncommenting the collections in Department and DepartmentFacilityType. Perhaps this is related to the manual changes you made. To configure the FK properties on the join type you could do something like this:

e.HasMany(e => e.CoursePresentationTypes).WithMany(p => p.Courses)
    .UsingEntity(
        join => join
            .HasOne(typeof(CoursePresentationType))
            .WithMany()
            .HasForeignKey("CoursePresentationTypesLookupTypeId", "CoursePresentationTypesCode"),
        join => join
            .HasOne(typeof(Course))
            .WithMany()
            .HasForeignKey("CoursesCourseId"),
        join =>
        {
            join.ToTable("_coursesPresentationTypes")
                .Property<int>("CoursesCourseId").HasColumnName("CourseID");
            join.Property<short>("CoursePresentationTypesLookupTypeId").HasColumnName("LookupTypeId");
            join.Property<string>("CoursePresentationTypesCode").HasColumnName("CoursePresentationTypeCode");
        }
    );

Using an explicit type for the join entity type would make writing this code easier.