How to seed data with many-to-may relations in Entity Framework Migrations
Updated Answer
Make sure you read "Using AddOrUpdate Properly" section below for a complete answer.
First of all, let's create a composite primary key (consisting of parcel id and item id) to eliminate duplicates. Add the following method in the DbContext
class:
protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
base.OnModelCreating(modelBuilder);
modelBuilder.Entity<Parcel>()
.HasMany(p => p.Items)
.WithMany(r => r.Parcels)
.Map(m =>
{
m.ToTable("ParcelItems");
m.MapLeftKey("ParcelId");
m.MapRightKey("BuyingItemId");
});
}
Then implement the Seed
method like so:
protected override void Seed(Context context)
{
context.Parcels.AddOrUpdate(p => p.Id,
new Parcel { Id = 1, Description = "Parcel 1", Weight = 1.0 },
new Parcel { Id = 2, Description = "Parcel 2", Weight = 2.0 },
new Parcel { Id = 3, Description = "Parcel 3", Weight = 3.0 });
context.BuyingItems.AddOrUpdate(b => b.Id,
new BuyingItem { Id = 1, Price = 10m },
new BuyingItem { Id = 2, Price = 20m });
// Make sure that the above entities are created in the database
context.SaveChanges();
var p1 = context.Parcels.Find(1);
// Uncomment the following line if you are not using lazy loading.
//context.Entry(p1).Collection(p => p.Items).Load();
var p2 = context.Parcels.Find(2);
// Uncomment the following line if you are not using lazy loading.
//context.Entry(p2).Collection(p => p.Items).Load();
var i1 = context.BuyingItems.Find(1);
var i2 = context.BuyingItems.Find(2);
p1.Items.Add(i1);
p1.Items.Add(i2);
// Uncomment to test whether this fails or not, it will work, and guess what, no duplicates!!!
//p1.Items.Add(i1);
//p1.Items.Add(i1);
//p1.Items.Add(i1);
//p1.Items.Add(i1);
//p1.Items.Add(i1);
p2.Items.Add(i1);
p2.Items.Add(i2);
// The following WON'T work, since we're assigning a new collection, it'll try to insert duplicate values only to fail.
//p1.Items = new[] { i1, i2 };
//p2.Items = new[] { i2 };
}
Here we make sure that the entities are created/updated in the database by calling context.SaveChanges()
within the Seed
method. After that, we retrieve the required parcel and buying item objects using context
. Thereafter we use the Items
property (which is a collection) on the Parcel
objects to add BuyingItem
as we please.
Please note, no matter how many times we call the Add
method using the same item object, we don't end up with primary key violation. That is because EF internally uses HashSet<T>
to manage the Parcel.Items
collection. A HashSet<Item>
, by its nature, won't let you add duplicate items.
Moreover, if you somehow manage to circumvent this EF behavior as I have demonstrated in the example, our primary key won't let the duplicates in.
Using AddOrUpdate
Properly
When you use a typical Id field (int, identity) as an identifier expression with AddOrUpdate
method, you should exercise caution.
In this instance, if you manually delete one of the rows from the Parcel table, you'll end up creating duplicates every time you run the Seed method (even with the updated Seed
method I have provided above).
Consider the following code,
context.Parcels.AddOrUpdate(p => p.Id,
new Parcel { Id = 1, Description = "Parcel 1", Weight = 1.0 },
new Parcel { Id = 2, Description = "Parcel 1", Weight = 1.0 },
new Parcel { Id = 3, Description = "Parcel 1", Weight = 1.0 }
);
Technically (considering the surrogate Id here), the rows are unique, but from the end-user point of view, they are duplicates.
The true solution here is to use the Description
field as an identifier expression. Add this attribute to the Description
property of the Parcel
class to make it unique: [MaxLength(255), Index(IsUnique=true)]
. Update the following snippets in the Seed
method:
context.Parcels.AddOrUpdate(p => p.Description,
new Parcel { Description = "Parcel 1", Weight = 1.0 },
new Parcel { Description = "Parcel 2", Weight = 2.0 },
new Parcel { Description = "Parcel 3", Weight = 3.0 });
// Make sure that the above entities are created in the database
context.SaveChanges();
var p1 = context.Parcels.Single(p => p.Description == "Parcel 1");
Note, I'm not using the Id
field as EF is going to ignore it while inserting rows. And we are using Description
to retrieve the correct parcel object, no matter what Id
value is.
Old Answer
I would like to add a few observations here:
Using Id is probably not going to do any good if the Id column is a database generated field. EF is going to ignore it.
This method seems to be working fine when the
Seed
method is run once. It won't create any duplicates, however, if you run it for a second time (and most of us have to do that often), it may inject duplicates. In my case it did.
This tutorial by Tom Dykstra showed me the right way of doing it. It works because we don't take anything for granted. We don't specify IDs. Instead, we query the context by known unique keys and add related entities (which again are acquired by querying context) to them. It worked like a charm in my case.
You must fill many-to-many relation in the same way as you build many-to-many relation in any EF code:
protected override void Seed(Context context)
{
base.Seed(context);
// This will create Parcel, BuyingItems and relations only once
context.AddOrUpdate(new Parcel()
{
Id = 1,
Description = "Test",
Items = new List<BuyingItem>
{
new BuyingItem() { Id = 1, Price = 10M },
new BuyingItem() { Id = 2, Price = 20M }
}
});
context.SaveChanges();
}
Specifying Id
which will be used in database is crucial otherwise each Update-Database
will create new records.
AddOrUpdate
doesn't support changing relations in any way so you cannot use it to add or remove relations in next migration. If you need it you must manually remove relation by loading Parcel
with BuyingItems
and calling Remove
or Add
on navigation collection to break or add new relation.