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'm trying to utilize only one.Address table to keep addresses of many system things in a genericKeyId field. Both a customer and a vendor are allowed to have several addresses.

the Address range:

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; }

class Customer:

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

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

class Vendor:

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);

I am seeing the following problem while attempting to seed the database (adding a Vendor along with a few addresses):

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'.

This is most likely due to referential integrity, which states that there isn't a customer in the database with the ID you're attempting to save.KeyId .

Is there a way to use EF with FluentAPI to do this, or am I playing with fire? Simply said, having to develop a class namedCustomerAddress and VendorAddress if every property is the same. It nearly seems like I must provide a dual foreign key, which EF forbids.

Additional information: I believe I will attempt to configure everything in SQL Management Studio before adding a database-first EF project to Visual Studio. I'm interested to see how the model and database context will be created.

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

Popular Answer

It seems that there is an incorrect mapping between the Customer/Vendor and Address classes.

In the Address table, distinct ForeignKey columns should refer to various parent tables (Customer/Vendor).

In light of the modifications, your entities will appear as follows:

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);

I really hope it will.

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