c# - 性能改进，谓词系统 linq 查询

Question

我正在尝试在管理 asp 网络核心应用程序上重新开发新闻系统。我们可以发布有关用户或公司的附加信息，并且这些信息具有权限(允许用户查看或不允许查看其功能)。

我正在使用 Entity Framework Core，我遇到了一些性能问题。我是学生，我的代码对你来说肯定很糟糕 ^^

请看:

我试图删除 ToList()，但它抛出了一个错误，而不是另一个线程的另一个请求...我的 DbContext 生命周期是 transient 的

IQueryable<AdditionalInformation> query = _context
                    .AdditionalInformations
                    // Company is the company targeted by this information (can be null if it's an information about a user)
                    .Include(u => u.Company)
                    // SSTRNUser is the user targeted by this information (can be null if it's company additionnal information
                    .Include(u => u.SSTRNUser)
                    .Include(u => u.Creator)
                    .Include(u => u.Documents)
                    .ToList()
                    .AsQueryable<AdditionalInformation>();

                var user = _context.Users
                    .Include(u => u.Function)
                    .FirstOrDefault(u => u .UserName == HttpContext.User.Identity.Name);

                var all = new List<Predicate<AdditionalInformation>>();

                // These persons must have the vision only on the companies they work
                string[] specificFunctions = new string[] { "AS.ST","I.ST","PS.T","CONS.ALL" };

                if (specificFunctions.Contains(user.Function.Code))
                {
                    if(user.Function.Code == "AS.ST")
                    {

                        user = _context.Users
                        .Include(u => u.Function)
                        .Include(u => u.EntreprisesAsAst)
                        .FirstOrDefault(u => u.UserName == HttpContext.User.Identity.Name);

                        // EntreprisesAsAst is the mapping table between the employee, the company and his planning on each companies he works
                        // For others Function, it's another tables
                        Predicate<AdditionalInformation> functionWorkerPredicate = c => query.Any(t => c.Company != null && c.Rights.Any(r => r == "AS.ST") && user.EntreprisesAsAst.Any(e => e.EntrepriseId == c.CompanyId));
                        all.Add(functionWorkerPredicate);
                    }
                    else if(user.Function.Code == "I.ST")
                    {

                        user = _context.Users
                        .Include(u => u.Function)
                        .Include(u => u.EntreprisesAsInf)
                        .FirstOrDefault(u => u.UserName == HttpContext.User.Identity.Name);

                        Predicate<AdditionalInformation> functionWorkerPredicate = c => query.Any(t => c.Company != null && c.Rights.Any(r => r == "I.ST") && user.EntreprisesAsInf.Any(e => e.EntrepriseId == c.CompanyId));
                        all.Add(functionWorkerPredicate);
                    }
                    else if(user.Function.Code == "PS.T")
                    {

                        user = _context.Users
                        .Include(u => u.Function)
                        .Include(u => u.EntreprisesAsPsy)
                        .FirstOrDefault(u => u.UserName == HttpContext.User.Identity.Name);

                        Predicate<AdditionalInformation> functionWorkerPredicate = c => query.Any(t => c.Company != null && c.Rights.Any(r => r == "PS.T") && user.EntreprisesAsPsy.Any(e=>e.EntrepriseId == c.CompanyId));
                        all.Add(functionWorkerPredicate);
                    }
                    else if(user.Function.Code == "CONS.ALL")
                    {

                        user = _context.Users
                        .Include(u => u.Function)
                        .Include(u => u.EntreprisesAsCon)
                        .FirstOrDefault(u => u.UserName == HttpContext.User.Identity.Name);

                        Predicate<AdditionalInformation> functionWorkerPredicate = c => query.Any(t => c.Company != null && c.Rights.Any(r => r == "CONS.ALL") && user.EntreprisesAsCon.Any(e => e.EntrepriseId == c.CompanyId));
                        all.Add(functionWorkerPredicate);
                    }



                }
                // this function (ADH = 'adherent' <==> client in France)
                else if (user.Function.Code == "ADH")
                {
                    // He must see only the information about his company when the client is allowed to see their
                    Predicate<AdditionalInformation> functionADHPredicate = c => query.Any(t => c.Company != null && c.CompanyId == user.CompanyId && c.Rights.Any(r => r == "ADH"));
                    all.Add(functionADHPredicate);
                }
                // Else there's other function (managers etc), and they're not scoped to a company (instead of specificFunctions)
                else
                {

                    Predicate<AdditionalInformation> functionPredicate = c => query.Any(t => c.Company != null && c.Rights.Any(r => r == user.Function.Code));
                    all.Add(functionPredicate);
                }


                // There's also 4 groups like director group, administrative concil etc
                if (await _userManager.IsInRoleAsync(user, "CODIR"))
                {
                    Predicate<AdditionalInformation> CODIRPredicate = c => query.Any(t => c.Rights.Any(r => r == "CODIR"));
                    all.Add(CODIRPredicate);
                }

                if (await _userManager.IsInRoleAsync(user, "COMEX"))
                {
                    Predicate<AdditionalInformation> COMEXPredicate = c => query.Any(t => c.Rights.Any(r => r == "COMEX"));
                    all.Add(COMEXPredicate);
                }

                if (await _userManager.IsInRoleAsync(user, "CSE"))
                {
                    Predicate<AdditionalInformation> CSEPredicate = c => query.Any(t => c.Rights.Any(r => r == "CSE"));
                    all.Add(CSEPredicate);
                }

                if (await _userManager.IsInRoleAsync(user, "CA"))
                {
                    Predicate<AdditionalInformation> CSEPredicate = c => query.Any(t => c.Rights.Any(r => r == "CA"));
                    all.Add(CSEPredicate);
                }

                // On informations about users, we can check "Targeted person", and the person can see informations about him
                Predicate<AdditionalInformation> TargetPredicate = c => query.Any(t => c.SSTRNUser != null && c.SSTRNUserId == user.Id && c.Rights.Any(r => r == "OWNER"));
                all.Add(TargetPredicate);

                // The creator of the information can read the informations he posts..
                Predicate<AdditionalInformation> OwnerPredicate = c => query.Any(t => c.Creator.Id == user.Id);
                all.Add(OwnerPredicate);

                // The director and the assistant can read all informations
                if (user.Function.Code == "DIR" || user.Function.Code == "AS.DIR")
                {
                    all.Clear();
                    Predicate<AdditionalInformation> ADMINPredicate = c => query.Any(t => c.AdditionalInformationId != null);
                    all.Add(ADMINPredicate);
                }


                var items = query.Where(a => PredicateExtensions.OrAll(all)(a)).ToList();

                return Ok(new
                {
                    paging = new
                    {
                        pageNumber = pageNumber,
                        pageSize = pageSize,
                        totalItems = items.Count(),
                        pageCount = Math.Ceiling((double)items.Count / pageSize)
                    },
                    additionalInformations = _mapper.Map<List<DisplayAdditionalInformationViewModel>>(items.OrderByDescending(i => i.LastModificationDate).Skip(pageSize * (pageNumber - 1)).Take(pageSize))
                });





