ef core linq过滤的子实体

本文介绍了ef core linq过滤的子实体的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我对linq查询有疑问. 我有3个实体: 用户，目标和结果. 每个用户可以有多个(或没有)目标，每个目标可以有多个(或没有结果) 我想要一个返回所有用户的查询，包括可能的目标和可能的结果.而且效果很好.但是现在我想包括过滤器以过滤目标和结果.因此查询只返回符合这些条件的用户，目标和结果.

I have a problem with a linq query. I have 3 entities: user, target and results. each user can have multiple (or no) targets and each target can have multiple (or no) results I want a query that returns all users including possible targets and possible results. And that's working great. But now I want to include filters to filter the targets and results. So that the query only returns users, targets and results matching these criteria.

public class User { public ICollection Targets {get;set;} public string otherProperty {get;set;} } public class Target { public ICollection Results {get;set;} public User user {get;set;} public string Language {get;set;} } public class Result { public Target Target {get;set;} public int score {get;set;} }

有什么可以帮助我的EF核心linq专家吗?

Any EF core linq specialists that can help me?

亲切的问候， 罗布雷希特

Kind regards, Robrecht

编辑1

var query = from auditUser in _auditUserRepository.GetAll().Include(u => u.user) .WhereIf(!input.Group.IsNullOrWhiteSpace(), u => u.Group == input.Group) .WhereIf(!input.Filter.IsNullOrWhiteSpace(), u => u.user.FullName.ToLower().Contains(input.Filter.ToLower())) select auditUser; var results = query .Include(u => u.Targets) .ThenInclude(t => t.AuditResults) .PageBy(input) .ToListAsync(); await query .SelectMany(u => u.Targets) .WhereIf(!input.Language.IsNullOrWhiteSpace(), t => t.Target == input.Language) .SelectMany(t => t.AuditResults) .WhereIf(input.From != null, r => r.CompletionDate >= input.From) .WhereIf(input.To != null, r => r.CompletionDate <= input.From) .LoadAsync();

这就是我想要的，但是有两个问题:

This is what I have so for but it has 2 problems:

如果没有a的结果，则不包括目标.似乎include或theninclude会创建内部联接，而不是左联接.

这不会过滤语言或日期.

推荐答案

我建议您使用所需的属性创建一个ViewModel类.示例:

I suggest you create a ViewModel class with the properties you want. Example:

public class UserViewModel { [Display(Name = "Other")] public string otherProperty {get; set;} [Display(Name = "Possible Targets")] public List<Target> targets {get; set;} [Display(Name = "Possible Results")] public List<Result> results{get; set;} }

然后在存储库"类中，您可以创建一种方法来过滤结果.

Then in your "repository" class you can create a method to filter the results.

public List<UserViewModel> GetUserViewModelBy(int scoreFilter, string filter1= "", string filter2) { IQueryable<Result> query = _context.Results.Where(i => i.score==scoreFilter)).Include(x => x.Target) .Include(x => x.Target.Results.ToList()) .Include(x => x.User) .Include(x=>x.User.Targets.Where(i=>i.Language.ToLower().Contains(filter1)).ToList()); if (!string.IsNullOrEmpty(filter2)) { query = query.Where(x => x.Target.Language.ToLower().Contains(filter2)); } return query.Select(x => new UserViewModel() { otherProperty = x.User.otherProperty, targets = x.User.targets, results = x.Results }).ToList(); }