CODEWITHSUNDEEP
c#linqlinq-to-sql
Legion
Legion

Reputation: 3427

How do I translate a SELECT MAX statement from SQL to LINQ to SQL?

I'm trying to translate this SQL statement into LINQ to SQL.

SELECT [PurpCode],
MAX(([PurpCode] + ' - ' + [Purpose])) AS [PurposeName]
FROM [dbo].[CCM]
WHERE [PurpCode] IS NOT NULL AND [PurpCode] <> ''
GROUP BY [PurpCode]
ORDER BY [PurpCode]

I've got this far:

    db.CCM
      .Where(ccm.PurpCode != null && ccm.PurpCode != "")
      .Select(ccm => new { ccm.PurpCode, PurposeName = ccm.PurpCode + " - " + ccm.Purpose })
      .Max(ccm => ccm.PurposeName)
      .GroupBy(ccm => ccm.) //Properties on ccm disappear after .Max
      .OrderBy(ccm => ccm.)

Max doesn't seem to work the same way in LINQ as it does in SQL. I'm using it in SQL Server as a quasi DISTINCT that only operates on the PurposeName column. Any attempt to do anything after .Max doesn't work since all the properties are lost on ccm.

Upvotes: 0

Views: 81

Answers (1)

Cam Bruce
Cam Bruce

Reputation: 5689

Use OrderBy to order each group's elements, then take the first one using FirstOrDefault. 

db.CCM
    .Where(ccm.PurpCode != null && ccm.PurpCode != "")
    .Select(ccm => new { ccm.PurpCode, PurposeName = ccm.PurpCode + " - " + ccm.Purpose })
    .GroupBy(ccm => ccm.PurpCode)
    .Select(grp=> new {  
          grp.Key, 
          PurposeName = grp.OrderBy(x=> 
              x.PurposeName).FirstOrDefault() });

Upvotes: 2

Related Questions