CODEWITHSUNDEEP
c#sql-serverentity-frameworkef-code-first
ledgeJumper
ledgeJumper

Reputation: 3630

Unsure why my many to many lookup table is throwing a FK reference error in Entity Framework

For the sake of brevity I will trim everything down a bit. I have two models that will have a many to many relationship. The Models are Users and Domains. Domains in this context is a certain area of a large company, like a parent company, its sister company, and maybe some small companies that are all under the same umbrella. This is the User.cs Model

public class User
{
    public User()
    {
        Domains = new HashSet<Domain>();
    }
    [Key]
    [DatabaseGeneratedAttribute(DatabaseGeneratedOption.Identity)]
    public int UserId { get; set; }
    public string UserName { get; set; }
    public string FirstName { get; set; }
    public string LastName { get; set; }
    public ICollection<Domain> Domains { get; set; }
}

And here is the Domain.cs model:

public class Domain
{
    public Domain()
    {
        Users = new HashSet<User>();
    }

    [Key]
    [DatabaseGeneratedAttribute(DatabaseGeneratedOption.Identity)]
    public int DomainId { get; set; }
    public string DomainName { get; set; }
    public ICollection<User> Users { get; set; }
}

Then I do this fancy magic in my Context.cs

protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        modelBuilder.Entity<User>()
            .HasMany(c => c.Domains)
            .WithMany(p => p.Users)
            .Map(
            m =>
            {
                m.MapLeftKey("DomainId");
                m.MapRightKey("UserId");
                m.ToTable("DomainUsers");
            });
        base.OnModelCreating(modelBuilder);
    }

This creates the DomainUsers table just fine. Makes the two column table with DomainID and UserID.

In my seeded data I have one users (UserId = 1), and three domains (IDs= 1,2,3). On the lookup table I can add one record:

UserID | DomainId
1      |  1

But if I try to add the another one:

UserID | DomainId
1      |  1
1      |  2

I get the following error:

The INSERT statement conflicted with the FOREIGN KEY constraint 
"FK_dbo.DomainUsers_dbo.Users_DomainId". Conflict occured in database "MyDatabase"
table "dbo.Users", column "UserId"

Am I missing some fundamental database design concept here, or is there something else to do with entity framework?

Upvotes: 0

Views: 104

Answers (1)

Mark Oreta
Mark Oreta

Reputation: 10416

Your mapping is backwards. From an MSDN Blog post

> modelBuilder.Entity<Post>()
>                 .HasMany(p => p.Tags)
>                 .WithMany(t => t.Posts)
>                 .Map(mc =>
>                    {
>                        mc.ToTable("PostJoinTag");
>                        mc.MapLeftKey("PostId");
>                        mc.MapRightKey("TagId");
>                    });

You’ll need to take care when specifying which is MapLeftKey and which is MapRightKey. Left will be the key of the first class you pointed to, which is Post, and right is for the other side of the relationship. If you get these backwards, your data will be stored incorrectly and your users will be very confused.

Try: 

 modelBuilder.Entity<User>()
            .HasMany(c => c.Domains)
            .WithMany(p => p.Users)
            .Map(
            m =>
            {
                m.MapLeftKey("UserId");
                m.MapRightKey("DomainId");
                m.ToTable("DomainUsers");
            });

Upvotes: 2

Related Questions