Entity Framework 6 Code first: How to seed data with a 'circular' relationship and store-generated columns?

Question

I am new to EF Code First, and I am trying to seed my database with some data using code-first migrations. I have managed to solve several errors so far, but now I am stuck and have not been able to find the answer. I have two issues when updating the database from my code.

I have several objects which have various many-to-many and one-to-one relationships, and some eventually create a circle. I am not sure if this is the cause of second problem or not, when I try to seed the database.

1. The first error I have is: A dependent property in a ReferentialConstraint is mapped to a store-generated column. Column: 'LicenseId'.

Is there a way I can use a db generated id as a foreign key? Is is just the order in which I am creating/inserting objects? (see seeding code below)

If I don't use [DatabaseGeneratedAttribute(DatabaseGeneratedOption.Identity)] in the License I get an error about not being able to implicitly insert an id that is not generated by the database.

public class License : Entity
{
    [Key, ForeignKey("Customer")]
    [DatabaseGeneratedAttribute(DatabaseGeneratedOption.Identity)]
    public int LicenseId { get; set; }

    [Required(ErrorMessage = "Date Created name is required")]
    public DateTime DateCreated { get; set; }

    public virtual ICollection<ProductLicense> ProductLicenses { get; set; } // one License has many ProductLicenses
    public virtual Customer Customer { get; set; } // one Customer has one License
}

public class Customer : Entity
{
    [Key]
    public int CustomerId { get; set;}

    [Required(ErrorMessage = "Username is required")]
    [Column(TypeName = "nvarchar")]
    [MaxLength(500)]
    public string Name { get; set; }

    public virtual ICollection<User> Users { get; set; } // one Customer has many users
    public virtual License License { get; set; } // one Customer has one License
    //public virtual ICollection<License> License { get; set; } // one Customer has many Licenses
}

2. If I change the License-Customer to be a many-to-many relationship (which I do not want) I get the following error: Cannot insert duplicate key row in object 'dbo.Users' with unique index 'IX_Username'.

These are all my relationships:

Customer -many-> User -many-> Role -many-> Permission -one- ProductLicense <-many- License -one- Customer

Here is the code I have been using in protected override void Seed(DAL.Models.Context context), creating the the objects in code and then using AddOrUpdate:

// Default Customers - create
var customers = new[]{
    new Customer { Name = "cust_a" },
    new Customer { Name = "cust_b" },
    new Customer { Name = "cust_c" }
};

// Default Licenses - create
var licenses = new[]{
    new License { DateCreated = DateTime.Now.AddDays(-3), Customer = customers[0] },
    new License { DateCreated = DateTime.Now.AddDays(-2), Customer = customers[1] },
    new License { DateCreated = DateTime.Now.AddDays(-1), Customer = customers[2] }
};

// Default ProductLicenses - create, and add default licenses
var productLicenses = new[]{
    new ProductLicense { LicenceType = LicenseType.Annual, StartDate = DateTime.Now, License = licenses[0] },
    new ProductLicense { LicenceType = LicenseType.Monthly, StartDate = DateTime.Now, License = licenses[1] },
    new ProductLicense { LicenceType = LicenseType.PAYG, StartDate = DateTime.Now, License = licenses[2] }
    };

// Default Permissions - create, and add default product licenses
var permissions = new[]{
    new Permission { Name = "Super_a", ProductLicense = productLicenses[0] },
    new Permission { Name = "Access_b", ProductLicense = productLicenses[1] },
    new Permission { Name = "Access_c", ProductLicense = productLicenses[2] }
};

// Default Roles - create, and add default permissions
var roles = new[]{
    new Role { Name = "Super_a", Permissions = permissions.Where(x => x.Name.Contains("_a")).ToList() },
    new Role { Name = "User_b", Permissions = permissions.Where(x => x.Name.Contains("_b")).ToList() },
    new Role { Name = "User_c", Permissions = permissions.Where(x => x.Name.Contains("_c")).ToList() }
};

