LINQ : two separate queries in a single distinct result

Question

It's been a while since I've worked with LINQ and I've been wracking my brain to solve something that I expected to be simpler...

I've got a db with schema (simplified) above, and want to combine the results of two queries, so I would get something like the following result using LINQ (to Entities):

SUV     
  Gas   
  Diesel    
  Hybrid    
  Hydrogen  
  Biofuel 
  1 
  2

Semi
  Gas
  Diesel
  1
  3
  4

So, a list of Categories (identified by CatTitles) with their distinct Types in a specified order, and their distinct BrandIDs.

I can't use navigation properties, because the link tables contain ordering.

I've got the following two queries:

var typesPerCat = (from cats in CarCategories
                join ctts in CategoryTypes on cats.CatID equals ctts.CatID
                join tps in Types on ctts.TypeID equals tps.TypeID
                select new
                {
                  cats.CatTitle,
                  tps.TypeName
                }).GroupBy(k=>k.CatTitle)

var brandIDsPerCat = (from cats in CarCategories
                join ctts in CategoryTypes on cats.CatID equals ctts.CatID
                join tps in Types on ctts.TypeID equals tps.TypeID
                join tpbs in TypeBrands on tps.TypeID equals tpbs.TypeID
                select new
                {
                  cats.CatTitle,
                  tpbs.BrandID
                }).GroupBy(k=> new {k.CatTitle, k.BrandID}) 

This last query leads to double entries: e.g. 1,2,2 and 1,3,3,3,4,4 (there is more than one BrandID per Type and Type per Category) and I can't figure out how to group correctly without a comparer.

I'd like to get the aforementioned result without having to resort to (very) ugly iterations with comparisons in code. At least preventing checks for double BrandIDs.

What would be the most efficient way of doing this? Thanks for helping out!

Answer 1

I would group first, then apply a distinct in the grouped values.

var brandIDsPerCat = (from cats in CarCategories
                join ctts in CategoryTypes on cats.CatID equals ctts.CatID
                join tps in Types on ctts.TypeID equals tps.TypeID
                join tpbs in TypeBrands on tps.TypeID equals tpbs.TypeID
                group new {cats, tpbs} by cats.CatTitle into g
                select new
                {
                  catTitle = g.Key
                  brandId = g.Select(x => x.tpbs.BrandId)
                             .Distinct()
                             .OrderBy(x => x)//if you need ordering in groups
                }