Entity Framework Core - realtion between two tables without using primary key

Question

I would like to write Console application in dotnet core 2.1 with EFCore.Postgres 2.1.2.

I have two tables in existing Database. Every table has it's own Primary key, but these tables should be related via another field - UserId

My models:

public class UserAddress
{
    public int UserAddressId {get;set;}
    public int UserId {get;set;}
    public string City {get;set;}
    public string Street {get; set;}

    public virtual UserDetails UserDetails {get;set;}
}

public class UserDetails
{
    public int UserDetailId {get;set;}
    public int UserId {get;set;}

    public string Name {get;set;}
    public string Surname {get;set;}
}

How Can I 'Teach' Entityframework to join these tables wia UserId? I would like to use them like below

using (var MyDb = new GraffitiDbContext())
{
    var query = MyDb.UserAddress              
        .Include(row => row.UserDetails);

    foreach (var item in query)
    {
        Console.Writeline(item.UserDetails.Name);
    }
}

How would you define such relationship in postgres? What are the restrictions? Can there be multiple UserAddress or UserDetails with the same UserId? Can there be no UserAddress for UserDetails with a speific UserId and vice versa? — Imantas
It's not possible in general if the UserId is not unique in one of the tables. Also don't you have User table where UserId is a PK, so these 2 tables relate indirectly through it? — Ivan Stoev
In postgres there is a table with UserId as a PK, and these two tables has FK. but in my console app I don't need data from that table. If you mind strict relationship between UserAddres and UserDetails - there isn't such a relationship. It's one to one relation. Assume that I don't need UserDetails, if there is no UserAddress for specyfied UserId. In both tables UserId is unique. — Ariel Sz
You may try mapping UserId in both entities as alternate key and try to map the one-to-one relationship with modelBuilder.Entity<UserAddress>().HasOne(e => e.UserDetails).WithOne().HasPrincipalKey<UserAddress>(e => e.UserId).HasForeignKey<UserDetails>(e => e.UserId). It might eventually work, but I personally would keep User entity (even with just PK property) and the real relationships. — Ivan Stoev
Thank you Ivan Stoev, that's the answear I was looking for - is it possible, but it's not the best way. I would like to learn EF, that's why i dont use another ORM or ADO.NET. — Ariel Sz

Kieran Devlin Kieran Devlin · Accepted Answer · 2019-01-07T13:31:43

With your current configuration you cannot infer this join as you would have to go from UserAddress to User to UserDetails. You can either add a relationship between UserAddress and UserDetails to make this traversal possible or you can use the following context navigation:

context.UserAddress.Include(x => x.User).ThenInclude(x => x.UserDetails)

The problem is that the User can have many UserDetails thus when you include the User on the UserAddress query, there is a possibility that you would get many records.

Address1 -> User1 -> UserDetail1
Address1 -> User1 -> UserDetail2

Entity Framework Core - realtion between two tables without using primary key

1 Answers