// Default Users - create, and add default roles
var users = new[]{
    new User { Username = "user@_a.com", Password = GenerateDefaultPasswordHash(), Salt = _defaultSalt, Roles = roles.Where(x => x.Name.Contains("_a")).ToList() },
    new User { Username = "user@_b.co.uk", Password = GenerateDefaultPasswordHash(), Salt = _defaultSalt, Roles = roles.Where(x => x.Name.Contains("_b")).ToList() },
    new User { Username = "user@_c.com", Password = GenerateDefaultPasswordHash(), Salt = _defaultSalt, Roles = roles.Where(x => x.Name.Contains("_c")).ToList() }
        };

// Default Customers - insert, with default users

foreach (var c in customers)
{
    c.Users = users.Where(x => x.Username.Contains(c.Name.ToLower())).ToList();
    context.Customers.AddOrUpdate(c);
}

I also tried changing the first part following //Default Customers - create to

// Default Customers - create and insert
context.Customers.AddOrUpdate(
    u => u.Name,
    new Customer { Name = "C6" },
    new Customer { Name = "RAC" },
    new Customer { Name = "HSBC" }
);

context.SaveChanges();

var customers = context.Customers.ToList();

I would greatly appreciate help with this, so that I can seed my database with appropriate data.

Many thanks for your help, and sorry for the long post.

--- UPDATE ---

After following Chris Pratt's excellent answer below I now get the following error: Conflicting changes detected. This may happen when trying to insert multiple entities with the same key.

Here is my new code for the seeding and all the models involved: https://gist.github.com/jacquibo/c19deb492ec3fff0b5a7

Can anyone help with this?

Answer 1

Seeding can be a bit complicated, but you just need to keep a few things in mind:

1. Anything added with AddOrUpdate will be, well, added or updated. But anything else will be added, not updated. In your code, the only thing you're using AddOrUpdate with is Customer.

2. EF will add related items when AddOrUpdate item is being added, but it ignores those relations when that item is being updated.

Based on that, if you're adding object hierarchies like this, then you have to take a little extra care. First, you need to call SaveChanges between levels of the hierarchy, and second, you need to work with ids, not relationships. For example:

var customers = new[]{
    new Customer { Name = "cust_a" },
    new Customer { Name = "cust_b" },
    new Customer { Name = "cust_c" }
};
context.Customers.AddOrUpdate(r => r.Name, customers[0], customers[1], customers[2]);
context.SaveChanges()

Now you have all your customers taken care of, and they each will have an value for their id, one way or another. Then, start digging into your hierarchy:

// Default Licenses - create
var licenses = new[]{
    new License { DateCreated = DateTime.Now.AddDays(-3), CustomerId = customers[0].CustomerId },
    new License { DateCreated = DateTime.Now.AddDays(-2), CustomerId = customers[1].CustomerId },
    new License { DateCreated = DateTime.Now.AddDays(-1), CustomerId = customers[2].CustomerId }
};
context.Licenses.AddOrUpdatE(r => r.DateCreated, licenses[0], licenses[1], licenses[2]);

You don't need to call SaveChanges if you're dealing with objects at the same level in the hierarchy until you've defined them all. However, if you have an entity that references something like License, then you would want to call SaveChanges again before adding those.

Also, notice that I'm switch to setting the id instead of the relationship. Doing it this way will allow you to update the relationship later by changing the id. For example:

// Changed customer id from customer[1] to customer[0]
new License { DateCreated = DateTime.Now.AddDays(-2), CustomerId = customers[0].CustomerId },

Whereas the following would not work:

// Attempted to change customer[1] to customer[0], but EF ignores this in the update.
new License { DateCreated = DateTime.Now.AddDays(-2), Customer = customers[0] },

Of course, you don't actually have a CustomerId property on License, but you should. While EF will automatically generate a column to hold the relationship without it, you can never actually get at the foreign key value without an explicit property, and for more reasons than this alone, being able to work with the actual foreign key is extremely beneficial. Just follow this convention for all your reference properties:

[ForeignKey("Customer")]
public int CustomerId { get; set;}
public virtual Customer Customer { get; set; }

The ForeignKey attribute isn't always required, depending on whether your foreign key and reference property names align to EF's conventions for such things, but I find it easier and less error-prone to just be explicit about my intentions.