SQL Server efficient sub-total in EF Core

entity-framework entity-framework-core sql-server tsql

Question

I'm trying to achieve a query similar to this:

SELECT r.*, (SELECT COUNT(UserID) FROM RoleUsers ru WHERE ru.RoleId = r.Id) AS Assignments
FROM Roles r

To retrieve the number of the users per each role.

The simplest and the most straightforward option to implement desired output:

this.DbContext.Set<Role>().Include(x => x.RoleUser)
                .Select(x => new { x, Assignments = x.RoleUsers.Count() });

Retrieves all the roles, and then N queries to retrieve count:

SELECT COUNT(*)
FROM [dbo].[RoleUsers] AS [r0]
WHERE @_outer_Id = [r0].[RoleId]

Which is not an option at all. I tried also to use GroupJoin, but it loads all the required data set in one query and performs grouping in memory:

    this.DbContext.Set<Role>().GroupJoin(this.DbContext.Set<RoleUser>(), role => role.Id,
        roleUser => roleUser.RoleId, (role, roleUser) => new
        {
            Role = role,
            Assignments = roleUser.Count()
        });

Generated query:

SELECT [role].[Id], [role].[CustomerId], [role].[CreateDate], [role].[Description], [role].[Mask], [role].[ModifyDate], [role].[Name], [assignment].[UserId], [assignment].[CustomerId], [assignment].[RoleId]
FROM [dbo].[Roles] AS [role]
LEFT JOIN [dbo].[RoleUser] AS [assignment] ON [role].[Id] = [assignment].[RoleId]
ORDER BY [role].[Id]

Also, I was looking into a way, to use windowing functions, where I can just split count by partition and use distinct roles, but I have no idea how to wire up windowing function in EF:

SELECT DISTINCT r.*, COUNT(ra.UserID) OVER(PARTITION BY ru.RoleId)
FROM RoleUsers ru
    RIGHT JOIN Roles r ON r.Id = ru.RoleId

So, is there any way to avoid EntitySQL?

1
1
1/23/2019 5:18:37 PM

Accepted Answer

Currently there is a defect in EF Core query aggregate translation to SQL when the query projection contains a whole entity, like

.Select(role => new { Role = role, ...}

The only workaround I'm aware of is to project to new entity (at least this is supported by EF Core) like

var query = this.DbContext.Set<Role>()
    .Select(role => new
    {
        Role = new Role { Id = role.Id, Name = role.Name, /* all other Role properies */ },
        Assignments = role.RoleUsers.Count()
    });

This translates to single SQL query. The drawback is that you have to manually project all entity properties.

3
1/23/2019 6:39:05 PM

Popular Answer

this.DbContext.Set<Role>()
     .Select(x => new { x, Assignments = x.RoleUsers.Count() });

you dont need to add include for RoleUser since you are using Select statement. Furhtermore, I guess that you are using LazyLoading where this is expected behavior. If you use eager loading the result of your LINQ will run in one query.

you can use context.Configuration.LazyLoadingEnabled = false; before your LINQ query to disable lazy loading specifically for this operation



Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow