实体框架嵌套投影很慢 [英] Entity framework nested projections are slow
本文介绍了实体框架嵌套投影很慢的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我正在运行查询以获取用户个人资料.查询获取用户详细信息，以及他们发布的所有评论，以及评论中的评论.
I'm running a query to fetch a users profile. The query get the users details, as well as all the reviews they have posted, as well as the comments in the reviews.
这很可能是我试图返回太多的情况，但是由于 api 是从移动设备调用的，我宁愿在一个网络调用中获得尽可能多的东西，而不是进行多个网络调用.
It may well be a case of I'm trying to get back too much, but as the api is getting called from mobile, I'd rather get as much as I can in one network call rather than making multiple network calls.
目前正在生成一些非常长的 sql，大约需要 25 秒！
At the moment this is generating some really long sql, and takes around 25 seconds!
关于如何改进它的任何提示，或者预测是否是正确的方法
Any tips on how to improve it, or whether projections are even right way to do it
public UserVM GetUserInfo(string userId, string currentUserId)
{
var results =
from u in context.AspNetUsers
where u.Id == userId
select new UserVM
{
Name = u.UserName, Id = u.Id, ProfilePic = u.ProfilePicUrl, FollowerCount = u.Followers.Count, FollowingCount = u.Following.Count,
MemberSince = u.RegisteredDate,
RatingsCount = u.Ratings.Count(x => x.IsDeleted!=true),
FollowedByCurrentUser = currentUserId != null && u.Followers.Any(x => x.FollowedByUserId == currentUserId && x.UserId == userId),
reviews =
from r in u.Ratings
where r.IsDeleted != true
&& r.IsDraft != true
select new RatingVM()
{
ratingId = r.Id,
author_name = r.User.UserName,
userId = r.UserId,
profile_photo_url = r.User.ProfilePicUrl,
rating = r.RatingValue,
text = r.RatingComment,
created = r.Created,
likeCount = r.RatingLikes.Count(x => x.IsLiked && x.RatingId == r.Id),
likedByCurrentUser = currentUserId != null && r.RatingLikes.Any(x => x.IsLiked && x.RatingId == r.Id && x.UserId == currentUserId),
photos = from ri in r.RatingImages
select new PhotoVM { Id = ri.Id, width = 0, height = 0, photo_reference = ri.PhotoUrl, isMember = true, googlePlaceId = r.Place.GooglePlaceId, placeName = r.Place.Name },
comments = from c in r.Comments
where c.IsDeleted != true
select new CommentVM { commentId = c.Id, Created = c.Created, CommentText = c.CommentText, RatingId = r.Id , UserName = c.User.UserName, ProfilePicUrl = c.User.ProfilePicUrl, userId = c.UserId }
}
};
return results.FirstOrDefault();
}
SELECT
[Project17].[C1] AS [C1],
[Project17].[UserName] AS [UserName],
[Project17].[Id] AS [Id],
[Project17].[ProfilePicUrl] AS [ProfilePicUrl],
[Project17].[C32] AS [C2],
[Project17].[C33] AS [C3],
[Project17].[RegisteredDate] AS [RegisteredDate],
[Project17].[C34] AS [C4],
[Project17].[C2] AS [C5],
[Project17].[C31] AS [C6],
[Project17].[C4] AS [C7],
[Project17].[C5] AS [C8],
[Project17].[C6] AS [C9],
[Project17].[C7] AS [C10],
[Project17].[C8] AS [C11],
[Project17].[C9] AS [C12],
[Project17].[C10] AS [C13],
[Project17].[C11] AS [C14],
[Project17].[C12] AS [C15],
[Project17].[C13] AS [C16],
[Project17].[C14] AS [C17],
[Project17].[C3] AS [C18],
[Project17].[C15] AS [C19],
[Project17].[C16] AS [C20],
[Project17].[C17] AS [C21],
[Project17].[C18] AS [C22],
[Project17].[C19] AS [C23],
[Project17].[C20] AS [C24],
[Project17].[C21] AS [C25],
[Project17].[C22] AS [C26],
[Project17].[C23] AS [C27],
[Project17].[C24] AS [C28],
[Project17].[C25] AS [C29],
[Project17].[C26] AS [C30],
[Project17].[C27] AS [C31],
[Project17].[C28] AS [C32],
[Project17].[C29] AS [C33],
[Project17].[C30] AS [C34]
FROM ( SELECT
[Limit1].[Id] AS [Id],
[Limit1].[UserName] AS [UserName],
[Limit1].[ProfilePicUrl] AS [ProfilePicUrl],
[Limit1].[RegisteredDate] AS [RegisteredDate],
[Limit1].[C1] AS [C1],
[Limit1].[C2] AS [C2],
[UnionAll1].[C1] AS [C3],
[UnionAll1].[Id] AS [C4],
[UnionAll1].[Id1] AS [C5],
[UnionAll1].[C2] AS [C6],
[UnionAll1].[C3] AS [C7],
[UnionAll1].[UserId] AS [C8],
[UnionAll1].[C4] AS [C9],
[UnionAll1].[RatingValue] AS [C10],
[UnionAll1].[RatingComment] AS [C11],
[UnionAll1].[Created] AS [C12],
[UnionAll1].[C5] AS [C13],
[UnionAll1].[C6] AS [C14],
[UnionAll1].[Id2] AS [C15],
[UnionAll1].[Id3] AS [C16],
[UnionAll1].[C7] AS [C17],
[UnionAll1].[C8] AS [C18],
[UnionAll1].[PhotoUrl] AS [C19],
[UnionAll1].[C9] AS [C20],
[UnionAll1].[GooglePlaceId] AS [C21],
[UnionAll1].[Name] AS [C22],
[UnionAll1].[C10] AS [C23],
[UnionAll1].[C11] AS [C24],
[UnionAll1].[C12] AS [C25],
[UnionAll1].[C13] AS [C26],
[UnionAll1].[C14] AS [C27],
[UnionAll1].[C15] AS [C28],
[UnionAll1].[C16] AS [C29],
[UnionAll1].[C17] AS [C30],
CASE WHEN ([UnionAll1].[Id] IS NULL) THEN CAST(NULL AS int) ELSE 1 END AS [C31],
[Limit1].[C3] AS [C32],
[Limit1].[C4] AS [C33],
[Limit1].[C5] AS [C34]
FROM (SELECT TOP (1)
@p__linq__4 AS [p__linq__4],
@p__linq__5 AS [p__linq__5],
[Project3].[Id] AS [Id],
[Project3].[UserName] AS [UserName],
[Project3].[ProfilePicUrl] AS [ProfilePicUrl],
[Project3].[RegisteredDate] AS [RegisteredDate],
1 AS [C1],
CASE WHEN ((@p__linq__1 IS NOT NULL) AND ( EXISTS (SELECT
1 AS [C1]
FROM [dbo].[Followers] AS [Extent5]
WHERE ([Project3].[Id] = [Extent5].[UserId]) AND ([Extent5].[FollowedByUserId] = @p__linq__2) AND ([Extent5].[UserId] = @p__linq__3)
))) THEN cast(1 as bit) ELSE cast(0 as bit) END AS [C2],
[Project3].[C1] AS [C3],
[Project3].[C2] AS [C4],
[Project3].[C3] AS [C5]
FROM ( SELECT
[Project2].[Id] AS [Id],
[Project2].[UserName] AS [UserName],
[Project2].[ProfilePicUrl] AS [ProfilePicUrl],
[Project2].[RegisteredDate] AS [RegisteredDate],
[Project2].[C1] AS [C1],
[Project2].[C2] AS [C2],
(SELECT
COUNT(1) AS [A1]
FROM [dbo].[Ratings] AS [Extent4]
WHERE ([Project2].[Id] = [Extent4].[UserId]) AND (1 <> [Extent4].[IsDeleted])) AS [C3]
FROM ( SELECT
[Project1].[Id] AS [Id],
[Project1].[UserName] AS [UserName],
[Project1].[ProfilePicUrl] AS [ProfilePicUrl],
[Project1].[RegisteredDate] AS [RegisteredDate],
[Project1].[C1] AS [C1],
(SELECT
COUNT(1) AS [A1]
FROM [dbo].[Followers] AS [Extent3]
WHERE [Project1].[Id] = [Extent3].[FollowedByUserId]) AS [C2]
FROM ( SELECT
[Extent1].[Id] AS [Id],
[Extent1].[UserName] AS [UserName],
[Extent1].[ProfilePicUrl] AS [ProfilePicUrl],
[Extent1].[RegisteredDate] AS [RegisteredDate],
(SELECT
COUNT(1) AS [A1]
FROM [dbo].[Followers] AS [Extent2]
WHERE [Extent1].[Id] = [Extent2].[UserId]) AS [C1]
FROM [dbo].[AspNetUsers] AS [Extent1]
WHERE [Extent1].[Id] = @p__linq__0
) AS [Project1]
) AS [Project2]
) AS [Project3] ) AS [Limit1]
OUTER APPLY (SELECT
CASE WHEN ([Filter10].[Id1] IS NULL) THEN CAST(NULL AS int) ELSE 1 END AS [C1],
[Project9].[Id] AS [Id],
[Project9].[Id] AS [Id1],
[Project9].[C1] AS [C2],
[Project9].[C2] AS [C3],
[Project9].[UserId] AS [UserId],
[Project9].[C3] AS [C4],
[Project9].[RatingValue] AS [RatingValue],
[Project9].[RatingComment] AS [RatingComment],
[Project9].[Created] AS [Created],
[Project9].[C5] AS [C5],
[Project9].[C4] AS [C6],
[Filter10].[Id1] AS [Id2],
[Filter10].[Id1] AS [Id3],
CASE WHEN ([Filter10].[Id1] IS NULL) THEN CAST(NULL AS int) ELSE 0 END AS [C7],
CASE WHEN ([Filter10].[Id1] IS NULL) THEN CAST(NULL AS int) ELSE 0 END AS [C8],
[Filter10].[PhotoUrl] AS [PhotoUrl],
CASE WHEN ([Filter10].[Id1] IS NULL) THEN CAST(NULL AS bit) ELSE cast(1 as bit) END AS [C9],
[Filter10].[GooglePlaceId] AS [GooglePlaceId],
[Filter10].[Name] AS [Name],
CAST(NULL AS int) AS [C10],
CAST(NULL AS int) AS [C11],
CAST(NULL AS datetime2) AS [C12],
CAST(NULL AS varchar(1)) AS [C13],
CAST(NULL AS int) AS [C14],
CAST(NULL AS varchar(1)) AS [C15],
CAST(NULL AS varchar(1)) AS [C16],
CAST(NULL AS varchar(1)) AS [C17]
FROM (SELECT
[Project7].[Id] AS [Id],
[Project7].[RatingValue] AS [RatingValue],
[Project7].[RatingComment] AS [RatingComment],
[Project7].[Created] AS [Created],
[Project7].[PlaceId] AS [PlaceId],
[Project7].[UserId] AS [UserId],
[Limit1].[UserName] AS [C1],
N'' AS [C2],
[Limit1].[ProfilePicUrl] AS [C3],
CASE WHEN ((@p__linq__4 IS NOT NULL) AND ( EXISTS (SELECT
1 AS [C1]
FROM [dbo].[RatingLikes] AS [Extent8]
WHERE ([Project7].[Id] = [Extent8].[RatingId]) AND ([Extent8].[IsLiked] = 1) AND ([Extent8].[RatingId] = [Project7].[Id]) AND ([Extent8].[UserId] = @p__linq__5)
))) THEN cast(1 as bit) ELSE cast(0 as bit) END AS [C4],
[Project7].[C1] AS [C5]
FROM ( SELECT
[Project6].[Id] AS [Id],
[Project6].[RatingValue] AS [RatingValue],
[Project6].[RatingComment] AS [RatingComment],
[Project6].[Created] AS [Created],
[Project6].[PlaceId] AS [PlaceId],
[Project6].[UserId] AS [UserId],
(SELECT
COUNT(1) AS [A1]
FROM [dbo].[RatingLikes] AS [Extent7]
WHERE ([Project6].[Id] = [Extent7].[RatingId]) AND ([Extent7].[IsLiked] = 1) AND ([Extent7].[RatingId] = [Project6].[Id])) AS [C1]
FROM ( SELECT
[Extent6].[Id] AS [Id],
[Extent6].[RatingValue] AS [RatingValue],
[Extent6].[RatingComment] AS [RatingComment],
[Extent6].[Created] AS [Created],
[Extent6].[PlaceId] AS [PlaceId],
[Extent6].[UserId] AS [UserId]
FROM [dbo].[Ratings] AS [Extent6]
WHERE ([Limit1].[Id] = [Extent6].[UserId]) AND (1 <> [Extent6].[IsDeleted]) AND (1 <> [Extent6].[IsDraft])
) AS [Project6]
) AS [Project7] ) AS [Project9]
OUTER APPLY (SELECT [Extent9].[Id] AS [Id1], [Extent9].[PhotoUrl] AS [PhotoUrl], [Project10].[Name] AS [Name], [Project10].[GooglePlaceId] AS [GooglePlaceId]
FROM [dbo].[RatingImages] AS [Extent9]
LEFT OUTER JOIN (SELECT
[Extent10].[Id] AS [Id],
[Extent10].[Name] AS [Name],
[Extent10].[GooglePlaceId] AS [GooglePlaceId]
FROM [dbo].[Places] AS [Extent10]
WHERE [Project9].[PlaceId] = [Extent10].[Id] ) AS [Project10] ON 1 = 1
WHERE [Project9].[Id] = [Extent9].[RatingId] ) AS [Filter10]
UNION ALL
SELECT
2 AS [C1],
[Project15].[Id] AS [Id],
[Project15].[Id] AS [Id1],
[Project15].[C1] AS [C2],
[Project15].[C2] AS [C3],
[Project15].[UserId] AS [UserId],
[Project15].[C3] AS [C4],
[Project15].[RatingValue] AS [RatingValue],
[Project15].[RatingComment] AS [RatingComment],
[Project15].[Created] AS [Created],
[Project15].[C5] AS [C5],
[Project15].[C4] AS [C6],
CAST(NULL AS int) AS [C7],
CAST(NULL AS int) AS [C8],
CAST(NULL AS int) AS [C9],
CAST(NULL AS int) AS [C10],
CAST(NULL AS varchar(1)) AS [C11],
CAST(NULL AS bit) AS [C12],
CAST(NULL AS varchar(1)) AS [C13],
CAST(NULL AS varchar(1)) AS [C14],
[Join2].[Id2] AS [Id2],
[Join2].[Id2] AS [Id3],
[Join2].[Created] AS [Created1],
[Join2].[CommentText] AS [CommentText],
[Project15].[Id] AS [Id4],
[Join2].[UserName] AS [UserName],
[Join2].[ProfilePicUrl] AS [ProfilePicUrl],
[Join2].[UserId] AS [UserId1]
FROM (SELECT
[Project13].[Id] AS [Id],
[Project13].[RatingValue] AS [RatingValue],
[Project13].[RatingComment] AS [RatingComment],
[Project13].[Created] AS [Created],
[Project13].[UserId] AS [UserId],
[Limit1].[UserName] AS [C1],
N'' AS [C2],
[Limit1].[ProfilePicUrl] AS [C3],
CASE WHEN ((@p__linq__4 IS NOT NULL) AND ( EXISTS (SELECT
1 AS [C1]
FROM [dbo].[RatingLikes] AS [Extent13]
WHERE ([Project13].[Id] = [Extent13].[RatingId]) AND ([Extent13].[IsLiked] = 1) AND ([Extent13].[RatingId] = [Project13].[Id]) AND ([Extent13].[UserId] = @p__linq__5)
))) THEN cast(1 as bit) ELSE cast(0 as bit) END AS [C4],
[Project13].[C1] AS [C5]
FROM ( SELECT
[Project12].[Id] AS [Id],
[Project12].[RatingValue] AS [RatingValue],
[Project12].[RatingComment] AS [RatingComment],
[Project12].[Created] AS [Created],
[Project12].[UserId] AS [UserId],
(SELECT
COUNT(1) AS [A1]
FROM [dbo].[RatingLikes] AS [Extent12]
WHERE ([Project12].[Id] = [Extent12].[RatingId]) AND ([Extent12].[IsLiked] = 1) AND ([Extent12].[RatingId] = [Project12].[Id])) AS [C1]
FROM ( SELECT
[Extent11].[Id] AS [Id],
[Extent11].[RatingValue] AS [RatingValue],
[Extent11].[RatingComment] AS [RatingComment],
[Extent11].[Created] AS [Created],
[Extent11].[UserId] AS [UserId]
FROM [dbo].[Ratings] AS [Extent11]
WHERE ([Limit1].[Id] = [Extent11].[UserId]) AND (1 <> [Extent11].[IsDeleted]) AND (1 <> [Extent11].[IsDraft])
) AS [Project12]
) AS [Project13] ) AS [Project15]
INNER JOIN (SELECT [Extent14].[Id] AS [Id2], [Extent14].[CommentText] AS [CommentText], [Extent14].[Created] AS [Created], [Extent14].[RatingId] AS [RatingId], [Extent14].[IsDeleted] AS [IsDeleted], [Extent14].[UserId] AS [UserId], [Extent15].[UserName] AS [UserName], [Extent15].[ProfilePicUrl] AS [ProfilePicUrl]
FROM [dbo].[Comments] AS [Extent14]
INNER JOIN [dbo].[AspNetUsers] AS [Extent15] ON [Extent14].[UserId] = [Extent15].[Id] ) AS [Join2] ON ([Project15].[Id] = [Join2].[RatingId]) AND (1 <> [Join2].[IsDeleted])) AS [UnionAll1]
) AS [Project17]
ORDER BY [Project17].[Id] ASC, [Project17].[C31] ASC, [Project17].[C5] ASC, [Project17].[C3] ASC
推荐答案
EF6 的每个 LINQ 查询使用一个大 SQL 查询的加载策略对于加载复杂的对象图并不是最佳的.还有其他几种方法可以加载图表.
EF6's loading strategy of using one big SQL query per LINQ query is not optimal for loading complex object graphs. There are several other ways to get your graph loaded.
例如，您可以加载根 AspNetUser 实体，然后遍历导航属性来构建图表.EF 会根据需要延迟加载.而且一旦实体被缓存在上下文中，后续的导航就不会引起额外的查询.
For instance you could load the root AspNetUser entity, and then traverse the Navigation Properties to build your graph. EF would Lazy Load as needed. And once an entity is cached in the context, subsequent navigations would not cause additional queries.
事实上，如果您预取部分或全部相关实体，EF 将缝合"或修复"您的导航属性.
In fact if you pre-fetch some or all of the related entities, EF will "stitch" or "fix-up" your navigation properties.
因此，作为一种优化，您可以编写查询，使用一些简单而廉价的查询，将您需要的实体提取到上下文缓存中.
So as an optimization, you can write queries that fetch into the context cache the entities that you will need using a few simple and cheap queries.
类似
var user = context.AspNetUsers.Where(u => u.Id == userId).Single();
var followers = context.Followers.Where(f => f.UserId == userId).ToList();
var ratings = context.Ratings.Where(f => f.UserId == userId).ToList();
var ratingIds = ratings.Select(r => r.Id).ToList();
var ratingLikes = context.RatingLikes.Where(x => ratingIds.Contains(x.RatingId) && x.IsLiked ).ToList();
var ratingPhotos = context.RatingPhotos.Where(x => ratingIds.Contains(x.RatingId)).ToList();
然后从加载的 AspNetUser 构建您的结果，例如
Then build your results from the loaded AspNetUser, eg
var u = user;
var results =
select new UserVM
{
Name = u.UserName,
Id = u.Id,
ProfilePic = u.ProfilePicUrl,
FollowerCount = u.Followers.Count, . . .
这篇关于实体框架嵌套投影很慢的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
