How do I use LINQ with GROUP BY and multiple MAX(...) to select multiple fields?

Question

I need to select fields from unique records within a table with multiple where clauses. I currently am using C# and LINQ fluent syntax connected with NHibernate. So I am wondering if there is a way to create this query that way. Here is a test dataset:

+----+----------+------+------+
| Id | ParentId | Name | Type |
+----+----------+------+------+
|  1 |      100 | A    |    1 |
|  2 |      100 | A    |    2 |
|  3 |      100 | A    |    3 |
|  4 |      200 | B    |    1 |
|  5 |      300 | A    |    1 |
|  6 |      300 | A    |    2 |
|  7 |      400 | A    |    1 |
|  8 |      400 | A    |    2 |
|  9 |      400 | A    |    3 |
| 10 |      400 | A    |    4 |
+----+----------+------+------+

I can get the results I want using this SQL query:

SELECT ParentId, COUNT(Name) as Cnt, Max(Id) as  Id, Max(Name) as Name, Max(Type) as Type FROM TestGroupBy Where Name = 'A' Group By ParentId;

This gives the result:

+----------+-----+----+------+------+
| ParentId | Cnt | Id | Name | Type |
+----------+-----+----+------+------+
|      100 |   3 |  3 | A    |    3 |
|      300 |   2 |  6 | A    |    2 |
|      400 |   4 | 10 | A    |    4 |
+----------+-----+----+------+------+

I know how to make the group by query but I can't figure out how to do the multiple MAX selects. Is that just not possible with LINQ? If it's not, then what would be a way that I could go about this?

Answer 1

When you have a queryable, you can call Select and pass a predicate. The new keyword constructs an object with the schema you prefer.

.Select( x => new
{
    ParentId = x.ParentId
    Cnt = x.Count(p => p.Name),
    Id = x.Max( p => p.Id  )
    /*etcetera*/
} );

Answer 2

Here's a small snippet that shows you the linq query in context:

public class Row
{
    public int Id;
    public int ParentId;
    public string Name;
    public int Type;

    public Row(int Id, int ParentId, string Name, int Type)
    {
        this.Id = Id;
        this.ParentId = ParentId;
        this.Name = Name;
        this.Type = Type;
    }
}

class Program
{
    static void Main(string[] args)
    {
        List<Row> test = new List<Row>();
        test.Add(new Row(1, 100, "A", 1));
        test.Add(new Row(2, 100, "A", 2));
        test.Add(new Row(3, 100, "A", 3));
        test.Add(new Row(4, 200, "B", 1));
        test.Add(new Row(5, 300, "A", 1));
        test.Add(new Row(6, 300, "A", 2));
        test.Add(new Row(7, 400, "A", 1));
        test.Add(new Row(8, 400, "A", 2));
        test.Add(new Row(9, 400, "A", 3));
        test.Add(new Row(10, 400, "A", 4));

        dynamic d = from row in test
                    where row.Name.Equals("A")
                    group row by row.ParentId into grp 
                    select new { 
                        ParentId = grp.Key, 
                        Cnt = grp.Count(), 
                        Id = grp.Max(x => x.Id), 
                        Name = grp.Max(x => x.Name), 
                        Type = grp.Max(x => x.Type) 
                    };
    }
}