Reputation: 30015

How to group by multiple columns using LINQ

How can I do group by multiple columns in LINQ?

Something similar to this in SQL:

SELECT * FROM <TableName> GROUP BY <Column1>,<Column2>

How can I convert this to LINQ:

QuantityBreakdown
(
    MaterialID int,
    ProductID int,
    Quantity float
)

INSERT INTO @QuantityBreakdown (MaterialID, ProductID, Quantity)
SELECT MaterialID, ProductID, SUM(Quantity)
FROM @Transactions
GROUP BY MaterialID, ProductID

Upvotes: 1194

Answers (15)

osexpert

Reputation: 563

Other answers are good for fixed\static columns, where you know what to group on when you code. But if you do not know which or how many columns to group by until the program is run, you could do something like:

public class GroupingKey<T> : IEquatable<GroupingKey<T>>
{
    public T[] Groups { get; init; }
    static EqualityComparer<T> equalityComparer = EqualityComparer<T>.Default;

    public GroupingKey(T[] groups)
    {
        Groups = groups;
    }

    public override int GetHashCode()
    {
        var hc = new HashCode();
        foreach (var g in Groups)
            hc.Add(g);
        return hc.ToHashCode();
    }

    public override bool Equals(object? other)
    {
        return Equals(other as GroupingKey<T>);
    }

    public bool Equals(GroupingKey<T>? other)
    {
        if (other == null)
            return false;

        if (ReferenceEquals(this, other))
            return true;

        if (Groups.Length != other.Groups.Length)
            return false;

        for (int i = 0; i < Groups.Length; i++)
        {
            if (!equalityComparer.Equals(Groups[i], other.Groups[i]))
                return false;
        }

        return true;
    }

    public override string ToString()
    {
        string[] array = new string[Groups.Length];
        for (int i = 0; i < Groups.Length; i++)
            array[i] = $"Group{i} = {Groups[i]}";

        return $"{{ {string.Join(", ", array)} }}";
    }
}

Example use:

public void GroupByAnyColumns(List<string[]> rows, List<int> groupByColumnIndexes)
{
    var grouped = rows.GroupBy(row => new GroupingKey<string>(groupByColumnIndexes.Select(colIdx => row[colIdx]).ToArray()));
}

Upvotes: -1

Mo0gles

Reputation: 10884

Procedural sample:

.GroupBy(x => new { x.Column1, x.Column2 })

Upvotes: 947

Dani

Reputation: 2036

For VB and anonymous/lambda:

query.GroupBy(Function(x) New With {Key x.Field1, Key x.Field2, Key x.FieldN })

Upvotes: -1

AlbertK

Reputation: 13167

C# 7.1 or greater using Tuples and Inferred tuple element names (currently it works only with linq to objects and it is not supported when expression trees are required e.g. someIQueryable.GroupBy(...). Github issue):

// declarative query syntax
var result = 
    from x in inMemoryTable
    group x by (x.Column1, x.Column2) into g
    select (g.Key.Column1, g.Key.Column2, QuantitySum: g.Sum(x => x.Quantity));

// or method syntax
var result2 = inMemoryTable.GroupBy(x => (x.Column1, x.Column2))
    .Select(g => (g.Key.Column1, g.Key.Column2, QuantitySum: g.Sum(x => x.Quantity)));

C# 3 or greater using anonymous types:

// declarative query syntax
var result3 = 
    from x in table
    group x by new { x.Column1, x.Column2 } into g
    select new { g.Key.Column1, g.Key.Column2, QuantitySum = g.Sum(x => x.Quantity) };

// or method syntax
var result4 = table.GroupBy(x => new { x.Column1, x.Column2 })
    .Select(g => 
      new { g.Key.Column1, g.Key.Column2 , QuantitySum= g.Sum(x => x.Quantity) });

Upvotes: 44

Ogglas

Reputation: 69918

A thing to note is that you need to send in an object for Lambda expressions and can't use an instance for a class.

Example:

public class Key
{
    public string Prop1 { get; set; }

    public string Prop2 { get; set; }
}

This will compile but will generate one key per cycle.

var groupedCycles = cycles.GroupBy(x => new Key
{ 
  Prop1 = x.Column1, 
  Prop2 = x.Column2 
})

If you wan't to name the key properties and then retreive them you can do it like this instead. This will GroupBy correctly and give you the key properties.

var groupedCycles = cycles.GroupBy(x => new 
{ 
  Prop1 = x.Column1, 
  Prop2= x.Column2 
})

foreach (var groupedCycle in groupedCycles)
{
    var key = new Key();
    key.Prop1 = groupedCycle.Key.Prop1;
    key.Prop2 = groupedCycle.Key.Prop2;
}

Upvotes: 3

Chris Smith

Reputation: 688

Though this question is asking about group by class properties, if you want to group by multiple columns against a ADO object (like a DataTable), you have to assign your "new" items to variables:

EnumerableRowCollection<DataRow> ClientProfiles = CurrentProfiles.AsEnumerable()
                        .Where(x => CheckProfileTypes.Contains(x.Field<object>(ProfileTypeField).ToString()));
// do other stuff, then check for dups...
                    var Dups = ClientProfiles.AsParallel()
                        .GroupBy(x => new { InterfaceID = x.Field<object>(InterfaceField).ToString(), ProfileType = x.Field<object>(ProfileTypeField).ToString() })
                        .Where(z => z.Count() > 1)
                        .Select(z => z);

Upvotes: 10

Let's Enkindle

Reputation: 53

.GroupBy(x => (x.MaterialID, x.ProductID))

Upvotes: 0

user2897701

Reputation:

Since C# 7 you can also use value tuples:

group x by (x.Column1, x.Column2)

.GroupBy(x => (x.Column1, x.Column2))

Upvotes: 52

John

Reputation: 458

group x by new { x.Col, x.Col}

Upvotes: 0

Kai Hartmann

Reputation: 3154

.GroupBy(x => x.Column1 + " " + x.Column2)

Upvotes: 1

Arindam

Reputation: 176

var Results= query.GroupBy(f => new { /* add members here */  });

Upvotes: 2

Milan

Reputation: 3013

For Group By Multiple Columns, Try this instead...

GroupBy(x=> new { x.Column1, x.Column2 }, (key, group) => new 
{ 
  Key1 = key.Column1,
  Key2 = key.Column2,
  Result = group.ToList() 
});

Same way you can add Column3, Column4 etc.

Upvotes: 205

Jay Bienvenu

Reputation: 3293

You can also use a Tuple<> for a strongly-typed grouping.

from grouping in list.GroupBy(x => new Tuple<string,string,string>(x.Person.LastName,x.Person.FirstName,x.Person.MiddleName))
select new SummaryItem
{
    LastName = grouping.Key.Item1,
    FirstName = grouping.Key.Item2,
    MiddleName = grouping.Key.Item3,
    DayCount = grouping.Count(), 
    AmountBilled = grouping.Sum(x => x.Rate),
}

Upvotes: 23

Sreedhar

Reputation: 30015

Ok got this as:

var query = (from t in Transactions
             group t by new {t.MaterialID, t.ProductID}
             into grp
                    select new
                    {
                        grp.Key.MaterialID,
                        grp.Key.ProductID,
                        Quantity = grp.Sum(t => t.Quantity)
                    }).ToList();

Upvotes: 539

leppie

Reputation: 117220

Use an anonymous type.

group x by new { x.Column1, x.Column2 }

Upvotes: 1401

How to group by multiple columns using LINQ

Answers (15)

Related Questions