I'm trying to get a related entity from the database but cannot get EF to produce a join. The Person in User object is always null.

I've lost track of what exactly I have tried already but I have been mostly re-configuring the relationships with data annotations and/or Fluent API.

These are my classes:

public class User
{
    public Guid Id { get; set; }
    public string Username { get; set; }
    public byte[] Password { get; set; }
    public byte[] Salt { get; set; }
    public UserRole Role { get; set; }
    public Person Person { get; set; }

    public User()
    {
        Id = Guid.NewGuid();
    }
}

public class Person
{
    public Guid Id { get; set; }
    public string FirstName { get; set; }
    public string LastName { get; set; }
    public int PersonalIdentificationNumber { get; set; }
    public string PhoneNumber { get; set; }
    public string Email { get; set; }
    public PersonImage PersonImage { get; set; }
    public Address Address { get; set; }
    public User User { get; set; }

    public Person()
    {
        Id = Guid.NewGuid();
    }
}

ModelBuilder:

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        var personEntityTypeBuilder = modelBuilder.Entity<Person>();

        personEntityTypeBuilder
            .HasOne(person => person.User)
            .WithOne(user => user.Person)
            .HasForeignKey<Person>("UserId");
    }

This is how I'm trying to retrieve the user currently, the include in there makes absolutely no difference, the exact same SQL query is created:

_db.Users.Include(u => u.Person).Single(i => i.Id == userId);

This is the query that EF produces:

SELECT TOP(2) [u].[Id], [u].[Password], [u].[Role], [u].[Salt], [u].[Username]
FROM [Users] AS [u]
WHERE [u].[Id] = @__userId_0

ER diagram

EDIT after T. Nielsen helped find the solution:

This was the part I was missing

        modelBuilder.Entity<Person>()
          .HasOne(person => person.User)
          .WithOne(user => user.Person)
          .HasForeignKey<Person>(person=>person.Id);

CodePudding user response：

It looks like you've stopped to see if it worked, simply before you've configured all elements and obviously your ForeignKey is wrong because you have no such property on Your class, kind of looks like a half attempt at something.

There is a more explict way when you explictly tell about HasForeignKey, but then you need to also specify principal key but it does have some variances they you should not have to get into.

While Id is ok to use and EF will typically end up chosing this, I like to be explict about the Id field whenever it is not "<typename>Id" format, which is Ef default and you can get around it with this reduced DbContext below. Perhaps your are unfamiliar with the "include" part of DbSets also, but checkout example, hope it helps.

public class UserDbContext : DbContext
{
    protected DbSet<User> Users;
    protected DbSet<Person> Persons;

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {  
        modelBuilder.Entity<Person>()
            .HasKey(x => x.Id);
        modelBuilder.Entity<Person>()
            .HasOne(person => person.User)
            .WithOne(user => user.Person);
        modelBuilder.Entity<Person>()
            .HasOne(person => person.PersonImage)
            .WithOne(img => img.Person);
        modelBuilder.Entity<Person>()
            .HasOne(person => person.Address)
            .WithOne(a => a.Person);

        modelBuilder.Entity<User>()
            .HasKey(k => k.Id);
        modelBuilder.Entity<User>()
            .HasOne(p => p.Person)
            .WithOne(u => u.User);
        modelBuilder.Entity<User>()
            .HasOne(p => p.Role)
            .WithMany(u => u.Users);            
    }

    public Person? GetPerson(Guid personId)
    {
        if(personId == Guid.Empty) throw new ArgumentException("Please initialize variable", nameof(personId));
        return Persons
                .Include(u => u.User)
                .FirstOrDefault(User => User.Id == personId);
    }
}