Many-to-many relationship left and right keys flipped after Entity Framework 5 upgrade

Question

I have some code that saves a many to many relationship in code. It was working fine with Entity Framework 4.1 but after updating to Entity Framework 5, it's failing.

I'm getting the following error:

The INSERT statement conflicted with the FOREIGN KEY constraint "FK_WebUserFavouriteEvent_Event". The conflict occurred in database "MainEvents", table "dbo.Event", column 'Id'.

I'm using POCO entities with custom mappings. Standard field and many-to-one relationship mappings seem to be working fine.

UPDATE

Ok, so I've got SQL Profiler installed and the plot has thickened...

exec sp_executesql N'insert [dbo].[WebUserFavouriteEvent]([WebUserId], [EventId])
values (@0, @1)
',N'@0 int,@1 int',@0=1820,@1=14

Which means:

WebUserId = @0 = 1820
EventId = @1 = 14

The interesting thing is is that EF5 seems to have flipped the foreign keys around... the WebUserId should be 14 and the EventId should be 1820, not the other way around like it is now.

I reviewed the mapping code and I'm 99% I've set it all up correctly. See Entity Framework Fluent API - Relationships MSDN article for more information.

NOTE: I have also found that this isn't restricted to saving either, SELECTs are also broken.

Here's all the relevant code:

Service Layer

public void AddFavEvent(WebUser webUser, Event @event)
{
    webUser.FavouriteEvents.Add(@event);

    _webUserRepo.Update(webUser);
}

Repository

public void Update<T>(params T[] entities)
    where T : DbTable
{
    foreach (var entity in entities)
    {
        entity.UpdatedOn = DateTime.UtcNow;
    }

    _dbContext.SaveChanges();
}

NOTE: I'm using a 1 DataContext per request approach, so webUser and @event would have been loaded from the same context as the one in the _webUserRepo.

Entities (don't worry about DbTable stuff)

public class Event : DbTable
{
    //BLAH
    public virtual ICollection<WebUser> FavouriteOf { get; set; }
    //BLAH
}

public class WebUser : DbTable
{
    //BLAH
    public virtual ICollection<Event> FavouriteEvents { get; set; }
    //BLAH
}

Mappings

public class EventMapping : DbTableMapping<Event>
{
    public EventMapping()
    {
        ToTable("Event");
        //BLAH
        HasMany(x => x.FavouriteOf)
            .WithMany(x => x.FavouriteEvents)
            .Map(x =>
                     {
                         x.MapLeftKey("EventId");
                         x.MapRightKey("WebUserId");
                         x.ToTable("WebUserFavouriteEvent");
                     });
    }
}

public class WebUserMapping : DbTableMapping<WebUser>
{
    public WebUserMapping ()
    {
        HasMany(x => x.FavouriteEvents)
            .WithMany(x => x.FavouriteOf)
            .Map(m =>
                     {
                         m.MapLeftKey("WebUserId");
                         m.MapRightKey("EventId");
                         m.ToTable("WebUserFavouriteEvent");
                     });
    }
}

Answer 1

Looking at this I suspect that the problem might be caused by the fact that you map the same relationship twice. And you map it in different order.

I made a simple test where I first mapped the relationship once:

    class Program
{
    static void Main(string[] args)
    {
        Database.SetInitializer(new DropCreateDatabaseAlways<Context>());
        var p = new Parent();
        var c = new Child();
        using (var db = new Context())
        {
            db.Parents.Add(new Parent());
            db.Parents.Add(p);

            db.Children.Add(c);
            db.SaveChanges();
        }

        using (var db = new Context())
        {
            var reloadedP = db.Parents.Find(p.ParentId);
            var reloadedC = db.Children.Find(c.ChildId);

            reloadedP.Children = new List<Child>();
            reloadedP.Children.Add(reloadedC);

            db.SaveChanges();
        }

        using (var db = new Context())
        {
            Console.WriteLine(db.Children.Count());
            Console.WriteLine(db.Children.Where(ch => ch.ChildId == c.ChildId).Select(ch => ch.Parents.Count).First());
            Console.WriteLine(db.Parents.Where(pa => pa.ParentId == p.ParentId).Select(pa => pa.Children.Count).First());
        }
    }
}

public class Parent
{
    public int ParentId { get; set; }
    public ICollection<Child> Children { get; set; }

}

public class Child
{
    public int ChildId { get; set; }
    public ICollection<Parent> Parents { get; set; }
}

public class Context : DbContext
{
    public Context() : base("data source=Mikael-PC;Integrated Security=SSPI;Initial Catalog=EFTest")
    {

    }

    public IDbSet<Child> Children { get; set; }
    public IDbSet<Parent> Parents { get; set; }

    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        base.OnModelCreating(modelBuilder);
        modelBuilder.Entity<Child>()
            .HasMany(x => x.Parents)
            .WithMany(x => x.Children)
            .Map(c =>
            {
                c.MapLeftKey("ChildId");
                c.MapRightKey("ParentId");
                c.ToTable("ChildToParentMapping"); 
            });

    }
}

