我正在尝试在管理ASP网络核心应用程序上重新开发新闻系统。
我们可以发布有关用户或公司的附加信息，并且这些信息具有权利（允许用户使用其功能查看或不访问用户）。
我正在使用Entity Framework Core，并且在性能方面存在一些问题。.我是学生，我的代码对您来说肯定是可怕的^^
看到：
我试图删除ToList（），但它抛出的错误是另一个线程发出的另一个请求...我的DbContext生命周期是瞬态的
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);
}
}
}
谢谢
最佳答案
有关此代码的一些事项（无顺序）：.ToList().AsQueryable<AdditionalInformation>()永远不会做您想在数据库上运行查询的事情。 ToList()将有效地将所有数据加载到内存中，然后所有内容都会在内存中发生。这可能是造成性能问题的根源。
您的PredicateExtensions处理的是实际的函数类型，而不是表达式的处理，因此您在数据库中构造的内容无法在数据库上执行（这意味着它将始终在内存中运行，其作用与ToList()相同）。
代替收集谓词，而是收集谓词表达式并将它们组合为表达式。
实际上，这里实际上不需要specificFunctions数组，因为无论如何您都与每个项目进行比较。UserManager.IsInRoleAsync每次调用将查询数据库两次，因此，如果要与多个角色进行比较，一个更好的主意是将所有角色加载一次。
函数代码DIR和AS.DIR清除所有以前构造的谓词，从而丢弃所有已完成的工作。因此，最好先做一下，然后将逻辑电路短路。
不必再加载用户，只需加载查询中的导航属性即可引用它。这样就可以在那里执行它。
关于c# - 性能提升，谓词系统linq查询，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/57179942/