我正在尝试理解为什么在我的情况下,使用连接操作比使用导航属性的语句更快。我有两个查询。
第一个查询使用导航属性:
生成的 SQL:
在运行第一个查询之前,请调用
结果: 导航属性查询:00:00:00.6719646 联接查询:00:00:00.4941169
从结果来看,似乎使用联接而不是导航属性的Linq查询更快。这是真的还是我做错了什么?
第一个查询使用导航属性:
var result = (from users in context.MetricBloodPreasure
orderby users.User.LastName, users.User.FirstName
select new
{
UserName = users.User.LastName + ", " + users.User.FirstName,
Date = users.DateOfValue,
}).ToList();
生成的 SQL:
SELECT
[Project1].[C1] AS [C1],
[Project1].[C2] AS [C2],
[Project1].[DateOfValue] AS [DateOfValue]
FROM ( SELECT
[Extent1].[DateOfValue] AS [DateOfValue],
[Extent2].[FirstName] AS [FirstName],
[Extent2].[LastName] AS [LastName],
1 AS [C1],
CASE WHEN ([Extent2].[LastName] IS NULL) THEN N'' ELSE [Extent2].[LastName] END + N', ' + CASE WHEN ([Extent2].[FirstName] IS NULL) THEN N'' ELSE [Extent2].[FirstName] END AS [C2]
FROM [dbo].[MetricBloodPreasure] AS [Extent1]
INNER JOIN [dbo].[User] AS [Extent2] ON [Extent1].[UserId] = [Extent2].[Id]
) AS [Project1]
ORDER BY [Project1].[LastName] ASC, [Project1].[FirstName] ASC
使用join方法的第二种方式:
var result1 = (from u in context.User
orderby u.LastName, u.FirstName
join us in context.MetricBloodPreasure
on u.Id equals us.UserId into users
from s in users
select new
{
UserName = s.User.LastName + ", " + s.User.FirstName,
Date = s.DateOfValue,
}).ToList();
生成的 SQL:
SELECT
1 AS [C1],
CASE WHEN ([Extent1].[LastName] IS NULL) THEN N'' ELSE [Extent1].[LastName] END + N', ' + CASE WHEN ([Extent1].[FirstName] IS NULL) THEN N'' ELSE [Extent1].[FirstName] END AS [C2],
[Extent2].[DateOfValue] AS [DateOfValue]
FROM [dbo].[User] AS [Extent1]
INNER JOIN [dbo].[MetricBloodPreasure] AS [Extent2] ON ([Extent1].[Id] = [Extent2].[UserId]) AND ([Extent2].[UserId] = [Extent1].[Id])
在运行第一个查询之前,请调用
var user = context.User.FirstOrDefault();
,因为我认为打开到数据库的连接需要一些时间。结果: 导航属性查询:00:00:00.6719646 联接查询:00:00:00.4941169
从结果来看,似乎使用联接而不是导航属性的Linq查询更快。这是真的还是我做错了什么?
...from s in users orderby u.LastName, u.FirstName...
。 - jbl