Linq to SQL: order by value in a linked table

Question

I have two tables that in a 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 the products (in linq to sql) by the value of some element of the specification (for example, "price"). So i'm doing 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 such a ProductSpec with spec_name is "price", the product is not included at all. I can add these products to Union or Concat, but this way the sorting of the first part is not saved.

What is the best way to handle this?

Thanks.

Answer 1

Firstly, I would recommend that you either do it in pure SQL as a function or a stored procedure, and then access it via linq or add a price column to the product table. It looks like the price will be a normal attribute to add to all 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 

Having said that, here is the weird LINQ bit that should work on your table (I may have invalid column names):

 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, as described above, and created 5 products, three with prices in different order of values, and this LINQ ordered them the same as SQL above, and also returned zero rows of results.

Hope it works!

Answer 2

In regular SQL, you use the LEFT OUTER JOIN. This saves the rows that appear in the left table (the one indicated first), even if the right table does not have a corresponding row (the second one is indicated, and the other is external). You get null values ​​for values ​​that should be, but are not present in the right table. Thus, the price for those items that have no price will look like NULL.

What translates to LINQ to SQL is different.

You might be wondering if it makes sense to have products that don't have a price. You emulate something called EAV - Entity, Attribute, Value - tables, and they are usually considered "not very good."

Answer 3

Can't you just make a simple connection?

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