How to map linking table for many-to-many relationship using Entity Framework 6

TL;DR: use synonym.

As promised earlier to myself, I blog about interesting challenges and the ways I solved them.

The other day, me and my team faced a limitation in EF6: you can either configure a linking table for many-to-many relationship between two entities or map this table to an entity. But not both simultaneously.

For instance, for the relationship between Orders and Addresses, when one order can be linked to multiple addresses (shipping, billing, legal, etc.) and one address can be used for multiple orders.

modelBuilder.Entity<Order>
            .HasMany(x => x.Addresses)
            .WithMany(x => x.Orders)
            .Map(c => c.ToTable("OrderAddresses")
                       .MapLeftKey("OrderId")
                       .MapRightKey("AddressId"));

And in the same time you’d like to have the intermediate, linking entity to be mapped too. To query it independently or for bulk operations such as insert.

modelBuilder.Entity<OrderAddress>
            .ToTable("OrderAddresses");

This case you’ll get an exception during context configuration validation:

System.InvalidOperationException: The navigation property ‘Addresses’ declared on type ‘Order’ has been configured with conflicting mapping information.

The solution would be to create a synonym (which in my project we keep in a separate schema called syn):

create schema syn
create synonym syn.OrderAddresses for dbo.OrderAddresses

and map the intermediate entity to it:

modelBuilder.Entity<OrderAddress>
            .ToTable("OrderAddresses", "syn");

Note the overload accepting the schema name.

Now you can bulk insert into this table directly:

using (var scope = new TransactionScope())
{
    dbContext.BulkInsert(orders);
    dbContext.BulkInsert(addresses);
    dbContext.BulkInsert(orderAddresses);

    scope.Complete();
}

We’re using EntityFramework.BulkInsert but also you can try EntityFramework.Utilities that supports both bulk insert and delete. However it also has a bug and doesn’t expose an overload accepting SqlBulkCopyOptions.

Happy mapping!

This entry was posted in Programming and tagged . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s