Entity Framework Core 5 - Single Navigation Property

Question

I'm using Entity Framework Core 5 and I'm having problems retrieving records with all of their associated related data. As you can see below, I have three classes; Person, Client and ReferralPerson. Both Client and ReferralPerson contain Person objects and Client also contains a ReferralPerson object.

public class Person
{
    public int ID { get; set; }

    [Required, StringLength(50)]
    public string FirstName { get; set; } = "N/A";

    [Required, StringLength(50)]
    public string LastName { get; set; } = "N/A";

    [StringLength(30)]
    public string PhoneNumberPrimary { get; set; }

    [StringLength(30)]
    public string PhoneNumberSecondary { get; set; }

    [StringLength(50)]
    public string Address { get; set; }

    [StringLength(50)]
    public string EmailAddress { get; set; }

}

public class Client
{
    public int ID { get; set; }
    public Person Person { get; set; }

    [StringLength(50)]
    public string CommunicationMethod { get; set; }
    public DateTime InitalContactDate { get; set; }       

    [Required, StringLength(100)]
    public string ReasonForVisit { get; set; }
    public ReferralPerson ReferralPerson { get; set; }

}

public class ReferralPerson
{
    public int ID { get; set; }
    public Person Person { get; set; }

    [StringLength(100)]
    public string BusinessName { get; set; }
    public int NumberOfReferrals { get; set; }

}

My OnModelCreating() method currently looks like the following:

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    //setup our relationships
    modelBuilder.Entity<Client>()
        .HasOne(c => c.Person);

    modelBuilder.Entity<Client>()
        .HasOne(c => c.ReferralPerson);

    modelBuilder.Entity<Person>().ToTable("People");
    modelBuilder.Entity<ReferralPerson>().ToTable("ReferralContacts");

}

Finally, I have a method that's used for searching Client records as follows:

public IEnumerable<Client> GetClientsByLastName(string lastName)
{
    var query = db.Clients
                  .Include(x => x.Person)
                  .Include(x => x.ReferralPerson)
                  .Where(x => string.IsNullOrEmpty(lastName) || 
                    x.Person.LastName.Contains(lastName))
                  .ToList();

    return query;
}

Looking at the database tables that are created by the migration, I can see that the relationships appear to be correct between the Clients table, Person (People) table and ReferralContacts table. I can also see that records are being added correctly to these tables when I save a new record. The issue is with the retrieving. Even though I am using ".Include(x => x.Person)" in the "GetClientsByLastName()" method, the person object does not get populated when retrieving a Client record. I've spent quite a bit of time looking at other posts and solutions as well as the EF Core documentation, but I'm still missing something. Any help would be greatly appreciated.

EDIT 5-9-2022:

Here is the EF generated SQL query from the LINQ Query shown above

DECLARE @__lastName_0 nvarchar(50) = N'bur';
SELECT [c].[ID], 
[c].[CommunicationMethod], 
[c].[InitalContactDate], 
[c].[PersonID], 
[c].[ReasonForVisit],
[c].[ReferralPersonID],
[p].[ID],
[p].[Address],
[p].[EmailAddress],
[p].[FirstName],
[p].[LastName],
[p].[PhoneNumberPrimary],
[p].[PhoneNumberSecondary],
[r].[ID],
[r].[BusinessName],
[r].[NumberOfReferrals],
[r].[PersonID]
FROM [Clients] AS [c]
LEFT JOIN [People] AS [p] ON [c].[PersonID] = [p].[ID]
LEFT JOIN [ReferralContacts] AS [r] ON [c].[ReferralPersonID] = [r].[ID]
WHERE (@__lastName_0 LIKE N'') OR (CHARINDEX(@__lastName_0, [p].[LastName]) > 0)
ORDER BY [p].[LastName]

When I execute that SQL Query in SSMS all of the related data is being retrieved as expected, so the issue does not appear to be in the query. It is how that data is then being populated into the underlying objects that is the issue.

Answer 1

Long story short, you're setting up this empty data you're seeing, and EFC won't overwrite it if you do. Comment out/delete all your entity constructors that are doing things like this:

    public Client()
    {
        Person = new Person(0, "", "");
        ContactType = new ContactType();
        ReferralPerson = new ReferralPerson();
        NewsletterOption = new NewsletterOption();
    }

If the entities are made so as to look like those presented in the question, everything works