Navid Salajegheh
Reputation: 1
entity framework query and performance
I have a Project Like social Network I have to write a query to get some posts and last comment of post that my followers (not blocked) liked That my code
public List<PostProject> GetFavoritePosts(string userId, int currentPage, int noOfRecords)
{
var skipPosts = noOfRecords * currentPage;
int i = -260;
var day = DateTime.Now.AddDays(i);
var blockedusers = DataContext.BlockedUsers.Where(bu => bu.BlockerId == userId);
var followers = DataContext.FollowUser.Where(u => u.FollowFromUserId == userId);
var posts = DataContext.Posts.Where(p => p.UserId != userId
&& p.DateOfUpdate > day
&& p.Likes.Any(l => followers.Any(fu => fu.FollowToUserId == l.UserId))
&& p.PostStatusId == 1
&& p.PostType == false
&& blockedusers.All(bu => bu.BlockedId != p.UserId));
var feed = posts
.OrderByDescending(post => post.Likes.Count)
.Select(post => new PostProject
{
PostId = post.PostId,
Content = post.Content,
Image = post.Image,
Location = post.Location,
Video = post.Video,
CreatedDate = post.CreatedDate,
DateOfUpdate = post.DateOfUpdate,
User = post.User,
ILiked = post.Likes.Any(like => like.UserId == userId),
LikeCount = post.Likes.Count,
CommentsCount = post.Comments.Count,
PostStatusId = post.PostStatusId,
ShareCount = post.SharePosts.Count,
Comments = new List<Comment> { post.Comments.OrderByDescending(c => c.CommentDate).FirstOrDefault() }
});
return feed.Skip(() => skipPosts).Take(() => noOfRecords).ToList();
}
This code generate sqlQuery with 16 select and 50ms to run !!!!
Where is my mistake thanks
sql Query :
SELECT
[Project13].[PostStatusId] AS [PostStatusId],
[Project13].[PostId] AS [PostId],
[Project13].[Content] AS [Content],
[Project13].[Image] AS [Image],
[Project13].[Location] AS [Location],
[Project13].[Video] AS [Video],
[Project13].[CreatedDate] AS [CreatedDate],
[Project13].[DateOfUpdate] AS [DateOfUpdate],
[Project13].[Id] AS [Id],
[Project13].[VisualId] AS [VisualId],
[Project13].[ProfilePicUrl] AS [ProfilePicUrl],
[Project13].[DateCreated] AS [DateCreated],
[Project13].[Activated] AS [Activated],
[Project13].[ActivationCode] AS [ActivationCode],
[Project13].[RoleId] AS [RoleId],
[Project13].[TeamId] AS [TeamId],
[Project13].[RegisterToken] AS [RegisterToken],
[Project13].[Email] AS [Email],
[Project13].[EmailConfirmed] AS [EmailConfirmed],
[Project13].[PasswordHash] AS [PasswordHash],
[Project13].[SecurityStamp] AS [SecurityStamp],
[Project13].[PhoneNumber] AS [PhoneNumber],
[Project13].[PhoneNumberConfirmed] AS [PhoneNumberConfirmed],
[Project13].[TwoFactorEnabled] AS [TwoFactorEnabled],
[Project13].[LockoutEndDateUtc] AS [LockoutEndDateUtc],
[Project13].[LockoutEnabled] AS [LockoutEnabled],
[Project13].[AccessFailedCount] AS [AccessFailedCount],
[Project13].[UserName] AS [UserName],
[Project13].[C1] AS [C1],
[Project13].[C4] AS [C2],
[Project13].[C5] AS [C3],
[Project13].[C6] AS [C4],
[Project13].[C2] AS [C5],
[Project13].[CommentId] AS [CommentId],
[Project13].[CommentText] AS [CommentText],
[Project13].[PostId1] AS [PostId1],
[Project13].[CommentDate] AS [CommentDate],
[Project13].[UserId] AS [UserId],
[Project13].[RowVersion] AS [RowVersion]
FROM ( SELECT
[Project11].[PostId] AS [PostId],
[Project11].[Content] AS [Content],
[Project11].[PostStatusId] AS [PostStatusId],
[Project11].[Image] AS [Image],
[Project11].[Location] AS [Location],
[Project11].[Video] AS [Video],
[Project11].[CreatedDate] AS [CreatedDate],
[Project11].[DateOfUpdate] AS [DateOfUpdate],
[Project11].[Id] AS [Id],
[Project11].[VisualId] AS [VisualId],
[Project11].[ProfilePicUrl] AS [ProfilePicUrl],
[Project11].[DateCreated] AS [DateCreated],
[Project11].[Activated] AS [Activated],
[Project11].[ActivationCode] AS [ActivationCode],
[Project11].[RoleId] AS [RoleId],
[Project11].[TeamId] AS [TeamId],
[Project11].[RegisterToken] AS [RegisterToken],
[Project11].[Email] AS [Email],
[Project11].[EmailConfirmed] AS [EmailConfirmed],
[Project11].[PasswordHash] AS [PasswordHash],
[Project11].[SecurityStamp] AS [SecurityStamp],
[Project11].[PhoneNumber] AS [PhoneNumber],
[Project11].[PhoneNumberConfirmed] AS [PhoneNumberConfirmed],
[Project11].[TwoFactorEnabled] AS [TwoFactorEnabled],
[Project11].[LockoutEndDateUtc] AS [LockoutEndDateUtc],
[Project11].[LockoutEnabled] AS [LockoutEnabled],
[Project11].[AccessFailedCount] AS [AccessFailedCount],
[Project11].[UserName] AS [UserName],
[Project11].[C1] AS [C1],
[Limit1].[CommentId] AS [CommentId],
[Limit1].[CommentText] AS [CommentText],
[Limit1].[PostId] AS [PostId1],
[Limit1].[CommentDate] AS [CommentDate],
[Limit1].[UserId] AS [UserId],
[Limit1].[RowVersion] AS [RowVersion],
1 AS [C2],
[Project11].[C2] AS [C3],
[Project11].[C3] AS [C4],
[Project11].[C4] AS [C5],
[Project11].[C5] AS [C6]
FROM (SELECT
[Project9].[PostId] AS [PostId],
[Project9].[Content] AS [Content],
[Project9].[PostStatusId] AS [PostStatusId],
[Project9].[Image] AS [Image],
[Project9].[Location] AS [Location],
[Project9].[Video] AS [Video],
[Project9].[CreatedDate] AS [CreatedDate],
[Project9].[DateOfUpdate] AS [DateOfUpdate],
[Project9].[Id] AS [Id],
[Project9].[VisualId] AS [VisualId],
[Project9].[ProfilePicUrl] AS [ProfilePicUrl],
[Project9].[DateCreated] AS [DateCreated],
[Project9].[Activated] AS [Activated],
[Project9].[ActivationCode] AS [ActivationCode],
[Project9].[RoleId] AS [RoleId],
[Project9].[TeamId] AS [TeamId],
[Project9].[RegisterToken] AS [RegisterToken],
[Project9].[Email] AS [Email],
[Project9].[EmailConfirmed] AS [EmailConfirmed],
[Project9].[PasswordHash] AS [PasswordHash],
[Project9].[SecurityStamp] AS [SecurityStamp],
[Project9].[PhoneNumber] AS [PhoneNumber],
[Project9].[PhoneNumberConfirmed] AS [PhoneNumberConfirmed],
[Project9].[TwoFactorEnabled] AS [TwoFactorEnabled],
[Project9].[LockoutEndDateUtc] AS [LockoutEndDateUtc],
[Project9].[LockoutEnabled] AS [LockoutEnabled],
[Project9].[AccessFailedCount] AS [AccessFailedCount],
[Project9].[UserName] AS [UserName],
CASE WHEN ( EXISTS (SELECT
1 AS [C1]
FROM [dbo].[Likes] AS [Extent10]
WHERE ([Project9].[PostId] = [Extent10].[PostId]) AND (([Extent10].[UserId] = @p__linq__4) OR (([Extent10].[UserId] IS NULL) AND (@p__linq__4 IS NULL)))
)) THEN cast(1 as bit) ELSE cast(0 as bit) END AS [C1],
[Project9].[C1] AS [C2],
[Project9].[C2] AS [C3],
[Project9].[C3] AS [C4],
[Project9].[C4] AS [C5]
FROM ( SELECT
[Project8].[PostId] AS [PostId],
[Project8].[Content] AS [Content],
[Project8].[PostStatusId] AS [PostStatusId],
[Project8].[Image] AS [Image],
[Project8].[Location] AS [Location],
[Project8].[Video] AS [Video],
[Project8].[CreatedDate] AS [CreatedDate],
[Project8].[DateOfUpdate] AS [DateOfUpdate],
[Project8].[Id] AS [Id],
[Project8].[VisualId] AS [VisualId],
[Project8].[ProfilePicUrl] AS [ProfilePicUrl],
[Project8].[DateCreated] AS [DateCreated],
[Project8].[Activated] AS [Activated],
[Project8].[ActivationCode] AS [ActivationCode],
[Project8].[RoleId] AS [RoleId],
[Project8].[TeamId] AS [TeamId],
[Project8].[RegisterToken] AS [RegisterToken],
[Project8].[Email] AS [Email],
[Project8].[EmailConfirmed] AS [EmailConfirmed],
[Project8].[PasswordHash] AS [PasswordHash],
[Project8].[SecurityStamp] AS [SecurityStamp],
[Project8].[PhoneNumber] AS [PhoneNumber],
[Project8].[PhoneNumberConfirmed] AS [PhoneNumberConfirmed],
[Project8].[TwoFactorEnabled] AS [TwoFactorEnabled],
[Project8].[LockoutEndDateUtc] AS [LockoutEndDateUtc],
[Project8].[LockoutEnabled] AS [LockoutEnabled],
[Project8].[AccessFailedCount] AS [AccessFailedCount],
[Project8].[UserName] AS [UserName],
[Project8].[C1] AS [C1],
[Project8].[C2] AS [C2],
[Project8].[C3] AS [C3],
(SELECT
COUNT(1) AS [A1]
FROM [dbo].[SharePosts] AS [Extent9]
WHERE [Project8].[PostId] = [Extent9].[PostId]) AS [C4]
FROM ( SELECT
[Project7].[PostId] AS [PostId],
[Project7].[Content] AS [Content],
[Project7].[PostStatusId] AS [PostStatusId],
[Project7].[Image] AS [Image],
[Project7].[Location] AS [Location],
[Project7].[Video] AS [Video],
[Project7].[CreatedDate] AS [CreatedDate],
[Project7].[DateOfUpdate] AS [DateOfUpdate],
[Project7].[Id] AS [Id],
[Project7].[VisualId] AS [VisualId],
[Project7].[ProfilePicUrl] AS [ProfilePicUrl],
[Project7].[DateCreated] AS [DateCreated],
[Project7].[Activated] AS [Activated],
[Project7].[ActivationCode] AS [ActivationCode],
[Project7].[RoleId] AS [RoleId],
[Project7].[TeamId] AS [TeamId],
[Project7].[RegisterToken] AS [RegisterToken],
[Project7].[Email] AS [Email],
[Project7].[EmailConfirmed] AS [EmailConfirmed],
[Project7].[PasswordHash] AS [PasswordHash],
[Project7].[SecurityStamp] AS [SecurityStamp],
[Project7].[PhoneNumber] AS [PhoneNumber],
[Project7].[PhoneNumberConfirmed] AS [PhoneNumberConfirmed],
[Project7].[TwoFactorEnabled] AS [TwoFactorEnabled],
[Project7].[LockoutEndDateUtc] AS [LockoutEndDateUtc],
[Project7].[LockoutEnabled] AS [LockoutEnabled],
[Project7].[AccessFailedCount] AS [AccessFailedCount],
[Project7].[UserName] AS [UserName],
[Project7].[C1] AS [C1],
[Project7].[C2] AS [C2],
(SELECT
COUNT(1) AS [A1]
FROM [dbo].[Comments] AS [Extent8]
WHERE [Project7].[PostId] = [Extent8].[PostId]) AS [C3]
FROM ( SELECT
[Project6].[PostId] AS [PostId],
[Project6].[Content] AS [Content],
[Project6].[PostStatusId] AS [PostStatusId],
[Project6].[Image] AS [Image],
[Project6].[Location] AS [Location],
[Project6].[Video] AS [Video],
[Project6].[CreatedDate] AS [CreatedDate],
[Project6].[DateOfUpdate] AS [DateOfUpdate],
[Project6].[Id] AS [Id],
[Project6].[VisualId] AS [VisualId],
[Project6].[ProfilePicUrl] AS [ProfilePicUrl],
[Project6].[DateCreated] AS [DateCreated],
[Project6].[Activated] AS [Activated],
[Project6].[ActivationCode] AS [ActivationCode],
[Project6].[RoleId] AS [RoleId],
[Project6].[TeamId] AS [TeamId],
[Project6].[RegisterToken] AS [RegisterToken],
[Project6].[Email] AS [Email],
[Project6].[EmailConfirmed] AS [EmailConfirmed],
[Project6].[PasswordHash] AS [PasswordHash],
[Project6].[SecurityStamp] AS [SecurityStamp],
[Project6].[PhoneNumber] AS [PhoneNumber],
[Project6].[PhoneNumberConfirmed] AS [PhoneNumberConfirmed],
[Project6].[TwoFactorEnabled] AS [TwoFactorEnabled],
[Project6].[LockoutEndDateUtc] AS [LockoutEndDateUtc],
[Project6].[LockoutEnabled] AS [LockoutEnabled],
[Project6].[AccessFailedCount] AS [AccessFailedCount],
[Project6].[UserName] AS [UserName],
[Project6].[C1] AS [C1],
(SELECT
COUNT(1) AS [A1]
FROM [dbo].[Likes] AS [Extent7]
WHERE [Project6].[PostId] = [Extent7].[PostId]) AS [C2]
FROM ( SELECT
[Project5].[PostId] AS [PostId],
[Project5].[Content] AS [Content],
[Project5].[PostStatusId] AS [PostStatusId],
[Project5].[Image] AS [Image],
[Project5].[Location] AS [Location],
[Project5].[Video] AS [Video],
[Project5].[CreatedDate] AS [CreatedDate],
[Project5].[DateOfUpdate] AS [DateOfUpdate],
[Extent6].[Id] AS [Id],
[Extent6].[VisualId] AS [VisualId],
[Extent6].[ProfilePicUrl] AS [ProfilePicUrl],
[Extent6].[DateCreated] AS [DateCreated],
[Extent6].[Activated] AS [Activated],
[Extent6].[ActivationCode] AS [ActivationCode],
[Extent6].[RoleId] AS [RoleId],
[Extent6].[TeamId] AS [TeamId],
[Extent6].[RegisterToken] AS [RegisterToken],
[Extent6].[Email] AS [Email],
[Extent6].[EmailConfirmed] AS [EmailConfirmed],
[Extent6].[PasswordHash] AS [PasswordHash],
[Extent6].[SecurityStamp] AS [SecurityStamp],
[Extent6].[PhoneNumber] AS [PhoneNumber],
[Extent6].[PhoneNumberConfirmed] AS [PhoneNumberConfirmed],
[Extent6].[TwoFactorEnabled] AS [TwoFactorEnabled],
[Extent6].[LockoutEndDateUtc] AS [LockoutEndDateUtc],
[Extent6].[LockoutEnabled] AS [LockoutEnabled],
[Extent6].[AccessFailedCount] AS [AccessFailedCount],
[Extent6].[UserName] AS [UserName],
[Project5].[C1] AS [C1]
FROM (SELECT
[Extent1].[PostId] AS [PostId],
[Extent1].[Content] AS [Content],
[Extent1].[PostStatusId] AS [PostStatusId],
[Extent1].[UserId] AS [UserId],
[Extent1].[Image] AS [Image],
[Extent1].[Location] AS [Location],
[Extent1].[Video] AS [Video],
[Extent1].[CreatedDate] AS [CreatedDate],
[Extent1].[DateOfUpdate] AS [DateOfUpdate],
(SELECT
COUNT(1) AS [A1]
FROM [dbo].[Likes] AS [Extent5]
WHERE [Extent1].[PostId] = [Extent5].[PostId]) AS [C1]
FROM [dbo].[Posts] AS [Extent1]
WHERE ( NOT (([Extent1].[UserId] = @p__linq__0) AND (0 = (CASE WHEN (@p__linq__0 IS NULL) THEN cast(1 as bit) ELSE cast(0 as bit) END)))) AND ([Extent1].[DateOfUpdate] > @p__linq__1) AND ( EXISTS (SELECT
1 AS [C1]
FROM ( SELECT
[Extent2].[UserId] AS [UserId]
FROM [dbo].[Likes] AS [Extent2]
WHERE [Extent1].[PostId] = [Extent2].[PostId]
) AS [Project1]
WHERE EXISTS (SELECT
1 AS [C1]
FROM [dbo].[FollowUsers] AS [Extent3]
WHERE ([Extent3].[FollowFromUserId] = @p__linq__2) AND ([Extent3].[FollowToUserId] = [Project1].[UserId])
)
)) AND (1 = [Extent1].[PostStatusId]) AND (0 = [Extent1].[PostType]) AND ( NOT EXISTS (SELECT
1 AS [C1]
FROM [dbo].[BlockedUsers] AS [Extent4]
WHERE ([Extent4].[BlockerId] = @p__linq__3) AND (([Extent4].[BlockedId] = [Extent1].[UserId]) OR (CASE WHEN ([Extent4].[BlockedId] <> [Extent1].[UserId]) THEN cast(1 as bit) WHEN ([Extent4].[BlockedId] = [Extent1].[UserId]) THEN cast(0 as bit) END IS NULL))
)) ) AS [Project5]
LEFT OUTER JOIN [dbo].[AspNetUsers] AS [Extent6] ON [Project5].[UserId] = [Extent6].[Id]
) AS [Project6]
) AS [Project7]
) AS [Project8]
) AS [Project9] ) AS [Project11]
OUTER APPLY (SELECT TOP (1) [Project12].[CommentId] AS [CommentId], [Project12].[CommentText] AS [CommentText], [Project12].[PostId] AS [PostId], [Project12].[CommentDate] AS [CommentDate], [Project12].[UserId] AS [UserId], [Project12].[RowVersion] AS [RowVersion]
FROM ( SELECT
[Extent11].[CommentId] AS [CommentId],
[Extent11].[CommentText] AS [CommentText],
[Extent11].[PostId] AS [PostId],
[Extent11].[CommentDate] AS [CommentDate],
[Extent11].[UserId] AS [UserId],
[Extent11].[RowVersion] AS [RowVersion]
FROM [dbo].[Comments] AS [Extent11]
WHERE [Project11].[PostId] = [Extent11].[PostId]
) AS [Project12]
ORDER BY [Project12].[CommentDate] DESC ) AS [Limit1]
) AS [Project13]
ORDER BY [Project13].[C3] DESC, [Project13].[PostId] ASC, [Project13].[Id] ASC, [Project13].[C2] ASC
Upvotes: 0
Views: 85
Answers (2)
DvS
Reputation: 1095
First, it seems there is confusion between Linq and Linq to SQL. Not all of Linq can be translated into SQL queries. For example: Any() and All() can't be used with Linq to Sql here - they are in-memory collection functions. This means that all rows need to be fetched and then resolved afterwards.
You are also not resolving your first two queries, for example by calling ToList().
var blockedusers = DataContext.BlockedUsers.Where(bu => bu.BlockerId == userId);
var followers = DataContext.FollowUser.Where(u => u.FollowFromUserId == userId);
This leaves them to run on-demand every time that they are used (deferred query execution) - meaning they call SQL each time. Beware the IEnumerable!
You could probably get rid of all your issues by doing joins onto BlockedUser and FollowUser all in the same query. For example, use a left join to BlockedUser and eliminate those rows by testing that the blocked user is null.
Upvotes: 1
Azamat
Reputation: 53
I think, you should firstly orderby, skeep, take and then select new PostObject in return
Upvotes: 0
Related Questions
- Entity Framework LINQ SQL Query Performance
- Optimize entity framework query
- Entity framework performance after query
- Performance tuning Entity Framework queries
- Improving Performance of an Entity Framework Query
- LINQ entity query performance
- EF and LINQ query to database speed
- Entity Framework - query execution performance issue
- LINQ query performance issue
- EF query performance issues