EF Core 8 -> Navigation Property always fetching only one record

Question

I have a very simple database with the following two entities - Customers and States.

State_Id is a foreign key referring to States table's primary key.

CREATE TABLE [dbo].[Customers]
(
    [Id] [int] NOT NULL,
    [FirstName] [varchar](50) NOT NULL,
    [LastName] [varchar](50) NOT NULL,
    [State_Id] [int] NULL,

    CONSTRAINT [PK_Customer] PRIMARY KEY CLUSTERED 
)

ALTER TABLE [dbo].[Customers] WITH CHECK 
    ADD CONSTRAINT [FK_Customers_States] 
        FOREIGN KEY([State_Id]) REFERENCES [dbo].[States] ([Id])
GO

ALTER TABLE [dbo].[Customers] CHECK CONSTRAINT [FK_Customers_States]
GO

CREATE TABLE [dbo].[States]
(
    [Id] [int] NOT NULL,
    [Name] [varchar](50) NOT NULL,

    CONSTRAINT [PK_States] PRIMARY KEY CLUSTERED 
)

Corresponding EF model classes looks like this.

public class Customer
{
    [Required]
    public int Id { get; set; }
    public string FirstName { get; set; }
    public string LastName { get; set; }
    public virtual State State { get; set; }//Navigation Property
    public int State_Id { get; set; }
}

public class State
{
    public int Id { get; set; }
    public string Name { get; set; }
    //Navigation property
    public virtual ICollection<Customer>? Customers { get; } = new List<Customer>();
}

OnModelCreating method looks like this:

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<Customer>()
        .HasKey(c => c.Id);
    modelBuilder.Entity<State>()
        .HasKey(c => c.Id);
    modelBuilder.Entity<Customer>()
        .HasOne(c => c.State)
        .WithMany(s => s.Customers)
        .HasForeignKey(p => p.State_Id);
    modelBuilder.Entity<State>()
        .HasMany(s => s.Customers);
}

Now I want to fetch all the customers that belong to a certain state using the States navigation property named Customers.

This is my code:

public List<Customer> Get(string stateName)
{
    var result = _appDbContext.States
                              .Where(s => s.Name == stateName)
                              .FirstOrDefault();

    // result.Customers.Count is always 1 even if city has more than 1 customers

    // Alternatively below query returns correct result.
    var correct = _appDbContext.Customers
                               .Where(c => c.State_Id == 2);

    return customers;
}

Am I missing anything?

Answer 1

Without eager loading or having lazy loading enabled, the behaviour of EF when it comes to related entities is that the DbContext will fill in any related entities it happens to be tracking at the time. This can lead to odd behaviour that is situational.

With a clean context, a statement like:

var result = _appDbContext.States.Where(s => s.Name == stateName).FirstOrDefault();

... would always come back with an empty customers collection. The only reason you would potentially get back a single, or multiple customers is if that DbContext happened to already be tracking any customers associated with that state. When you request the State, EF will scan the tracking cache for related data to associate automatically. This can be an incomplete picture, and as soon as you add an AsNoTracking() to speed queries up, that behaviour stops leading to accusations of broken queries.

The best way to handle reads with EF without relying on eager or lazy loading is to read /w projection then fetch individual entities and explicit relationships /w eager loading when performing updates. (I.e. using Select) When you get in the habit of using Projection you avoid the tracked state issues if you forget to eager load a relationship, and you also stand to build more efficient queries than if you get into the alternative habit of eager loading everything "just in case".