public static class PredicateExtensions
    {
        public static Predicate<T> Or<T>(this Predicate<T> p1, Predicate<T> p2)
        {
            return obj => p1(obj) || p2(obj);
        }

        public static Predicate<T> And<T>(this Predicate<T> p1, Predicate<T> p2)
        {
            return obj => p1(obj) && p2(obj);
        }
        public static Predicate<T> False<T>() { return obj => false; }
        public static Predicate<T> True<T>() { return obj => true; }

        public static Predicate<T> OrAll<T>(IEnumerable<Predicate<T>> conditions)
        {
            Predicate<T> result = PredicateExtensions.False<T>();
            foreach (Predicate<T> cond in conditions)
                result = result.Or<T>(cond);
            return result;
        }

        public static Predicate<T> AndAll<T>(IEnumerable<Predicate<T>> conditions)
        {
            Predicate<T> result = PredicateExtensions.True<T>();
            foreach (Predicate<T> cond in conditions)
                result = result.And<T>(cond);
            return result;
        }
    }

AddiInfo 类:

public class AdditionalInformation{
...

        private static readonly char delimiter = '¤';

        private string _rights;
        [NotMapped]
        public string[] Rights {
            get {
                if (string.IsNullOrEmpty(_rights)) {
                    return new List<string>().ToArray();
                } else {
                    return _rights.Split(delimiter);
                }
            }
            set
            {
                _rights = string.Join($"{delimiter}", value);
            }
        }
}

谢谢

Answer 1

关于此代码的一些事情(无顺序):

• .ToList().AsQueryable<AdditionalInformation>()永远不会做您想做的事情，即在数据库上运行查询。 ToList()将有效地将所有数据加载到内存中，然后一切都发生在内存中的数据上。这可能是此处性能问题的根源。
• 你的 PredicateExtensions在实际函数类型上工作，而不是在表达式上工作，因此您在那里构建的内容无法在数据库上执行(这意味着它将始终在内存中运行，这与 ToList() 具有相同的效果)。
• 不是收集谓词，而是收集谓词表达式并将它们组合为表达式。
• specificFunctions这里实际上不需要数组，因为您无论如何都要与每个单独的项目进行比较。
• UserManager.IsInRoleAsync每次调用将查询数据库两次，因此如果您想与多个角色进行比较，更好的办法是加载所有角色一次。
• 函数代码DIR和 AS.DIR清除所有先前构造的谓词，丢弃所有已完成的工作。因此，最好提前执行此操作并将逻辑短路。
• 与其再次加载用户，不如根本不加载它，而只是通过查询中的导航属性引用它。这样它就可以在那里执行。