CODEWITHSUNDEEP
c#sqlentity-frameworklinq
Fakhar Ahmad Rasul
Fakhar Ahmad Rasul

Reputation: 1691

Entity Framework query optimization, trying to avoid any

Below is the query I am trying to optimize:-

posts = ctx.Posts
    .Include(x => x.User)
    .Include(x => x.User.UserLanguageMappings)
    .Include(x => x.Medias)
    .Include(x => x.Medias.Select(y => y.MediaUserViews))
    .Include(x => x.PollOptions)
    .Include(x => x.Comments)
    .Include(x => x.TurnOffNotifications)
    .Include(x => x.SharedParent)
    .Include(x => x.SharedParent.User)
    .Include(x => x.ExtendedPostList.Select(extendedPost => extendedPost.Medias))
    .Where(x => x.IsDeleted == false 
                && x.Group_Id == null 
                && x.ParentPost_Id == null
                && (myFollowingsIds.Contains(x.User_Id)
                || x.Visibility == (int)PostVisibilityTypes.Public 
                && x.User.UserLanguageMappings.Any(y => UserLanguageIds.Contains(y.LanguageId)))        //using any here
                && !BlockedUsers.Contains(x.User_Id)
                && !HideAllUsersIds.Contains(x.User_Id)
                && !HidePostsIds.Contains(x.Id)
                && !ReportPostsIds.Contains(x.Id)
                && (x.Visibility != (int)PostVisibilityTypes.OnlyMe || 
                    x.Visibility == (int)PostVisibilityTypes.OnlyMe && 
                    x.User_Id == userId)
                && (x.IsPoll ? x.PollExpiryTime >= DateTime.UtcNow : true))
    .OrderByDescending(x => x.Id)
    .Skip(Math.Abs(PageNo - 1) * PageSize) 
    .Take(PageSize)
    .ToList();

My problems with this query:

As far as my understanding of EF goes, everything after Any() will not be executed on the database and that's of course not very efficient. I am looking for an alternate approach with which the entire query is executed on the database and any other ways I can optimize this query.

UserLanguageMapping model:-

public partial class UserLanguageMapping
{
    public int Id { get; set; }
    public int UserId { get; set; }
    public int LanguageId { get; set; }
}

UserLanguageIds is a list of all the language ids of the logged in user

Upvotes: 0

Views: 56

Answers (1)

Raju Joseph
Raju Joseph

Reputation: 533

Any should be translated to SQL. Can you check the SQL generated by EF by enabling logging.

Upvotes: 1

Related Questions