Linq to SQL: order by value in related table

Question

I have 2 tables which in simplified form look like this:

Products(
   id: int,
   name: varchar
);

ProductSpecs(
   product_id: int,
   spec_name: varchar,
   spec_value: int
);

Now I need to sort products (in linq to sql) by value of some specification item (eg. "price"). So I do something like this

var products = from p in db.Products
               from ps in p.ProductsSpecs
               where ps.spec_name == "price"
               orderby ps.spec_value
               select p;

The problem is that if there's no such ProductSpec with spec_name "price" the product is not included at all. I can add these products with Union or Concat but this way the sorting of the first part is not preserved.

What is the best way to deal with this?

Thanks.

Answer 1

In ordinary SQL, you'd use an LEFT OUTER JOIN. This preserves rows that appear in the left-hand table (the one listed first), even when there's no matching row in the right-hand table (the second one listed, and the one that is outer joined). You end up with nulls for the values that should be, but weren't, present in the right-hand table. So, the price for those items missing a price would appear as NULL.

What that translates to in LINQ to SQL is another matter.

You might care to think about whether it is reasonable to have products that do not have a price. You're emulating something called EAV - Entity, Attribute, Value - tables, and they are generally regarded as 'not a good thing'.

Answer 2

Can you not just do a simple join?

var products = from p in db.Products join ps in db.ProductSpecs on p.id equals ps.product_id where ps.spec_name == "price" orderby ps.spec_value select p;

Answer 3

First, I would recommend that you either do this in pure SQL as a function or Stored Procedure and then access this through linq, or add a price column to your product table. It seems like price would be a normal attribute to add to all of your products even if that price is NULL.

SQL:

select p.*
from products p
left outer join productspecs ps on
    p.id = ps.product_id
    and ps.spec_name = 'Price'
order by ps.spec_value

With that said, here's the weird bit of LINQ that should work on your table (I might have some of the column names spelled incorrectly):

var products = from p in db.Products
               join ps in (from pss in db.ProductSpecs
                           where pss.spec_name== "Price"
                           select pss
                           ) on p.id equals ps.product_id into temp
               from t in temp.DefaultIfEmpty()
               orderby t.spec_value
               select p;

I tested this on some tables setup like above and created 5 products, three with prices in different value orders and this LINQ ordered them just like the SQL above and returned the null result rows as well.

Hope this works!