EF Core 2.1, linq query not producing group by sql query and I can see just Order By at last

Question

I have a linq query which has Group By clause, but the Group By is not happening on sql server. I tried a simple query and the Group By is happening on sql server. Please guide me why this different behavior?? I want that group-by on server for performance improvement.

Simple query where I get group-by if I log the sql query:

var testt = (from doc in _patientRepository.Documents
                     group doc by doc.DocumentType into G
                     select new
                     {
                         Key = G.Key

                     }).ToList();

Generated sql:

Executed DbCommand (247ms) [Parameters=[], CommandType='Text', 
CommandTimeout='30']
SELECT [doc].[DocumentType] AS [Key]
FROM [Document] AS [doc]
GROUP BY [doc].[DocumentType]

Issue query:

var patX = (from doc in _patientRepository.Documents
                                               join pat in _patientRepository.Patients
                                               on doc.PatientId.ToString().ToLower() equals pat.PatientId.ToString().ToLower()
                                               where doc.Source.ToLower() != "testclient.server.postman" &&
                                               pat.Deleted == false && sfHCPs.Contains(pat.HcpId.ToLower())
                                               select new Document()
                                               {
                                                   DocumentId = doc.DocumentId,
                                                   CreationDateTime = doc.CreationDateTime,
                                                   DocumentType = doc.DocumentType,
                                                   PatientId = doc.PatientId,
                                                   DocumentTypeVersion = doc.DocumentTypeVersion,
                                                   Source = doc.Source,
                                                   PayloadLeft = DocumentMapper.DeserializePayload(doc.PayloadLeft),
                                                   PayloadRight = DocumentMapper.DeserializePayload(doc.PayloadRight),
                                                   PayloadBoth = DocumentMapper.DeserializePayload(doc.PayloadBoth),
                                                   IsSalesforceSynced = doc.IsSalesforceSynced,
                                                   HcpId = pat.HcpId
                                               }).GroupBy(p => new { p.PatientId, p.DocumentType })
        .Select(g => g.OrderByDescending(p => p.CreationDateTime).FirstOrDefault())
        .Where(x => x.IsSalesforceSynced == false)
        .ToList();

Why don't it have group-by sql generated:

Microsoft.EntityFrameworkCore.Database.Command[20101]
Executed DbCommand (200ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
SELECT [doc].[DocumentId], [doc].[CreationDateTime], [doc].[DocumentType], [doc].[PatientId], [doc].[DocumentTypeVersion], [doc].[Source], [doc].[PayloadLeft], [doc].[PayloadRight], [doc].[PayloadBoth], [doc].[IsSalesforceSynced], [pat].[HcpId]
FROM [Document] AS [doc]
INNER JOIN [Patient] AS [pat] ON LOWER(CONVERT(VARCHAR(36), [doc].[PatientId])) = LOWER(CONVERT(VARCHAR(36), [pat].[PatientId]))
WHERE ((LOWER([doc].[Source]) <> N'testclient.server.postman') AND ([pat].[Deleted] = 0)) AND LOWER([pat].[HcpId]) IN (N'4e7103a9-7dff-4fa5-b540-a32a31be2997', N'abc1', N'def2', N'ghi3')
ORDER BY [doc].[PatientId], [doc].[DocumentType]

I tried below approach but same sql generated:

    var patX = ((from doc in _patientRepository.Documents
                                                       join pat in _patientRepository.Patients
                                                       on doc.PatientId.ToString().ToLower() 
                                                       equals pat.PatientId.ToString().ToLower()
                                                       where doc.Source.ToLower() != "testclient.server.postman" &&
                                                       pat.Deleted == false && sfHCPs.Contains(pat.HcpId.ToLower())
                                                       select new Document()
                                                       {
                                                           DocumentId = doc.DocumentId,
                                                           CreationDateTime = doc.CreationDateTime,
                                                           DocumentType = doc.DocumentType,
                                                           PatientId = doc.PatientId,
                                                           DocumentTypeVersion = doc.DocumentTypeVersion,
                                                           Source = doc.Source,
                                                           PayloadLeft = DocumentMapper.DeserializePayload(doc.PayloadLeft),
                                                           PayloadRight = DocumentMapper.DeserializePayload(doc.PayloadRight),
                                                           PayloadBoth = DocumentMapper.DeserializePayload(doc.PayloadBoth),
                                                           IsSalesforceSynced = doc.IsSalesforceSynced,
                                                           HcpId = pat.HcpId
                                                       }).GroupBy(p => new { p.PatientId, p.DocumentType })
                .Select(g => g.OrderByDescending(p => p.CreationDateTime).FirstOrDefault())
                .Where(x => x.IsSalesforceSynced == false))
                .ToList();

Answer 1

Before version 2.1, in EF Core the GroupBy LINQ operator would always be evaluated in memory.Now support translating it to the SQL GROUP BY clause in most common cases.

change code : try to place .GroupBy before .select method(first select)

Answer 2

Consider re-ordering the query so the select to the new class is last:

var p1 = from doc in _patientRepository.Documents
         join pat in _patientRepository.Patients on doc.PatientId.ToString().ToLower() equals pat.PatientId.ToString().ToLower()
         where doc.Source.ToLower() != "testclient.server.postman" && pat.Deleted == false && sfHCPs.Contains(pat.HcpId.ToLower())
         group new { doc, pat.HcpId } by new { doc.PatientId, doc.DocumentType } into dpg
         select dpg.OrderByDescending(dp => dp.doc.CreationDateTime).FirstOrDefault();

var patX = (from dp in p1
            where !dp.doc.IsSalesforceSynced
            select new Document() {
                DocumentId = dp.doc.DocumentId,
                CreationDateTime = dp.doc.CreationDateTime,
                DocumentType = dp.doc.DocumentType,
                PatientId = dp.doc.PatientId,
                DocumentTypeVersion = dp.doc.DocumentTypeVersion,
                Source = dp.doc.Source,
                PayloadLeft = DocumentMapper.DeserializePayload(dp.doc.PayloadLeft),
                PayloadRight = DocumentMapper.DeserializePayload(dp.doc.PayloadRight),
                PayloadBoth = DocumentMapper.DeserializePayload(dp.doc.PayloadBoth),
                IsSalesforceSynced = dp.doc.IsSalesforceSynced,
                HcpId = dp.HcpId
            })
            .ToList();