EF 4.1：为什么把一个常量到一个变量导致额外的子查询？ [英] EF 4.1: Why does turning a constant into a variable result in extra sub query?
问题描述
今天，我发现，实体框架是添加不必要的子查询，它生成的SQL。我开始挖我的代码试图缩小它可能来自。 A（长），而后来我PIN-指出是什么导致了它。但是现在我比当我开始更加困惑，因为我不知道为什么它会导致它。
Today I discovered that Entity Framework was adding an unnecessary sub query to the SQL it generates. I started digging my code trying to narrow down where it might come from. A (long) while later I pin-pointed what's causing it. But now I'm more confused than when I started, as I have no clue why it causes it.
基本上我发现的是，在某些情况下，只需一个转换恒成变量可以改变的实体框架生成SQL。我一直都缩水到最低限度，并在一个小控制台应用程序包装它：
Basically what I discovered is that on certain scenarios, simply converting a constant into a variable can alter the SQL that Entity Framework generates. I've shrunk everything to the bare minimum and packed it in a little console app:
using System;
using System.Data.Entity;
using System.Linq;
class Program
{
private static readonly BlogContext _db = new BlogContext();
static void Main(string[] args)
{
const string email = "foo@bar.com";
var comments = from c in _db.Comments
where c.Email == email
select c;
var result = (from p in _db.Posts
join c in comments on p.PostId equals c.PostId
orderby p.Title
select new { p.Title, c.Content });
Console.WriteLine(result);
}
}
public class BlogContext : DbContext
{
public DbSet<Post> Posts { get; set; }
public DbSet<Comment> Comments { get; set; }
}
public class Post
{
public int PostId { get; set; }
public string Title { get; set; }
}
public class Comment
{
public int CommentId { get; set; }
public int PostId { get; set; }
public string Email { get; set; }
public string Content { get; set; }
}
这显示了下面的输出，这是完美的：
This shows the following output, which is perfect:
SELECT
[Extent1].[PostId] AS [PostId],
[Extent1].[Title] AS [Title],
[Extent2].[Content] AS [Content]
FROM [dbo].[Posts] AS [Extent1]
INNER JOIN [dbo].[Comments] AS [Extent2] ON [Extent1].[PostId] = [Extent2].[PostId]
WHERE N'foo@bar.com' = [Extent2].[Email]
ORDER BY [Extent1].[Title] ASC
现在，如果我做电子邮件变量
/*const*/ string email = "foo@bar.com";
输出的变化从根本上：
SELECT
[Project1].[PostId] AS [PostId],
[Project1].[Title] AS [Title],
[Project1].[Content] AS [Content]
FROM ( SELECT
[Extent1].[PostId] AS [PostId],
[Extent1].[Title] AS [Title],
[Extent2].[Content] AS [Content]
FROM [dbo].[Posts] AS [Extent1]
INNER JOIN [dbo].[Comments] AS [Extent2] ON [Extent1].[PostId] = [Extent2].[PostId]
WHERE [Extent2].[Email] = @p__linq__0
) AS [Project1]
ORDER BY [Project1].[Title] ASC
作为一个方面说明，LINQ to SQL的似乎并没有做到这一点。我知道这可能是没关系忽略这一点，因为这两个命令返回相同的数据。但我，为什么出现这种情况非常好奇。直到今天，我总是有（也许是假的？）印象中，它始终是安全的把一个常数到一个变量，提供该值保持不变（在这种情况下一样）。所以，我要问...
As a side note, LINQ to SQL does not seem to do this. I know it's probably okay to ignore this, as both commands return the same data. But I'm extremely curious as to why this happens. Up until today I always had the (perhaps false?) impression that it is always safe to turn a constant into a variable, providing that the value remains the same (which in this case does). So I have to ask...
为什么一个看似不起眼的变化导致生成的SQL如此大的差别？
更新：
只是要清楚，我的问题不是关于<$的价值C $ C>电子邮件是在第一个查询和硬编码值在第二个变量（这使得所有的感官世界）。我的问题是关于为什么变量版本导致额外的子查询。
Just to be clear, my question isn't about the value of email being a hard-coded value in the first query and a variable in the second (which makes all the sense in the world). My question is about why the variable version results in the extra sub query.
谢谢！
推荐答案
这实际上是在SQL大的差别？内部查询的相同的原始查询，外部查询是刚刚超过不改变结果集内一个包装
Is this actually a big difference in the SQL? The inner query is the same as the original query, and the outer query is just a wrapper over the inner that doesn't change the result set.
除非这是造成的问题，我个人并不担心。请查询计划查询的两个版本之间有什么不同？我的猜测是，他们是一致的。
Unless this is causing problems, I personally wouldn't worry about it. Do the query plans differ between the two flavours of query? My guess is that they're identical.
这篇关于EF 4.1：为什么把一个常量到一个变量导致额外的子查询？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
