Linq to SQL:在相关表中按值排序 [英] Linq to SQL: order by value in related table

问题描述

我有2个表格，它们的简化形式如下:

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
);

现在，我需要按某种规格项目的值(例如价格")对产品(从linq到sql)进行排序.所以我做这样的事情

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;

问题是，如果没有这样的SpecSpec名称为"price"的ProductSpec，则根本不包括该产品.我可以将这些产品与Union或Concat一起添加，但是这样就不会保留第一部分的排序.

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?

谢谢.

推荐答案

首先，我建议您以纯SQL作为函数或存储过程来进行此操作，然后通过linq进行访问，或者向您的价格添加价格列产品表.即使价格为NULL，价格似乎仍是添加到您所有产品中的正常属性.

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

话虽如此，这是应该在您的表上使用的LINQ怪异的地方(我可能有些列名的拼写不正确):

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;

我在如上的某些表设置上对此进行了测试，并创建了5种产品，其中三种具有不同值顺序的价格，并且此LINQ像上面的SQL一样对它们进行了排序，并且还返回了空结果行.

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.

希望这行得通！

这篇关于Linq to SQL:在相关表中按值排序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！