如何在LINQ to Entities中重写SQL查询

Question

我正试图在LINQ to Entities中重写SQL查询。我使用LINQPad和我自己程序集中的类型化数据上下文来测试。

我试图重写的SQL查询：

SELECT DISTINCT variantID AS setID, option_value AS name, option_value_description AS description, sort_order as sortOrder
FROM all_products_option_names AS lst
WHERE lst.optionID=14 AND lst.productID IN (SELECT productID FROM all_products_option_names
                                            WHERE optionID=7 AND option_value IN (SELECT name FROM brands
                                                                                  WHERE brandID=1))
ORDER BY sortOrder;

到目前为止，我提出的LINQ to Entities查询（由于超时错误而不起作用）：

from a in all_products_option_names
where a.optionID == 14 && all_products_option_names.Any(x => x.productID == a.productID && x.optionID == 7 && brands.Any(y => y.name == x.option_value && y.brandID == 1))
select new
{
    id = a.variantID,
    name = a.option_value,
    description = a.option_value_description,
    sortOrder = a.sort_order,
}

这是我运行上述查询时遇到的错误：An error occurred while executing the command definition. See the inner exception for details.

内部异常为：Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.

编辑：

我使用MySQL，这可能就是LINQPad没有向我显示生成的SQL的原因。

SQL版本没有超时。

编辑2:

我通过完全更改查询解决了这个问题，所以这个问题现在无关紧要了。

我认为史蒂文的回答是正确的，因为他最接近我想要实现的目标，他的回答给了我一个想法，让我找到了解决方案。

Answer 1

试试这个：

var brandNames =
    from brand in db.Brands
    where brand.ID == 1
    select name;
var brandProductNames =
    from p in db.all_products_option_names
    where p.optionID == 7
    where brandNames.Contains(p.option_value)
    select p.productId;
var results =
    from p in db.all_products_option_names
    where p.optionID == 14
    where brandProductNames.Contains(p.productId)
    select new
    {
        setID = p.variantID, 
        name = p.option_value, 
        description = p.option_value_description, 
        sortOrder = p.sort_order
    };

Answer 2

我建议进行联接，而不是像您现有的那样进行子选择。从性能的角度来看，子选择不是很有效，这就像在编写代码时在循环中有循环一样，这不是一个好主意。如果数据库运行缓慢，甚至看起来像一个简单的查询，这实际上可能会导致超时。

Answer 3

我会尝试使用joins和结尾的distinct，如下所示：

var results =
    (from p in db.all_products_option_names
     join p2 in db.all_products_option_names on p.productId equals p2.productId
     join b in db.Brands on p2.option_value equals b.name
     where p.optionID == 14
     where p2.optionID == 7
     where b.BrandID == 1
     select new
     {
        setID = p.variantID, 
        name = p.option_value, 
        description = p.option_value_description, 
        sortOrder = p.sort_order
     }).Distinct();

或者，您可以尝试将joins和into以及类似的any一起使用

 var results =
         from p in db.all_products_option_names
         join p2 in (from p3 in db.all_products_option_names.Where(x => x.optionId == 7) 
                     join b in db.Brands.Where(x => x.BrandID == 1) on p3.option_value equals b.name 
                     select p3) into pg
         where p.optionID == 14
         where pg.Any()
         select new
         {
            setID = p.variantID, 
            name = p.option_value, 
            description = p.option_value_description, 
            sortOrder = p.sort_order
         };