Linq select distinct count performed in memory

Question

I'm working on understanding how LINQ converts to SQL.

I have the following query I'm trying to generate using LINQ.

SELECT [OrganizationId]
  ,[DepartmentId]
  ,[LocationName]
  ,[Group1]
  ,[Group2]
  ,[Group3]
  ,[BooklistId]
  ,[BooklistName]
  ,COUNT(DISTINCT [OrdererId])
  ,MAX([ExpectedDate])
FROM [Orders]
WHERE ([DepartmentId] IS NOT NULL AND ([DepartmentId] = '<Guid>')) AND ([Schoolyear] = '2018')
GROUP BY [OrganizationId]
  ,[DepartmentId]
  ,[LocationName]
  ,[Group1]
  ,[Group2]
  ,[Group3]
  ,[BooklistId]
  ,[BooklistName]
ORDER BY [BooklistName]

With indexes this query performs under 200ms.

My LINQ query is the following:

await _context
            .Orders
            .Where(i => i.DepartmentId != null && i.DepartmentId.Equals(Parameters.DepartmentId))
            .Where(i => i.SchoolYear.Equals(Parameters.SchoolYear))
            // Group the data.
            .GroupBy(orders => new
            {
                orders.BooklistId,
                orders.BooklistName,
                orders.OrganizationId,
                orders.DepartmentId,
                orders.LocationName,
                orders.Groep1,
                orders.Groep2,
                orders.Groep3
            })
            .OrderBy(i => i.Key.BooklistName)
            .Select(i => new BookListViewModel
            {
                Count = i.Select(orders => orders.OrdererId).Distinct().Count(s => s != null),
                Id = i.Key.OrganizationId,
                Name = i.Key.BooklistName,
                LocationName = i.Key.LocationName,
                Number = i.Key.BooklistId,
                Group1 = i.Key.Group1,
                Group2 = i.Key.Group2,
                Group3 = i.Key.Group3,
                DepartmentId = i.Key.DepartmentId,
                ExpectedDate = i.Max(orders => orders.ExpectedDate)
            })
            .ToListAsync();

However I keep seeing:

Microsoft.EntityFrameworkCore.Query:Warning: The LINQ expression 'GroupBy(new <>f__AnonymousType1`8(BooklistId = [i].BooklistId, BooklistName = [i].BooklistName, OrganizationId = [i].OrganizationId, DepartmentId = [i].DepartmentId, LocationName = [i].LocationName, Group1 = [i].Group1, Group2 = [i].Group2, Group3 = [i].Group3), [i])' could not be translated and will be evaluated locally.

Microsoft.EntityFrameworkCore.Query:Warning: The LINQ expression 'Distinct()' could not be translated and will be evaluated locally.

Microsoft.EntityFrameworkCore.Query:Warning: The LINQ expression 'where ([s] != null)' could not be translated and will be evaluated locally.

Microsoft.EntityFrameworkCore.Query:Warning: The LINQ expression 'Count()' could not be translated and will be evaluated locally.

Can anyone tell me why the LINQ query performs in memory? What do I need to change in the LINQ query to get the result I want?

Answer 1

The query is executing in memory because you instantiating a collection of BookListViewModel objects with the statement

.Select(i => new BookListViewModel
            {...})

If you simply remove the class BookListViewModel, Linq will execute the perform the query on the db side (which is a good idea since the optimizer is much more efficient) like this...

.Select(i => new
            {
                Count = i.Select(orders => orders.OrdererId).Distinct().Count(s => s != null),
                i.Key.OrganizationId,
                i.Key.BooklistName,
                i.Key.LocationName,
                i.Key.BooklistId,
                i.Key.Group1,
                i.Key.Group2,
                i.Key.Group3,
                i.Key.DepartmentId,
                ExpectedDate = i.Max(orders => orders.ExpectedDate)
            })

Then you can instantiate your collection at the end so the whole thing will look like this...

await _context
            .Orders
            .Where(i => i.DepartmentId != null && i.DepartmentId.Equals(Parameters.DepartmentId))
            .Where(i => i.SchoolYear.Equals(Parameters.SchoolYear))
            // Group the data.
            .GroupBy(orders => new
            {
                orders.BooklistId,
                orders.BooklistName,
                orders.OrganizationId,
                orders.DepartmentId,
                orders.LocationName,
                orders.Group1,
                orders.Group2,
                orders.Group3
            })
            .OrderBy(i => i.Key.BooklistName)
.Select(i => new
            {
                Count = i.Select(orders => orders.OrdererId).Distinct().Count(s => s != null),
                i.Key.OrganizationId,
                i.Key.BooklistName,
                i.Key.LocationName,
                i.Key.BooklistId,
                i.Key.Group1,
                i.Key.Group2,
                i.Key.Group3,
                i.Key.DepartmentId,
                ExpectedDate = i.Max(orders => orders.ExpectedDate)
            })
            .Select(i => new BookListViewModel
            {
                Count = i.Count,
                Id = i.Id,
                Name = i.Name,
                LocationName = i.LocationName,
                Number = i.Number ,
                Group1 = i.Group1 ,
                Group2 = i.Group2,
                Group3 = i.Group3,
                DepartmentId = i.DepartmentId,
                ExpectedDate = i.ExpectedDate
            })
            .ToListAsync();