# 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

Category Table
ID
Name

ID
Amount
ProductID

ID
Name


Example Data:

Products

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

Categories

ID      Name
1       Category1

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

ID      Name


Currently I have this:

var addons = (from s in Products
where s.Category.Name == "Category1"
select new { Name = a.Addon.Name, Amount = a.Amount }

var compare = from c in addons
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


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

Addon          Amounts

2
0

3
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?

• Do you have some sample data that would produce those results that you could share with us? Your query is very complicated, much more complicated than it probably has to be and it's hard to make sense of it. "somewhat messy" is a huge understatement. – Jeff Mercado Apr 27 '12 at 4:32
• Hi Jeff. I've added the table layouts and sample data. The table layouts are set in stone and cannot be changed. – Dan Apr 27 '12 at 13:20
• You say you're using LINQ to SQL, but there's an Entity Framework tag on the question. They're really two different technologies any may (or may not) matter in getting a proper answer. – Jesse C. Slicer Apr 29 '12 at 2:36

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 =
select new
{

• This assumes the order provided by the context. No additional ordering was made on the addons. Though that could easily be added by including orderby addon.Id. – Jeff Mercado May 4 '12 at 14:09