0

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.

Legion
  • 3,922
  • 8
  • 51
  • 95
  • @PaulF `.GroupBy` has a similar but not quite the same effect as `.Max` – Legion Jul 19 '18 at 15:54
  • Perhaps my [SQL to LINQ Recipe](https://stackoverflow.com/questions/49245160/sql-to-linq-with-multiple-join-count-and-left-join/49245786#49245786) would help you? – NetMage Jul 19 '18 at 16:01

1 Answers1

2

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() });
Cam Bruce
  • 5,632
  • 19
  • 34
  • This works, but the `PurposeName = grp.Orderby` section throws me a little. If I think of it as assigning the `FirstOrDefault()` value into `PurposeName` it makes sense, but having `grp.OrderBy` in the middle is a bit confusing. – Legion Jul 19 '18 at 16:16
  • `OrderBy` is used to sort each group's collection, then `FirstOrDefault` takes the first element of that sorted collection, effectively mimicking a Max function. If you wanted to do Min, you would use OrderByDescending` – Cam Bruce Jul 19 '18 at 16:32