And then I changed the OnModelCreating to be:

        protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        base.OnModelCreating(modelBuilder);
        modelBuilder.Entity<Child>()
            .HasMany(x => x.Parents)
            .WithMany(x => x.Children)
            .Map(c =>
            {
                c.MapLeftKey("ChildId");
                c.MapRightKey("ParentId");
                c.ToTable("ChildToParentMapping"); 
            });

        modelBuilder.Entity<Parent>()
           .HasMany(x => x.Children)
           .WithMany(x => x.Parents)
           .Map(c =>
           {
               c.MapLeftKey("ParentId");
               c.MapRightKey("ChildId");
               c.ToTable("ChildToParentMapping");
           });
    }

What I found and suspected is that the first run generates this sql:

exec sp_executesql N'insert [dbo].[ChildToParentMapping]([ChildId], [ParentId])
values (@0, @1)
',N'@0 int,@1 int',@0=1,@1=2

In constrast to the second which generates:

exec sp_executesql N'insert [dbo].[ChildToParentMapping]([ParentId], [ChildId])
values (@0, @1)
',N'@0 int,@1 int',@0=1,@1=2

You see the values flipped? Here it actually count the ChildId column as ParentId. Now this doesn't crash for me but I let EF create the database which means it probably just switch the column names and if I would look at the foreign keys they would be switched too. If you created the database manually that probably won't be the case.

So in short: You mappings aren't equal and I expect one of them to be used and that one is probably wrong. In earlier verions I guess EF picked them up in different order.

UPDATE: I got a bit curious about the foreign keys and checked the sql.

From the first code:

ALTER TABLE [dbo].[ChildToParentMapping] ADD CONSTRAINT [FK_dbo.ChildToParentMapping_dbo.Children_ChildId] FOREIGN KEY ([ChildId]) REFERENCES [dbo].[Children] ([ChildId]) ON DELETE CASCADE

And from the second code:

ALTER TABLE [dbo].[ChildToParentMapping] ADD CONSTRAINT [FK_dbo.ChildToParentMapping_dbo.Children_ParentId] FOREIGN KEY ([ParentId]) REFERENCES [dbo].[Children] ([ChildId]) ON DELETE CASCADE

Now that is not nice. ParentId mapped against Children is certainly not what we want.

So is the second mapping wrong? Not really because see what happend when I removed the first one:

ALTER TABLE [dbo].[ChildToParentMapping] ADD CONSTRAINT [FK_dbo.ChildToParentMapping_dbo.Parents_ParentId] FOREIGN KEY ([ParentId]) REFERENCES [dbo].[Parents] ([ParentId]) ON DELETE CASCADE

Somehow having two mappings seems to mess things up. Bug or not I don't know.

Answer 2

I can confirm this is a bug in EF5, although I'm still not sure how it worked in 4.3.1.

The problem is that we are not correctly associating the LeftKey/RightKey calls with their corresponding navigation properties.

I will file an EF6 bug over on our CodePlex project site.

To workaround, I think you will need to either:

1. Configure the association from one side only. Or,
2. Switch the LeftKey/RightKey columns names to be the same in both configurations.

Sorry for the inconvenience.

UPDATE: Here is the bug.