LINQ: How do I select unique rows: max(id) with multiple columns?

Question

This is a "toy" example of a table that has many columns and 100s of thousands of rows.

I want FILTER OUT any rows containing the same AcctNo, CustomerName and CustomerContact, but KEEP the ID for ONE of the duplicates (so I can access the record later).

• Example:

  ID  AcctNo  CustomerName  CustomerContact
  1   1111    Acme Foods    John Smith
  2   1111    Acme Foods    John Smith
  3   1111    Acme Foods    Judy Lawson
  4   2222    YoyoDyne Inc  Thomas Pynchon
  5   2222    YoyoDyne Inc  Thomas Pynchon
  <= For AcctNo 1111, I want to save IDs 2 and 3
  

• Fiddle: https://www.db-fiddle.com/f/bEECHi6XnvKAeXC4Xthrrr/1

• Working SQL:

  select max(id) as ID,AcctNo,CustomerName,CustomerContact
  from test
  where AcctNo = '11111'
  group by AcctNo,CustomerName,CustomerContact
  

  OK: returns IDs 2 and 3:

  ID AcctNo CustomerName CustomerContact
  -- ------ ------------ ---------------
  2  11111  Acme Foods   John Smith
  3  11111  Acme Foods   Judy Lawson
  

Q: What's a LINQ equivalent for this SQL?

• Failed attempt:

  IQueryable<CustomerData> query =
      from c in context.CustomerData
      where c.AcctNo == acctno
      group c by new { c.AcctNo , c.CustomerName, c.CustomerContact } into gcs
      select new { newID = gcs.Max(x => x.ID), gcs.AcctNo, gcs.CustomerName, gcs.CustomerContact }
  

Answer 1

You almost got it right. Just access the group properties using Key in your projection.

IQueryable<CustomerData> query =
    from c in context.CustomerData
    where c.AcctNo == acctno
    group c by new { c.AcctNo , c.CustomerName, c.CustomerContact } into gcs
    select new { newID = gcs.Max(x => x.ID), gcs.Key.AcctNo, gcs.Key.CustomerName, gcs.Key.CustomerContact }

Answer 2

First create a custom DTO view model similar to this

public CustomerViewModel
{
   public string AcctNo { get; set; }
   public string CustomerName { get; set; }
   public string CustomerContact { get; set; }
   public int MaxId { get; set; }
}       

The query

context.CustomerData.Where(c => c.AcctNo == "1111")
       .GroupBy(c => new { c.AcctNo , c.CustomerName, c.CustomerContact })
       .Select(cg => new CustomerViewModel {
                      AcctNo  = cg.Key.AcctNo,
                      CustomerName  = cg.Key.CustomerName,
                      CustomerContact  = cg.Key.CustomerContact,
                      MaxId = cg.Max(c => c.ID)
        })