Using Entity Framework Core query with .Include() to eager load child data is causing SQL Server to time out

Question

I have an Entity Framework Core 2.1 project with a parent/child entity relationship . When I execute a query against the parent table, the results are returned nearly immediately. When I attempt to Context.Parents.Include(parent => parent.Children), the query appears to execute properly on the server (by viewing the SQL Server Profiler output), but the application code gets a timeout exception after 20 seconds. This is a small data set (~20 records in parent table and ~100 records in child table), so there should not be any real performance issue. Does anyone have any idea what I'm doing wrong here? I suspect that I have misconfigured my context, but I'm not certain.

EDIT
Here are my classes:

public partial class A
{
    public int Id { get; set; }
    public bool Active { get; set; }
    public ICollection<B> BItems { get; set; }
}

public partial class B
{
    public int Id { get; set; }
    public int AId { get; set; }
    public A AItem { get; set; }
}

public partial class Context
{
    public virtual DbSet<A> A { get; set; }
    public virtual DbSet<B> B { get; set; }
    protected override OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<B>(entity =>
        {
            // Other field setup, unrelated to relationship...

            entity.HasOne(b => b.A)
                .WithMany(a => a.BItems)
                .HasForeignKey(b => b.AId)
                .OnDelete(DeleteBehavior.ClientSetNull)
                .HasConstraintName("FK_ConstraintName");
        });

        modelBuilder.Entity<A>(entity =>
        {
             // Other field setup, unrelated to relationship
        });
    }
    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        if (!optionsBuilder.IsConfigured)
        {
            optionsBuilder.UseSqlServer(...connection string from configuration goes here...);
        }
    }
}

My usage code looks like this:

var results = await context.A
    .Include(a => a.BItems)
    .Where(a => a.Active)
    .ToListAsync();

I'm using EF Core 2.1 against SQL Server 2016. I see 2 queries executed in SQL Server Profiler; one to query for the A records and one to query for the B records that is INNER JOINed against the A table.

Hi, "I suspect that I have misconfigured my context" - let us see that then (the relevant entity classes, context, fluent configuration). Also the EF Core version. W/o that there is nothing we can do to help. Normally the query with child collection include should execute 2 SQL queries, and neither should take so long with such data. — Ivan Stoev
@IvanStoev I agree that the query should not take long. And when I watch them in SQL Server Profiler, the two queries appear to execute rapidly. The client never receives the results, however, and instead times out. I will edit my post with additional details about the configuration... — Tom Whittaker
Ok, nothing wrong/unusual in what you've shown. And you are saying the var result = await … .ToListAsync(); line is throwing timeout exception? Or some other code after that (serialization)? — Ivan Stoev
You are hearing me correctly. I am stepping through this code in the debugger. It hits the line with var results = ..., I step over that line and I see the queries in SQL Server Profiler, but the data is never returned in the debugger. Instead, a timeout exception is thrown. — Tom Whittaker
I've running out of ideas - never seen something like this before, and of course can't reproduce with the code you've shared. The only difference is that I'm running on LocalDB SqlServer database. Probably you should fill an issue in EF Core issue tracker. Good luck. — Ivan Stoev

Tom Whittaker Tom Whittaker · Accepted Answer · 2018-12-10T15:36:49

This issue resolved itself over time, and I'm not entirely sure what did it. Part of my suspicious was hardware related, as we had other local issues on our LAN that manifested themselves in other ways while working on this project.

Using Entity Framework Core query with .Include() to eager load child data is causing SQL Server to time out

1 Answers