none
EF Code First, Trying to Prevent Replication of Foreign Keys in Subclass Tables RRS feed

  • Question

  • I'm using EF Code first to interact with an existing database that utilizes TPT inheritance. Consider the following classes, MemberPost, and Issue, where a Post is associated with a Member (foreign key), and Issue extends Post. Following is a sample of the model, using data annotations:

    [Table("tblMembers_Member")]
    public partial class Member
    {
        public Member()
        {
            this.PostList = new HashSet<Post>();
        }
    
        [Key]
        public Guid MemberID { get; set; }
        public virtual ICollection<Post> PostList { get; set; }
    }
    
    [Table("tblForums_Post")]
    public partial class Post
    {
        public Post()
        {
        }
    
        [Key]
        public Guid PostID { get; set; }
    
        [Required]
        public Guid MemberID { get; set; }
    
        [ForeignKey("MemberID")]
        public virtual Member Member { get; set; }
    }
    
    [Table("tblForums_Issue")]
    public partial class Issue : Post
    {
        public Issue()
        {
        }
    }

    I was having a number of mapping issues, so I decided to generate a separate database, based on this same structure and take a look at that. What I observed is that the generated database contains an additional foreign key from Issue to Member, named Member_MemberID.

    I also tried the mapping utilizing EntityTypeConfiguration<T>, with the same results. This is an example mapping for Post:

    public partial class PostMap : EntityTypeConfiguration<Post>
    {
        public  PostMap()
        {
            // Primary Key
            this.HasKey(t => t.PostID);
    
            // Table & Column Mappings
            this.ToTable("tblForums_Post");
            this.Property(t => t.PostID).HasColumnName("PostID");
            this.Property(t => t.MemberID).HasColumnName("MemberID");
    
            // Relationships
            this.HasRequired(t => t.Member)
                .WithMany(t => t.PostList)
                .HasForeignKey(t => t.MemberID)
                .WillCascadeOnDelete(false);
        }
    }

    How can I adjust the mapping to allow the base class/table to own the foreign key relationship, and allow sub classes to utilize that relationship?

    Wednesday, December 11, 2013 4:29 PM

Answers

  • Hello,

    >>I was having a number of mapping issues, so I decided to generate a separate database

    Do you mean that you have two database, one for post and member and anther for issue and member and then you will have two dbcontext objects?

    >>What I observed is that the generated database contains an additional foreign key from Issue to Member, named Member_MemberID.

    This is strange that because I used the entity classes schema provided by you and then created the table just in one database, it worked fine and had not additional foreign key from Issue to Member.

    DbContext class is very simple:

    public class ForeignKeyContext:DbContext 
    
        {
    
            public DbSet<Member> Members { get; set; }
    
            public DbSet<Post> Posts { get; set; }
    
            public DbSet<Issue> Issues { get; set; }
    
            protected override void OnModelCreating(DbModelBuilder modelBuilder)
    
            {
    
     
    
            }
    
        }

    The created the databse schema is like below:

    CREATE TABLE [dbo].[tblMembers_Member] (
    
        [MemberID] UNIQUEIDENTIFIER NOT NULL,
    
        CONSTRAINT [PK_dbo.tblMembers_Member] PRIMARY KEY CLUSTERED ([MemberID] ASC)
    
    );
    
    
    CREATE TABLE [dbo].[tblForums_Post] (
    
        [PostID]   UNIQUEIDENTIFIER NOT NULL,
    
        [MemberID] UNIQUEIDENTIFIER NOT NULL,
    
        CONSTRAINT [PK_dbo.tblForums_Post] PRIMARY KEY CLUSTERED ([PostID] ASC),
    
        CONSTRAINT [FK_dbo.tblForums_Post_dbo.tblMembers_Member_MemberID] FOREIGN KEY ([MemberID]) REFERENCES [dbo].[tblMembers_Member] ([MemberID]) ON DELETE CASCADE
    
    );
    
    CREATE TABLE [dbo].[tblForums_Issue] (
    
        [PostID] UNIQUEIDENTIFIER NOT NULL,
    
        CONSTRAINT [PK_dbo.tblForums_Issue] PRIMARY KEY CLUSTERED ([PostID] ASC),
    
        CONSTRAINT [FK_dbo.tblForums_Issue_dbo.tblForums_Post_PostID] FOREIGN KEY ([PostID]) REFERENCES [dbo].[tblForums_Post] ([PostID])
    
    );

    If I misunderstand, please let me know.

    I use EF5, .Net Framework 4.5 and VS2012.

    Regards.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    • Edited by Fred BaoModerator Thursday, December 12, 2013 1:56 AM
    • Marked as answer by DaveCl Thursday, December 12, 2013 5:18 AM
    Thursday, December 12, 2013 1:55 AM
    Moderator

