How do you JOIN/GROUP/Max/Min all within Linq?

Question

I have the following SQL statement:

select 
   p.productId, 
   p.minAge, 
   p.maxAge, 
   min(pcb.lowerbound) MinValue,    
   max(pcb.upperBound) MaxValue, 
   p.name ProductName
from gliweb..product p 
inner join gliweb..ProductClassBand pcb on pcb.productId = p.productId
where p.IncludeInQuote = 1
      and @currentAge between p.minAge and p.maxAge
group by p.productId, p.minAge, p.maxAge, p.name
order by p.name

As you can see, it is a simple statement with a GROUP and then a MIN/MAX. I am trying to convert this query into C# LINQ and I am having a hard time.

Thus far, I have the following:

var productListDatabase = from products in DataContext.Products
    join band in DataContext.ProductClassBands on products.productId equals band.productId
    where products.minAge <= currentAge &&
            products.maxAge >= currentAge &&
            products.IncludeInQuote == true
    orderby products.name
            group products by new{
                products.productId,
                products.minAge ,
                products.maxAge,
                products.name
            } into g

    select new
    {
        g.Key.maxAge,
        g.Key.minAge,
        g.Key.productId,
        g.Key.name
        //,minFace = (from t2 in band select t2.
    };

It does everything that I need except for the MIN/MAX face columns. I am unsure how to do this because I am joining one table but need to aggregate the data from another table.

Can anyone help me finish this query as a Linq statement?

Answer 1

First off, you need to use group join

Group Join

A join clause with an into expression is called a group join.

A group join produces a hierarchical result sequence, which associates elements in the left source sequence with one or more matching elements in the right side source sequence. A group join has no equivalent in relational terms; it is essentially a sequence of object arrays.

Here is the query

var productListDatabase = from product in DataContext.Products
    join band in DataContext.ProductClassBands on product.productId equals band.productId into productBands
    where product.minAge <= currentAge &&
        product.maxAge >= currentAge &&
        product.IncludeInQuote == true
    group new { product, productBands } by new {
        product.productId,
        product.minAge ,
        product.maxAge,
        product.name
    } into g
    orderby g.Key.name
    select new
    {
        g.Key.maxAge,
        g.Key.minAge,
        g.Key.productId,
        g.Key.name,
        minFace = g.SelectMany(e => e.productBands).Min(band => band.lowerbound),
        maxFace = g.SelectMany(e => e.productBands).Max(band => band.upperBound)
    };