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

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!