I'm attempting to create a compare 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?
