在逻辑上,这两个语句看起来相同,但它们生成的SQL不同:
#1
var people = _DB.People.Where(p => p.Status == MyPersonEnum.STUDENT.ToString());
var ids = people.Select(p => p.Id);
var cars = _DB.Cars.Where(c => ids.Contains(c.PersonId));
#2
string s = MyPersonEnum.STUDENT.ToString();
var people = _DB.People.Where(p => p.Status == s);
var ids = people.Select(p => p.Id);
var cars = _DB.Cars.Where(c => ids.Contains(c.PersonId));
示例1无法工作,但示例2可以。
对于var people
查询生成的SQL相同,但是最终查询中的SQL不同,如下所示:
#1
SELECT [t0].[PersonId], [t0].[etc].....
FROM [Cars] AS [t0]
WHERE EXISTS(
SELECT NULL AS [EMPTY]
FROM [People] AS [t1]
WHERE ([t1].[Id] = [t0].[PersonId]) AND ([t1].[Status] = (CONVERT(NVarChar,@p0)))
)
#2
SELECT [t0].[PersonId], [t0].[etc].....
FROM [Cars] AS [t0]
WHERE EXISTS(
SELECT NULL AS [EMPTY]
FROM [People] AS [t1]
WHERE ([t1].[Id] = [t0].[PersonId]) AND ([t1].[Status] = @p0)
)
为什么会有这种差异?
编辑:
到目前为止,我所做的一切都是在调试器中检查可查询内容以生成SQL。然而,在像Jon建议的设置记录器之后,似乎执行的真正SQL不同。
#1
SELECT [t1].[Id], [t1].etc ... [t0].Id, [t1].etc ...
FROM [Cars] AS [t0], [People] AS [t1]
WHERE ([t1].[Id] = [t0].[PersonId]) AND (EXISTS(
SELECT NULL AS [EMPTY]
FROM [People] AS [t2]
WHERE ([t2].[Id] = [t0].[PersonId]) AND ([t2].[Status] = (CONVERT(NVarChar,@p0)))
)) AND ([t1].[Status] = @p1)
-- @p0: Input Int (Size = 0; Prec = 0; Scale = 0) [2]
-- @p1: Input NVarChar (Size = 7; Prec = 0; Scale = 0) [STUDENT]
#2
SELECT [t1].[Id], [t1].etc ... [t0].Id, [t1].etc ...
FROM [Cars] AS [t0], [People] AS [t1]
WHERE ([t1].[Id] = [t0].[PersonId]) AND (EXISTS(
SELECT NULL AS [EMPTY]
FROM [People] AS [t2]
WHERE ([t2].[Id] = [t0].[PersonId]) AND ([t2].[Status] = @p0)
)) AND ([t1].[Status] = @p1)
-- @p0: Input NVarChar (Size = 7; Prec = 0; Scale = 0) [STUDENT]
-- @p1: Input NVarChar (Size = 7; Prec = 0; Scale = 0) [STUDENT]