I was given (therefore I cannot change it) a database with entities that are versioned using a version number column.

The entities are Person, Domicile, Address. Each of this entities has a composite primary key made with id and version.

A Person can have multiple Domiciles, a Domicile references an Address. An Address can be referenced by different Domiciles. Therefore we have a one-to-many relationship between a Person and its Domiciles and there is a many-to-one relationship between Domicile and the Address.

Each entity reference the other entity only using the id, therefore every entity is versioned indipendently.

Let me give you an example of some tables:

Person

|id|version|etc.
|1 |1      |...
|1 |2      |...

Domicile

|id|version|person_id|address_id|etc...
|2 |1      |1        |3         |...
|2 |2      |1        |3         |...
|3 |1      |1        |4         |...

Address

|id|version|etc...
|3 |1      |...
|3 |2      |...
|4 |1      |...

As you can see each entity reference each other using just the id, and there can be multiple versions of the same entity therefore you can have the same id multiple times.

I would like my model entities to look something like:

public class Person {
  public long Id {get; set;}
  public long Version {get; set;}
  public List<Domicile> Domiciles {get; set;}
}

public class Domicile {
  public long Id {get; set;}
  public long Version {get; set;}
  public long AddressId {get; set;}
  public List<Address> AddressVersions {get; set;}
}

public class Address {
  public long Id {get; set;}
  public long Version {get; set;}
}

Configuring the relationship between a person and its domiciles it's straight-forward:

modelBuilder.Entity<Person>(entity => {
  //...
  entity.HasMany(p => p.Domiciles)
    .WithOne()
    .HasForeignKey(d => d.PersonId)
    .HasPrincipalKey(p => p.Id);
});

Now comes the part that I have no clue how to configure: the relationship between a Domicile and its Address versions. In theory this relation should be a many-to-one because one Address is used by multiple Domiciles.

I want to have ALL the Address versions on the Domicile so I've declared the property public List<Address> AddressVersions, but now I have to configure the navigation property.

If I try to configure the relationship as:

modelBuilder.Entity<Domicile>(entity => {
  //...
  entity.HasMany(p => p.AddressVersions)
    .WithMany());
});

This will not compile because a many-to-many relationship requires a navigation property on both sides.

If I try to model the relationship with a one-to-many configuration:

modelBuilder.Entity<Domicile>(entity => {
  //...
  entity.HasMany(p => p.AddressVersions)
    .WithOne()
    .HasPrincipalKey(p => p.AddressId);
});

It will not work because I don't have a foreign key on Address that refers to Domicile given that an Address is used by multiple Domiciles.

If I go the other way around with the relationship:

modelBuilder.Entity<Domicile>(entity => {
  //...
  entity.HasOne(p => p.Address)
    .WithMany()
    .HasForeignKey(p => p.AddressId)
    .HasPrincipalKey(d => d.Id);
});

Despite this works I get just one of the many possibile versions of an Address, losing information.

How can I model such relations like between Address and Domicile and making available on the principal entity all the versions of the related entity ?

EDIT:

Some clarifications on the data:

Every entity has a composite primary key defined as follows:

PRIMARY KEY (id, version);

This composite primary key uniquely identifies a specific record and version.

Every entity references the other entity with a foreign key defined as follows:

CREATE TABLE Domicile(
  --etc.
  CONSTRAINT fk_address FOREIGN KEY (address_id) REFERENCES address (id)
);

As you can see the foreign key references just a part of the primary key.

In the example table that I provided above I expect that when I select Person with id 1 and version 2 I get 3 domiciles back (id 2 and the two versions 1 and 2 and id 3 with just one version).
Then, for every version of domicile with id 2, I expect 2 addresses (id 3 with version 1 and 2) and for the single version of domicile with id 3 I exepect 1 address (id 4 with version 1)

In code we could write my expectation for Domicile with id 2 as:

Assert.That(domicile.Addresses.Count, Is.EqualTo(3));

Assert.That(domicile.Addresses[0].Id, Is.EqualTo(3));
Assert.That(domicile.Addresses[0].Version, Is.EqualTo(1));

Assert.That(domicile.Addresses[1].Id, Is.EqualTo(3)); //Same address id as before...
Assert.That(domicile.Addresses[0].Version, Is.EqualTo(2)); //...but different version

The problem seems to be that the foreign key does not reference unique rows because it uses just a part of the primary key, therefore this relationship cannot be modeled (maybe ?) with Entity Framework Core.

Hope this clarifies better what I have to work with and what I want to get.

CodePudding user response：

Short answer is what you want cannot be done with built-in functionality. Perhaps third party extensions/tools could help.

Id is not unique for any of your three entities so you cannot have a many-to-one or one-to-many relationship between them using only an Id property as the principal key (since the principal key must uniquely identify the related record).

Options:

1. manually join these entities and project the results
2. create DB views (if allowed) to provide the junction entities that EF could use for many-to-many relationships.