Concerning the relationship configuration of Entity Framework, the error message "invalid column name XXXX_Id" is displayed. entityxxxx_id

Concerning the relationship configuration of Entity Framework, the error message "invalid column name XXXX_Id" is displayed. entityxxxx_id

Problem description:

There are two tables in the database:

The Member table has a foreign key RoleId, which corresponds to the primary key Id of the Role table, the business logic is that the RoleId of the Member table must correspond to the Id of the Role table (but no foreign key is set when designing the data table, which is configured using EF (Entity Framework, the same below)

The code for homepage Member is as follows:

    public partial class Member    {        public int Id { get; set; }        public string Name { get; set; }        public string Password { get; set; }        public bool Delete{ get; set; }        public int RoleId { get; set; }        public virtual Role Role { get; set; }    }

View Code

The code for the Role table is as follows:

    public partial class Role    {        public int Id { get; set; }        public string Name { get; set; }        public virtual IList<Member> Members { get; set; }    }

View Code

The relationship code of Member and Role is as follows:

public class MemberMap : EntityTypeConfiguration<Member>    {        public MemberMap()        {            this.ToTable("Member");            this.HasKey(m => m.Id);            this.HasRequired(m => m.Role).WithMany().HasForeignKey(m => m.RoleId);        }    }

View Code

    public class RoleMap: EntityTypeConfiguration<Role>    {        public RoleMap()        {            this.ToTable("Role");            this.HasKey(r => r.Id);        }    }

View Code

The EF operation class is as follows:

public partial class EFContext<T> : DbContext where T : class    {        public EFContext(): base("name=MyConnectionString")        {        }        protected override void OnModelCreating(DbModelBuilder modelBuilder)        {            Database.SetInitializer<EFContext<T>> (null);            modelBuilder.Configurations.Add(new MemberMap());            modelBuilder.Configurations.Add(new RoleMap());            base.OnModelCreating(modelBuilder);        }        public DbSet<T> Table { get; set; }        public IQueryable<T> GetList(Expression<Func<T,bool>> where)        {            return this.Table.Where(where);        }    }

View Code

An exception is prompted when the program is running,

 

 

The details are as follows:

Use SQL Profiler to find the generated code as follows:

 

The actual RoleId is a foreign key.

The solution is to configure the witing with WithMany (), that is, modify the MemberMap as follows:

public class MemberMap : EntityTypeConfiguration<Member>    {        public MemberMap()        {            this.ToTable("Member");            this.HasKey(m => m.Id);            this.HasRequired(m => m.Role).WithMany(r => r.Members).HasForeignKey(m => m.RoleId);        }    }

Then run the console program. There is no exception. The SQL Profiler is as follows:

 

Someone may ask, what if we do use withbers () instead of withbers (r => r. Members) in MemberMap?

The modification method is as follows:

Comment out or delete the Members attribute in the Role entity code

public partial class Role    {        public int Id { get; set; }        public string Name { get; set; }        //public virtual IList<Member> Members { get; set; }    }

View Code

Modify MemberMap as described above:

public class MemberMap : EntityTypeConfiguration<Member>    {        public MemberMap()        {            this.ToTable("Member");            this.HasKey(m => m.Id);            this.HasRequired(m => m.Role).WithMany().HasForeignKey(m => m.RoleId);        }    }

View Code

Finally, re-run the program without exception.