Single Address table used to store addresses for many entities (using double foreign key) with Entity Framework

c# entity-framework entity-framework-6 entity-framework-core sql

Question

I am attempting to use a single Address table to store addresses for several entities in the system with a generic KeyId field. A Customer can have multiple addresses and a Vendor can have multiple addresses.

Address class:

public int Id { get; set; }
public string Name { get; set; }
public string Address1 { get; set; }
public string Address2 { get; set; }
public string City { get; set; }
public string State { get; set; }
public string Country { get; set; }
public string ZipCode { get; set; }
// These 2 fields make it so I can get all of the addresses for a single Customer or Vendor
public string EntityType { get; set; }
public int KeyId { get; set; }

// Navigation properties
public Customer Customer { get; set; }
public Vendor Vendor { get; set; }
public Location Location { get; set; }

Customer class:

public int Id { get; set; }
public string Name { get; set; }

// Navigation properties
public IList<Address> Addresses { get; set; }

Vendor class:

public int Id { get; set; }
public string Name { get; set; }

// Navigation properties
public IList<Address> Addresses { get; set; }

DbContext:

    builder.Entity<Customer>()
        .HasMany(c => c.Addresses)
        .WithOne(a => a.Customer)
        .HasForeignKey(a => a.KeyId);

    builder.Entity<Vendor>()
        .HasMany(v => v.Addresses)
        .WithOne(a => a.Vendor)
        .HasForeignKey(a => a.KeyId);

When trying to seed the database (adding a Vendor along with a few addresses) I am running into an error that says the following:

SqlException: The MERGE statement conflicted with the FOREIGN KEY constraint "FK_Address_Customer_KeyId". The conflict occurred in database "MyDatabase", table "dbo.Customer", column 'Id'.

I am pretty sure this is because of referential integrity saying there is no customer in the database with the Id you are trying to store in KeyId.

Is there any possible way to do something like this w/ FluentAPI using EF or am I playing with fire? It just seems so crazy to have to create a class called CustomerAddress and VendorAddress if all properties are the same. It is almost as if I need to specify a dual foreign key which EF doesn't let you do.

Additional note: I think I am going to try and setup everything in SQL management studio then add a database first EF project in Visual Studio. I am curious to see how it will go about creating the model and db context.

1
4
6/1/2017 1:23:05 PM

Popular Answer

It looks like mapping between Customer/Vendor vs Address class is not correct.

You should have different ForeignKey columns in Address table pointing to different parent tables [Customer/Vendor].

So after the changes your entities will look like:

Address:

 public int Id { get; set; }
 public string Name { get; set; }
 public string Address1 { get; set; }
 public string Address2 { get; set; }
 public string City { get; set; }
 public string State { get; set; }
 public string Country { get; set; }
 public string ZipCode { get; set; }
 public int CustomerId { get; set; }
 public int VendorId { get; set; }


 // Navigation properties
 public Customer Customer { get; set; }
 public Vendor Vendor { get; set; }

DBContext:

builder.Entity<Customer>()
    .HasMany(c => c.Addresses)
    .WithOne(a => a.Customer)
    .HasForeignKey(a => a.CustomerId);

builder.Entity<Vendor>()
    .HasMany(v => v.Addresses)
    .WithOne(a => a.Vendor)
    .HasForeignKey(a => a.VendorId);

Hope it will help.

1
6/1/2017 5:43:09 AM


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