CODEWITHSUNDEEP
c#linq
Tys
Tys

Reputation: 3610

Linq, join, group, count > select more values

With LINQ i'm doing the following to select my suppliers and their product count:

from s in context.Suppliers 
join p in context.Products on s equals p.Supplier 
group s by s.CompanyName 
into result 
select new {   
    SupplierName = result.Key,   
    ProductCount = result.Count() 
}

this works just fine, but i want to select some more properties from my supplier table, SupplierId and SupplierAddress, like: 

....    
select new {   
    SupplierName = result.Key,   
    ProductCount = result.Count(),   
    SupplierId = ..,   
    SupplierAddress = ..,  
    }

Does anyone know how to do this?

Thanks for helping!

Upvotes: 0

Views: 4336

Answers (2)

spender
spender

Reputation: 120450

EDIT

Um... Unless I'm mistaken, this can be done considerably more cleanly:

context
    .Products
    .GroupBy(p=>p.Supplier)
    .Select(result=>new {
        SupplierName = result.Key,   
        ProductCount = result.Count(),
        SupplierId = result.Key.Id,   
        SupplierAddress = result.Key.Address,  
    }

The joins come out of the box from the FK relationships in the DB, so a Product already has a Supplier. It seems you spotted this setup in your own code ( ...equals p.Supplier ), then failed to understand its meaning. Apologies for changing from comprehension syntax to method chains. They come more naturally to me.

Supplemental to @Dan's comment (which is likely correct for Linq2Objects), in Linq2Sql (I can't vouch for L2E, but I imagine it's much the same), if you group by a property that is generated by a FK relationship, the resulting generated SQL will GROUP BY the key value, not the entire entity.

Upvotes: 1

Dan Abramov
Dan Abramov

Reputation: 268255

So you're sure all Supplier groups with the same CompanyName are guranteed to have the same Id and Address?

from s in context.Suppliers 
join p in context.Products on s equals p.Supplier 
group s by s.CompanyName 
into result 
select new {   
    SupplierName = result.Key,   
    ProductCount = result.Count(),
    SupplierId = result.First().Id,
    SuppliedAddress = result.First().Address 
}

It would look more natural if you grouped by Id instead, or maybe all of them:

from s in context.Suppliers 
join p in context.Products on s equals p.Supplier 
group s by new { s.CompanyName, s.Id, s.Address } 
into result 
select new {   
    ProductCount = result.Count(),
    SupplierName = result.Key.CompanyName,   
    SupplierId = result.Key.Id,
    SuppliedAddress = result.Key.Address 
}

Upvotes: 3

Related Questions