ChatGPT解决这个技术问题 Extra ChatGPT

Group By Multiple Columns

How can I do GroupBy 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

l
leppie

Use an anonymous type.

Eg

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

If you're new to grouping with anonymous types the use of the 'new' keyword in this example does the magic.
in case of mvc with nHibernate getting error for dll issues. Problem resolved by GroupBy(x=> new { x.Column1, x.Column2 }, (key, group) => new { Key1 = key.Column1, Key2 = key.Column2 , Result = group.ToList() });
I thought in this case the new objects would by compared by reference, so no match - no groupping.
@HoGo anonymous typed objects implement their own Equals and GetHashCode methods which is used when grouping the objects.
A bit tough to visualize the output data structure when you're new to Linq. Does this create a grouping where the anonymous type is used as the key?
i
iliketocode

Procedural sample:

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

What type is the object returned?
@MGG_Soft that would be an anonymous type
@Tom this should work the way it is. When you skip naming the fields of an anonymous type C# assumes you want to use the name of the finally accessed property/field from the projection. (So your example is equivalent to Mo0gles')
@Crisfole yes I totall agree under most cases this is true. However there are times when the compiler can't infer the field names and they have to be explicitly specified. As in when you get the "Invalid anonymous type declarator" compile error. It's happened to me and also thalesfc hence the comment.
found my answer. I need to define a new entity (MyViewEntity) containing Column1 and Column2 properties and the return type is : IEnumerable> and Grouping code snip is : MyEntityList.GroupBy(myEntity => new MyViewEntity { Column1 = myEntity.Column1, Column2 = myEntity.Column2 });
s
splattne

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();

+1 - Thanks for the comprehensive example. The other answer's snippets are too short and without context. Also you show an aggregate function (Sum in this case). Very helpful. I find the use of an aggregate function (i.e., MAX, MIN, SUM, etc.) side-by-side with grouping to be a common scenario.
Here : stackoverflow.com/questions/14189537/… ,it is shown for a data table when grouping is based on a single column, whose name is known, but how can it be done if columns based on which the grouping is to be done has to be generated dynamically ?
This is really helpful in understanding the concept of grouping and applying aggregation over it.
Great example... just what i was looking for. I even needed the aggregate so this was the perfect answer even though I was looking for lambda i got enough from it to solve my needs.
having grp.Key. was what I needed to get it working, thanks!
M
Milan

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.


That was very helpful and should get a lot more upvotes! Result contains all data sets linked to all columns. Thanks a lot!
note: I had to use .AsEnumerable() instead of ToList()
Awesome, thanks for this. Here's my example. Note that GetFees returns an IQueryable RegistryAccountDA.GetFees(registryAccountId, fromDate, toDate) .GroupBy(x => new { x.AccountId, x.FeeName }, (key, group) => new { AccountId = key.AccountId, FeeName = key.FeeName, AppliedFee = group.Sum(x => x.AppliedFee) ?? 0M }).ToList();
Is it possbile to get other columns from this query, which were not grouped? If there is array of object, I would like to get this object grouped by two columns, but get all properties from the object, not just those two columns.
N
Nathan Tregillus

Since C# 7 you can also use value tuples:

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

or

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

Well I think you are missing an extra ) at the end. You are not closing the ()
I have added it.
.GroupBy(x => new { x.Column1, x.Column2})
An expression tree may not contain a tuple literal. This is why .GroupBy(x => new { x.Column1, x.Column2})
A
AlbertK

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

Wow, thank you. This has made a mark on brain, and increased capacity for possibilities.
Super ----- :) :)
J
Jay Bienvenu

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

Note: Creating a new Tuple is not supported in Linq To Entities
C
Chris Smith

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);

I couldn't do the Linq query "group c by new{c.Field("Title"),c.Field("CIF")}", and you saved me a lot of time!! the final query was: "group c by new{titulo= c.Field("Title"),cif=c.Field("CIF")} "
s
sepehr
var Results= query.GroupBy(f => new { /* add members here */  });

Adds nothing to the previous answers.
O
Ogglas

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;
}

K
Kai Hartmann
.GroupBy(x => x.Column1 + " " + x.Column2)

Combined with Linq.Enumerable.Aggregate() this even allows for grouping by a dynamic number of properties: propertyValues.Aggregate((current, next) => current + " " + next).
This is a better answer than anyone is giving credit for. It may be problematic if there could be instances of combinations where column1 added to column2 would equal the same thing for situations where column1 differs ("ab" "cde" would match "abc" "de"). That said, this is a great solution if you can't use a dynamic type because you are pre-constructing lambdas after the group by in separate expressions.
"ab" "cde" should actually not match "abc" "de", hence the blank in between.
what about "abc de" "" and "abc" "de "?
J
John

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


L
Let's Enkindle

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


Consider adding an explanation on how this code solves the problem.
D
Dani

For VB and anonymous/lambda:

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