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.

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.

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

标签： entity-framework entity-framework-6 refactoring

1条回答

小情绪 Triste *

2楼-- · 2020-02-07 05:51

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.

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.

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.

something like

 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();

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, . . .

0人赞添加讨论(0) 举报

Entity framework nested projections are slow

采纳回答

编辑标签

举报内容

检举类型

检举原因

检举说明(必填)

打开微信“扫一扫”，打开网页后点击屏幕右上角分享按钮

付费偷看金额在0.1-10元之间