7
\$\begingroup\$

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?

\$\endgroup\$
3
  • 2
    \$\begingroup\$ 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. \$\endgroup\$ – Jeff Mercado Apr 27 '12 at 4:32
  • \$\begingroup\$ Hi Jeff. I've added the table layouts and sample data. The table layouts are set in stone and cannot be changed. \$\endgroup\$ – Dan Apr 27 '12 at 13:20
  • \$\begingroup\$ 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. \$\endgroup\$ – Jesse C. Slicer Apr 29 '12 at 2:36
3
\$\begingroup\$

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,
    };
\$\endgroup\$
2
  • \$\begingroup\$ Where does this order by the add-on count in a descending fashion? I've been wrestling with it myself to no end and can't get it to work properly in a single query. \$\endgroup\$ – Jesse C. Slicer Apr 29 '12 at 2:37
  • \$\begingroup\$ 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. \$\endgroup\$ – Jeff Mercado May 4 '12 at 14:09

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.