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

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 }
    


Read more here: https://stackoverflow.com/questions/67010539/linq-how-do-i-select-unique-rows-maxid-with-multiple-columns

Content Attribution

This content was originally published by FoggyDay at Recent Questions - Stack Overflow, and is syndicated here via their RSS feed. You can read the original post over there.

%d bloggers like this: