Performance improvement, predicate system linq queries

.net-core asp.net-core c# entity-framework-core

Question

I'm trying to redevelop a news system on a management asp net core app. We can post an additionnal information about a user or a company, and these informations works with rights (a user is allowed to see or no with his function).

I'm using Entity Framework Core, and I've some problems with performance.. I'm student, my code is certainly horrible for you ^^

See that :

I tried to remove the ToList(), but it's throwing an error than there's another request by another thread... MY DbContext Lifetime is 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 class:

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

Thank you

1
2
7/24/2019 9:45:20 AM

Accepted Answer

Some things about this code (in no order):

  • .ToList().AsQueryable<AdditionalInformation>() will never do what you would want to do which is run the query on the database. The ToList() will effectively load all the data into memory and everything then happens on that data in memory. That’s likely a source of performance problems here.
  • Your PredicateExtensions work on actual function types not on expressions, so what you are constructing there cannot be executed on the database (meaning that it will always run in memory, which has the same effect as the ToList()).
  • Instead of collecting predicates, collect predicate expressions and combine these as expressions.
  • The specificFunctions array isn’t actually needed here since you compare against each individual item anyway.
  • UserManager.IsInRoleAsync will query the database twice per call, so if you want to compare against multiple roles, a better idea would be to load all roles once.
  • The function codes DIR and AS.DIR clear all previously constructed predicates, throwing away all the work that has been done. So it’s a better idea to do that up front and short-circuit the logic.
  • Instead of loading the user again, don’t load it at all but just reference it through navigation properties within your query. That way it can be executed there.
3
7/24/2019 10:42:28 AM


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow