为什么会出现这种额外的加入增加＃查询？ [英] Why does this additional join increase # of queries?

问题描述

我有想出一个有效的LINQ到SQL查询的麻烦。我试图做这样的事情：

I'm having trouble coming up with an efficient LINQ-to-SQL query. I am attempting to do something like this:

from x in Items
select new
{
    Name = x.Name
    TypeARelated = from r in x.Related
                   where r.Type == "A"
                   select r
}

正如你所期望的，它产生的单个查询从项目表，其中的左连接上的相关表中。现在，如果我添加其他一些类似的行...

As you might expect, it produces a single query from the "Items" table, with a left join on the "Related" table. Now if I add another few similar lines...

from x in Items
select new
{
    Name = x.Name
    TypeARelated = from r in x.Related
                   where r.Type == "A"
                   select r,
    TypeBRelated = from r in x.Related
                   where r.Type == "B"
                   select r
}

其结果是，类似的查询的首次尝试运行，接着是单独的查询到的相关表的每条记录中的文件。有没有一种方法来包装这一切都在一个单一的查询？什么是这个原因？在此先感谢您的帮助，您可以提供。

The result is that a similar query to the first attempt is run, followed by an individual query to the "Related" table for each record in "Items". Is there a way to wrap this all up in a single query? What would be the cause of this? Thanks in advance for any help you can provide.

推荐答案

如果直接在SQL编写上面的查询可以写成像这样（伪code）：

The above query if written directly in SQL would be written like so (pseudo-code):

SELECT 
    X.NAME AS NAME,
    (CASE R.TYPE WHEN A THEN R ELSE NULL) AS TypeARelated,
    (CASE R.TYPE WHEN B THEN R ELSE NULL) AS TypeBRelated
FROM Items AS X
JOIN Related AS R ON <some field>

不过，LINQ到SQL是效率不高，从你的解释，它确实一个连接，然后去逐个比较每个记录。一种更好的方式是使用两个LINQ查询类似的第一实例，这将产生两个SQL查询。然后使用两个LINQ查询的结果，并加入他们的行列，这不会产生任何的SQL语句。这种方法将限制在SQL中执行的查询的数量为2。

However, linq-to-sql is not as efficient, from your explanation, it does one join, then goes to individually compare each record. A better way would be to use two linq queries similar to your first example, which would generate two SQL queries. Then use the result of the two linq queries and join them, which would not generate any SQL statement. This method would limit the number of queries executed in SQL to 2.

如果的A等，都将随时间增加，或不同的条件将要加入的条件ierType ==，你最好使用存储过程，这将是一个SQL的查询号码在任何时候。

If the number of conditions i.e. r.Type == "A" etc., are going to increase over time, or different conditions are going to be added, you're better off using a stored procedure, which would be one SQL query at all times.

Hasanain

这篇关于为什么会出现这种额外的加入增加＃查询？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！