I am currently using EF Core 6 (w/ lazy loading) to access my DB.
When I access my desired data like this:
var depCount = admin.Departments.Count(d => !d.Deleted)

SQL Server Profiler shows me this:

exec sp_executesql N'SELECT [t].[DepId], [t].[BaseDepId], [t].[CreatedTimeStamp], [t].[CustCode], [t].[Deleted], [t].[DeletedTimeStamp], [t].[DepName], [t].[ForSingleSurvey], [t].[Level], [t].[SendInvitationMails], [a].[AdminId], [t].[AdministratorsAdminId], [t].[DepartmentsDepId], [t0].[AdministratorsAdminId], [t0].[DepartmentsDepId], [t0].[AdminId], [t0].[AdminEmail], [t0].[AdminPwdHash], [t0].[AuthToken], [t0].[CreatedTimeStamp], [t0].[CustCode], [t0].[CycleId], [t0].[EmailConfirmed], [t0].[FirstName], [t0].[LastName], [t0].[LastTokenGenerated], [t0].[OnlyManaging]
FROM [Administrators] AS [a]
INNER JOIN (
    SELECT [d].[DepId], [d].[BaseDepId], [d].[CreatedTimeStamp], [d].[CustCode], [d].[Deleted], [d].[DeletedTimeStamp], [d].[DepName], [d].[ForSingleSurvey], [d].[Level], [d].[SendInvitationMails], [a0].[AdministratorsAdminId], [a0].[DepartmentsDepId]
    FROM [AdministratorDepartment] AS [a0]
    INNER JOIN [Departments] AS [d] ON [a0].[DepartmentsDepId] = [d].[DepId]
) AS [t] ON [a].[AdminId] = [t].[AdministratorsAdminId]
LEFT JOIN (
    SELECT [a1].[AdministratorsAdminId], [a1].[DepartmentsDepId], [a2].[AdminId], [a2].[AdminEmail], [a2].[AdminPwdHash], [a2].[AuthToken], [a2].[CreatedTimeStamp], [a2].[CustCode], [a2].[CycleId], [a2].[EmailConfirmed], [a2].[FirstName], [a2].[LastName], [a2].[LastTokenGenerated], [a2].[OnlyManaging]
    FROM [AdministratorDepartment] AS [a1]
    INNER JOIN [Administrators] AS [a2] ON [a1].[AdministratorsAdminId] = [a2].[AdminId]
    WHERE [a2].[AdminId] = @__p_0
) AS [t0] ON [t].[DepId] = [t0].[DepartmentsDepId]
WHERE [a].[AdminId] = @__p_0
ORDER BY [a].[AdminId], [t].[AdministratorsAdminId], [t].[DepartmentsDepId], [t].[DepId], [t0].[AdministratorsAdminId], [t0].[DepartmentsDepId]',N'@__p_0 int',@__p_0=122

which obviously is very inefficient and way too much overhead.

However, when I access my desired data like this:

var depCount = await context.Departments.CountAsync(d => d.Admins.Any(a => a.AdminId == admin.AdminId) && !d.Deleted)

the Profiler shows me the following statement:

exec sp_executesql N'SELECT COUNT(*)
FROM [Departments] AS [d]
WHERE EXISTS (
    SELECT 1
    FROM [AdministratorDepartment] AS [a]
    INNER JOIN [Administrators] AS [a0] ON [a].[AdministratorsAdminId] = [a0].[AdminId]
    WHERE ([d].[DepId] = [a].[DepartmentsDepId]) AND ([a0].[AdminId] = @__a_AdminId_0)) AND ([d].[Deleted] = CAST(0 AS bit))',N'@__a_AdminId_0 int',@__a_AdminId_0=113

which is what I would want.

Does anybody know if I can produce that behavior with the first accessing method (via the Navigation Property)? Since this would be way easier to code...

Thank you in advance!

CodePudding user response：

The first example will access the collection if eager loaded, otherwise if you have lazy loading enabled then the proxy will result in a DB hit to load the related Departments. Based on that you're seeing the query run then that indicates lazy loading is enabled and running. Lazy loading incurs a performance hit, but serves as a safety net to ensure that if data is available, it can be loaded when accessed.

When loading entities you need to plan ahead a bit and either ensure data you need will be eager loaded with the entity(ies) or that you are projecting the data down to include all of the details you will need.

For example, eager loading:

var admin = await context.Admins
    .Include(x => x.Departments)
    .SingleOrDefaultAsync(x => x.AdminId == adminId);

// later...
var departmentCount = admin.Departments.Count();

This avoids the extra DB hit as the departments would have been loaded when the Admin was. The cost to consider with eager loading is that you are still loading a lot of data (everything about admin and all of their departments and any other related data you eager load) which may not be necessary. In the above case we just want a count. The issue I often find with teams that insist on loading entities and want to avoid lazy loading is that they default to eager loading everything. Still, when loading single entities, eager loading related data is generally Ok where it is useful for that data to be loaded.

When reading data especially, it can be much better to consider using projection solutions to fetch the data you want to consume. For instance take a situation where you want to search for Admins and include information like their department count. Using eager loading you'd have something like:

var admins = await context.Admins
    .Include(x => x.Departments)
    .Where(x => *some condition*)
    .Skip(pageNumber * pageSize)
    .Take(pageSize)
    .ToListAsync();

Even best case that we use server-side pagination to control the amount of data included, this still loads a page of admins and all departments for each. If there are other related entities this can balloon out the amount of data loaded fairly quickly. EF can end up generating things like Cartesian products between related tables which it will chew through to produce the entity graph, but this will still take up memory and time.

With projection we could create something like:

[Serializable]
public class AdminSummaryViewModel
{
    public int AdminId { get; set; }
    public string Name { get; set; }
    // Additional details we want to show....

    public int DepartmentCount { get; set; }
}

Then when we go to get the data:

var admins = await context.Admins
    .Where(x => *some condition*)
    .Select(x => new AdminSummaryViewModel 
    {
        AdminId = x.AdminId,
        Name = x.Name,
        // ...
        DepartmentCount = x.Departments.Count()
    })
    .Skip(pageNumber * pageSize)
    .Take(pageSize)
    .ToListAsync();

This generates an SQL statement that will only query the details we need from the relevant tables, including condensing down the requested department count. This reduces the total amount of data passed over the wire and time needed for EF to produce the view models desired. Automapper has a ProjectTo extension method for IQueryable which can populate a ViewModel without having to write out that .Select() block.

CodePudding user response：

admin.Departments.Count(d => !d.Deleted) is lazy loading the entire Departments collection, then calling Enumerable.Count to give the answer.

Without using any helper methods, you can get the answer this way;

public int DepartmentCount (DbContext context, Admin admin)
{
    var navigation = context
        .Entry(admin)
        .Navigation(nameof(Admin.Departments));

    // if it's already in memory, just use that
    if (navigation.IsLoaded)
        return admin.Departments.Count(d => !d.Deleted);

    // otherwise query the database

    // EF Core can generate the query for you, equivalent to supplying FK parameters;
    // context.Departments.Where(d => d.AdminId == admin.Id)
    var query = (IQueryable<Department>)navigation.Query();

    return query.Count(d => !d.Deleted);
}