All replies

  • Hello,

    >>I was having a number of mapping issues, so I decided to generate a separate database

    Do you mean that you have two database, one for post and member and anther for issue and member and then you will have two dbcontext objects?

    >>What I observed is that the generated database contains an additional foreign key from Issue to Member, named Member_MemberID.

    This is strange that because I used the entity classes schema provided by you and then created the table just in one database, it worked fine and had not additional foreign key from Issue to Member.

    DbContext class is very simple:

    public class ForeignKeyContext:DbContext 
    
        {
    
            public DbSet<Member> Members { get; set; }
    
            public DbSet<Post> Posts { get; set; }
    
            public DbSet<Issue> Issues { get; set; }
    
            protected override void OnModelCreating(DbModelBuilder modelBuilder)
    
            {
    
     
    
            }
    
        }

    The created the databse schema is like below:

    CREATE TABLE [dbo].[tblMembers_Member] (
    
        [MemberID] UNIQUEIDENTIFIER NOT NULL,
    
        CONSTRAINT [PK_dbo.tblMembers_Member] PRIMARY KEY CLUSTERED ([MemberID] ASC)
    
    );
    
    
    CREATE TABLE [dbo].[tblForums_Post] (
    
        [PostID]   UNIQUEIDENTIFIER NOT NULL,
    
        [MemberID] UNIQUEIDENTIFIER NOT NULL,
    
        CONSTRAINT [PK_dbo.tblForums_Post] PRIMARY KEY CLUSTERED ([PostID] ASC),
    
        CONSTRAINT [FK_dbo.tblForums_Post_dbo.tblMembers_Member_MemberID] FOREIGN KEY ([MemberID]) REFERENCES [dbo].[tblMembers_Member] ([MemberID]) ON DELETE CASCADE
    
    );
    
    CREATE TABLE [dbo].[tblForums_Issue] (
    
        [PostID] UNIQUEIDENTIFIER NOT NULL,
    
        CONSTRAINT [PK_dbo.tblForums_Issue] PRIMARY KEY CLUSTERED ([PostID] ASC),
    
        CONSTRAINT [FK_dbo.tblForums_Issue_dbo.tblForums_Post_PostID] FOREIGN KEY ([PostID]) REFERENCES [dbo].[tblForums_Post] ([PostID])
    
    );

    If I misunderstand, please let me know.

    I use EF5, .Net Framework 4.5 and VS2012.

    Regards.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    • Edited by Fred BaoModerator Thursday, December 12, 2013 1:56 AM
    • Marked as answer by DaveCl Thursday, December 12, 2013 5:18 AM
    Thursday, December 12, 2013 1:55 AM
    Moderator
  • Thanks Fred.  That's interesting that you're not seeing the extra foreign key, your generated schema looks good.  This is all in one database (I generated a second database for comparison only).  I'm using EF6.

    I created another project with this small model info, and you're right, this maps just fine.  I had tried a minimal model with just a few tables, now I'll add more more model info until the mapping issue arises.

    Edit: I found that I some extraneous collections in my Member class, such as:

    public virtual ICollection<Issue> IssueList { get; set; }


    Thanks for your help!



    • Edited by DaveCl Thursday, December 12, 2013 5:18 AM
    Thursday, December 12, 2013 3:30 AM