Comparison table for comparing products I'm attempting to create a comparison table to compare products depending on the add-ons they have. To grab the data and filter it I'm using LINQ To SQL.

Table's Layout (cut short):




Products Table  
ID
Name
CategoryID
ProductAddons

Category Table
ID
Name

ProductAddon Table
ID
Amount 
AddonID
ProductID

Addon Table
ID
Name



Example Data:


Products

ID      Name      CategoryID
1       Product1  1   
2       Product2  1 
3       Product3  1 

Categories

ID      Name
1       Category1

ProductAddons

ID      Amount      AddonID      ProductID
1       1           1            1
2       2           2            1 
3       1           3            1
4       2           1            2
5       3           2            2
6       1           2            3
7       1           3            3

Addons

ID      Name
1       Addon1
2       Addon2
3       Addon3



Currently I have this:



var addons = (from s in Products
              where s.Category.Name == "Category1"
              orderby s.ProductAddons.Count descending
              let adds = from a in s.ProductAddons
                         orderby a.Addon.Name
                         select new { Name = a.Addon.Name, Amount = a.Amount }
              select adds).ToList();

var compare = from c in addons
              let has = from z in addons.First().Union(addons.First().Except(c))
                      let add = (from a in c
                                 where a.Name == z.Name
                                 select a.Amount).FirstOrDefault()
                      select new { Name = z.Name, Amount = add }
              select has;

var compileResults = from c in addons.First()
                     let adds = from s in compare
                             let y = (from a in s
                                      where a.Name.Contains(c.Name)
                                      select a.Amount).First()
                             select y
                     select new { Addon = c.Name, Amounts = adds };


This would return (3 products, 3 Add-ons):




Addon          Amounts

Addon1         1
               2
               0

Addon2         2
               3
               1

Addon3         1
               0
               1



I can then loop through the results to build my compare table, which works fine. But to me the LINQ looks somewhat messy. Would there be a cleaner way to do this?
So it looks like what you want is some sort of outer join.  I think this should work for you:

var categoryFilter = "Category1";
var query =
    from addon in dc.Addons
    select new
    {
        Addon = addon.Name,
        Amounts =
            from product in dc.Products
            where product.Category.Name == categoryFilter
            join productAddon in dc.ProductAddons
                on new { AddonId = addon.Id, ProductId = product.Id }
                equals new { productAddon.AddonId, productAddon.ProductId }
                into pas
            from productAddon in productAddons.DefaultIfEmpty()
            select (int?)productAddon.Amount ?? 0,
    };