Entity framework one-to-many relationship configuration-CodePudding

A client has many adresses and a warehouse has one address. My C# code looks like this:

public class Address
{
    public int Id { get; set; }
    public string Text { get; set; }
    public Client Client { get; set; }    
    public Warehouse Warehouse { get; set; }
}

public class Client
{
    public int Id { get; set; }
    public string Name { get; set; }
    public int? AddressId { get; set; }
    public IList<Address> Addresses { get; set; }
}
public class Warehouse
{
    public int Id { get; set; }
    public string Name { get; set; }
    public int? AddressId { get; set; }
    public Address Address { get; set; }
}

Here are what the tables look like. I'll add some sample data to illustrate my point:

Adresses

 ───── ──────────────────── ─────────── 
| Id  | Text               | ClientId  |
 ───── ──────────────────── ─────────── 
| 1   | Address of WH 1    |           |
| 2   | Address of WH 2    |           |
| 3   | Address 1 of Cl 2  | 1         |
| 4   | Address 2 of Cl 1  | 2         |
| 5   | Address 1 of Cl 1  | 2         |
 ───── ──────────────────── ───────────

Clients

 ───── ─────────── ──────────── 
| Id  | Name      | AddressId  |
 ───── ─────────── ──────────── 
| 1   | Client 2  | (null)     |
| 2   | Client 1  | (null)     |
| 3   | Client 3  | (null)     |
 ───── ─────────── ────────────

Warehouses

 ───── ────────────── ──────────── 
| Id  | Name         | AddressId  |
 ───── ────────────── ──────────── 
| 1   | Warehouse 1  | 1          |
| 2   | Warehouse 2  | 2          |
 ───── ────────────── ────────────

My goal is to have no ClientId column in Addresses. Instead, for each AddressId, add an extra entry in Clients. Addresses would get cascade deleted with the deletion of a client as well.

 ───── ─────────── ──────────── 
| Id  | Name      | AddressId  |
 ───── ─────────── ──────────── 
| 1   | Client 2  | 4          |
| 1   | Client 2  | 5          |
| 2   | Client 1  | 1          |
| 3   | Client 3  | (null)     |
 ───── ─────────── ────────────

CodePudding user response：

Your original schema was somewhat broken, your proposed change is worse. Each table should have a unique PK. If you start doubling up IDs in a table it means you are resorting to composite PK between client ID and address ID, except that won't work for Clients that don't have an address. (#null Address ID)

What you are looking for is a Many-to-Many relationship between address and client:

From a table point of view:

Addresses

 ───── ──────────────────── 
| Id  | Text               |
 ───── ──────────────────── 
| 1   | Address of WH 1    |
| 2   | Address of WH 2    |
| 3   | Address 1 of Cl 2  |
| 4   | Address 2 of Cl 1  |
| 5   | Address 1 of Cl 1  |
 ───── ────────────────────

Clients

 ───── ─────────── 
| Id  | Name      |
 ───── ─────────── 
| 1   | Client 2  |
| 2   | Client 1  |
| 3   | Client 3  |
 ───── ───────────

ClientAddressses

 ────────── ─────────── 
| ClientId | AddressId |
 ────────── ─────────── 
| 1        | 4         |
| 1        | 5         |
| 1        | 1         |
 ────────── ───────────

From the entity perspective, Client can have a collection of Addresses, and Addresses can optionally have a collection of Clients.

public class Address
{
    public int Id { get; set; }
    public string Text { get; set; }
}

public class Client
{
    [Key]
    public int Id { get; set; }
    public string Name { get; set; }
    public virtual ICollection<Address> Addresses { get; set; } = new List<Address>();
}

Address would not have a reference to a Warehouse, but rather a Warehouse would have a reference to an Address via an AddressId.

public class Warehouse
{
    public int Id { get; set; }
    public string Name { get; set; }
    public virtual Address Address { get; set; }
}

Wiring the client and address means establishing the many-to-many relationship. This can be done in the OnModelCreating event handler for the DbContext:

// EF 6

modelBuilder.Entity<Client>()
    .HasMany(x => x.Addresses)
    .WithMany()
    .Map(x => { x.MapLeftKey("ClientId"); x.MapRightKey("AddressId"); x.ToTable("ClientAddresses"); });

modelBuilder.Entity<Warehouse>()
    .HasOptional(x => x.Address)
    .WithMany()
    .Map(x => x.MapKey("AddressId"));

In EF Core that mapping looks a bit different, and depending on which version you might be using that may or may not require defining ClientAddresses as an entity class. (Required EF Core 3.1 and earlier, not required EF Core 5/6)

While the Warehouse table will have an Address ID to point to it's address, and possibly a Client ID to point to the client. (depending on the relationship between those two) When using navigation properties I highly recommend not declaring the FKs as properties in the entity. This creates two sources of truth that can be mismatched and lead to inconsistent behaviour when updated depending on whether the navigation property was loaded or not. Instead I recommend using shadow properties for the FK fields and always use the navigation properties. (Where speed is more important for things like bulk updates, use FKs and leave out the navigation property) Shadow properties are supported much better in EF Core, so the mapping of them is different to what I have above. (EF6)