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:

  • 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:

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: