Grouping Similar columns and concatenate other columns in LINQ

Question

I have the following class

public class InvoiceRO 
{
    public int ID{ get; set; }

    public string Address { get; set; }

    public string Reference1 { get; set; }

    public string DNNumber { get; set; }

    public string QuotationNumber { get; set; }

}

Here i have the data as follows

ID   Address    Reference1  DNNumber   QuotationNumber
----------------------------------------------------------
1    add1           ref1      d001      q001
2    add1           ref1      d001      q002
3    add1           ref1      d002      q003

I need only one row as output, so first 2 column values will be equal and get only one and the last 2 columns will concatenate and retrieve as comma separated value. The final output will be

ID   Address    Reference1  DNNumber        QuotationNumber
----------------------------------------------------------
1    add1           ref1      d001, d002      q001, q002, q003

How to group like this in LINQ?

Answer 1

You can use GroupBy and then concatenate the strings with String.Join:

invoices.GroupBy(i => new {i.Address, i.Reference1})
        .Select(g => new InvoiceRO {
                        ID              = g.First().ID, 
                        Address         = g.Key.Address, 
                        Reference1      = g.Key.Reference1,
                        DNNumber        = string.Join(", ", g.Select(i => i.DNNumber)),
                        QuotationNumber = string.Join(", ", g.Select(i => i.QuotationNumber))
                        }