Entity Framework 6 Code first One-to-Many relation in 3rd level child tables

asp.net-identity-2 c# ef-code-first entity-framework-6

Question

I have ApplicationUser table created by ASP.NET identity framework and want to use another table Customer with one-to-one relation. The PK of ApplicationUser table is Id of type string. And the Customer table has a FK of type string. I am not allowed to change the Id type for ApplicationUser table. But customer table must have an identity(auto number) column with int type. This is fine.

And then this Customer table has a one-to-many relation with Contact table. enter image description here

Both Customer and Contact tables have unique identity column Ids. The Contact table has a CustomerId Column. If there is no AspNetUser table, code first generates one-to-many relation using these id and CustomerId columns. Since the AspNetuser table has one-to-one relation with Customer, this doesn’t use CustomerId. But the code first generate its own string key (Customer_UserId) to build this one-to-many relation.

How do I use Customer table column to relate Contact tables CustomerId column to map the relation using EF6 code first approach?

Here is the complete code:

ApplicationUser

public class ApplicationUser : IdentityUser
{
    //Navigation   properties
    public Customer Customer { get; set; }

    public async Task<ClaimsIdentity> GenerateUserIdentityAsync(UserManager<ApplicationUser> manager)
    {
        var userIdentity = await manager.CreateIdentityAsync(this, DefaultAuthenticationTypes.ApplicationCookie);
        return userIdentity;
    }
}

Customer

public class Customer
{
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    [Index(IsUnique = true)]
    public int Id { get; set; }

    [Key, ForeignKey("ApplicationUser")]
    public string UserId { get; set; }

    public string BusinessName { get; set; }

    //Navigation properties
    [ForeignKey("UserId")]
    public ApplicationUser ApplicationUser { get; set; }
    public virtual ICollection<Contact> Contacts { get; set; }
}

Contact

public class Contact
{
    public int Id { get; set; }
    public int CustomerId { get; set; }

    public string LastName { get; set; }
    public string FirstName { get; set; }
    public string OfficeLocation { get; set; }
    //Navigation properties
    [Required]
    public Customer Customer { get; set; }

}

ApplicationDbContext

public class ApplicationDbContext : IdentityDbContext<ApplicationUser>
{
    public ApplicationDbContext(): base("DefaultConnection", throwIfV1Schema: false){}

    static ApplicationDbContext()
    {
        // Set the database intializer which is run once during application start
        // This seeds the database with admin user credentials and admin role
        Database.SetInitializer<ApplicationDbContext>(new ApplicationDbInitializer());
    }

    public static ApplicationDbContext Create()
    {
        return new ApplicationDbContext();
    }

    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        base.OnModelCreating(modelBuilder);
        modelBuilder.Entity<Customer>()
            .HasRequired(u => u.ApplicationUser).WithRequiredDependent(c => c.Customer);
    }

    public System.Data.Entity.DbSet<WebServer.Models.Customer> Customers { get; set; }

    public System.Data.Entity.DbSet<WebServer.Models.Contact> Contacts { get; set; }
}
1
3
8/30/2015 5:27:30 AM

Accepted Answer

I figured it out. ApplicationUser and Customer tables are one-to one so the both tables has the same PK (String Id in ApplicationUser and String UserId in Customer). Logically we can think these two tables as one table with PK of type string. The Contact table is a child of Customer table. Therefore one-to-many relation between Customer and Contact. Therefore Contact table must have a Foreign Key of type string. So the CustomerId in the model should be type of string instead int. Also to instruct code-first to use this field I need to add [ForeignKey("CustomerId")] attribute to public Customer Customer { get; set; } property.

So the new Contact model should be like this

public class Contact
{
   public int Id { get; set; }
   public string CustomerId { get; set; }
   public string LastName { get; set; }
   public string FirstName { get; set; }
   public string OfficeLocation { get; set; }

   //Navigation properties

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

PS: In my original application I have another level in this table hierarchy. A contact can have many address, so the Address table can use FK of type int with Contacts PK to form another one-to-many relation.

0
9/2/2015 1:33:55 AM

Popular Answer

I found that removing your explicit foreign keys and also removing the contact's 'Customer' property changed the schema significantly.

This removes the Customer_UserId.

Looks like this is the behaviour you are after.



Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow