Entity Framework taking a long time to return when I use Include

Question

I'm trying to make a simple query in my database with EF 6, C# and .NET 6. I have a table of users and a table of departments, the relationship between them is many to many but even though there are only 7 departments, 2 users and only one relationship, the query is taking a long time - more than 2 seconds! How can I improve it?

Here is the method that makes the query

public async Task<List<User>> FindAllAsync()
{
    try
    {
        return await _context.Users
                             .Include(obj => obj.Departments)
                             .ToListAsync();
    }
    catch
    {
        throw new Exception();
    }
}

In my User class I have an ICollection of Departments and vice-versa

public ICollection<Department>? Departments { get; set; }

I try to make the method synchronous but the result was the same and if I remove the inc the query ends very quickly.

Answer 1

I am assuming SQL Server?

This has to be something with your SQL Server schema. I would run the query directly in SSMS and hit CTRL+M to display the Actual Execution Plan.

Click the Execution Plan tab inside SSMS and look for where it goes sideways.