Sunday, June 30, 2013

EF Code First: seeding with foreign keys

This is just a quick tip, more for future reference for myself than anything else.

When using Entity Framework Code First, one of the things you might want to do while developing the application is to seed your database with some test data. This can be something as easy as:

    protected override void Seed(InventoryDB context)
    {
      context.Products.AddOrUpdate(
        p => p.Name,
        new Product { Name = "Hammer", SalePrice = 11.99m, },
        new Product { Name = "Nail Pack x100", SalePrice = 0.05m, },
        new Product { Name = "Saw", SalePrice = 19.99m, },
        new Product { Name = "Toolkit", SalePrice = 39.99m, }
        );
      //...
    }

(I am using the AddOrUpdate method so the code detects whether those products already exist and doesn't add them again if they do.)

The Product class is quite simple and – most importantly – doesn't depend on any other:

public class Product
  {
    public int Id { get; set; }

    [StringLength(256)]
    public string Name { get; set; }

    public decimal? SalePrice { get; set; }
  }

However, with a more complex relationship like this one things get complicated:

  public class Acquisition
  {
    public int Id { get; set; }
    public int CompanyId { get; set; }

    public virtual ICollection<AcquisitionItem> Items { get; set; }
    public virtual Company Company { get; set; }
  }

  public class AcquisitionItem
  {
    public int Id { get; set; }
    public int AcquisitionId { get; set; }
    public int ProductId { get; set; }
    public decimal Quantity { get; set; }
    public decimal Price { get; set; }

    public virtual Acquisition Acquisition { get; set; }
    public virtual Product Product { get; set; }
  }

The problem occurs when I try to set the CompanyId and ProductId properties:

      context.Acquisitions.AddOrUpdate(
        new Acquisition
        {
          CompanyId = ?
          Items = new[]
          {
            new AcquisitionItem { ProductId = ?, Quantity = 20, Price = 5.99m },
            new AcquisitionItem { ProductId = ?, Quantity = 2000, Price = 0.01m },
          }
        }
      );

(The EF library is "smart" enough to automatically associate the AcquisitionItems with their parent Acquisition entity, which is great – otherwise this code would have been a lot more complicated.)

The solution is to add the products (and companies) first, call the SaveChanges method and then get the Ids of the entities I'm going to use; this is the complete Seed method:

    protected override void Seed(InventoryDB context)
    {
      //  This method will be called after migrating to the latest version.

      context.Products.AddOrUpdate(
        p => p.Name,
        new Product { Name = "Hammer", SalePrice = 11.99m, },
        new Product { Name = "Nail Pack x100", SalePrice = 0.05m, },
        new Product { Name = "Saw", SalePrice = 19.99m, },
        new Product { Name = "Toolkit", SalePrice = 39.99m, }
        );
      context.Companies.AddOrUpdate(
        c => c.Name,
        new Company { Name = "Acme" },
        new Company { Name = "Hotpoint" }
        );

      context.SaveChanges();

      // get the name -> id mappings
      var products = context
        .Products
        .ToDictionary(p => p.Name, p => p.Id);
      var companies = context
        .Companies
        .ToDictionary(c => c.Name, c => c.Id);

      context.Acquisitions.AddOrUpdate(
        new Acquisition
        {
          CompanyId = companies["Acme"],
          Items = new[]
          {
            new AcquisitionItem { ProductId = products["Hammer"], Quantity = 20, Price = 5.99m },
            new AcquisitionItem { ProductId = products["Nail Pack x100"], Quantity = 2000, Price = 0.01m },
          }
        },
        new Acquisition
        {
          CompanyId = companies["Hotpoint"],
          Items = new[]
          {
            new AcquisitionItem { ProductId = products["Saw"], Quantity = 10, Price = 12.99m },
            new AcquisitionItem { ProductId = products["Toolkit"], Quantity = 10, Price = 29.99m },
          }
        }
        );

      context.SaveChanges();
    }

I used the ToDictionary extension method to create name-to-Id mappings for products and companies, which allowed me to avoid using a construct like:

            new AcquisitionItem { ProductId = Products.FirstOrDefault(p => p.Name == "Saw").Id, Quantity = 10, Price = 12.99m },

which would have worked but would have been much uglier.

(This was taken from my Inventory project up on GitHub.)

5 comments:

Anonymous said...

Thanks for this. I came to this article having errors with foreign key and I didn't think to think about saving the tables partway through so the data would exist when the next record type was processed. Totally forgot that it doesn't immediately update the database.

Thanks again!

Marcel said...

Great, I'm glad it helped someone.

Steven Woods said...

Thanks! Quick simple summary that helped me seed a set of tables with many to many relationships. Much appreciated!

Ubfacility Saas said...

thanks a lot for this......

BizTalkers Rob said...

worked well for me - thanks :)