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

user_3135317 0
Viewed 68

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 }
group-by linq c#
2 Answers

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)
        })
user_91223540

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 }
user_8817980
Related