ef core linq过滤的子实体 [英] ef core linq filtered child entities

问题描述

我对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:

1. 如果没有a的结果，则不包括目标.似乎include或theninclude会创建内部联接，而不是左联接.
2. 这不会过滤语言或日期.

推荐答案

我建议您使用所需的属性创建一个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();
        }

这篇关于ef core linq过滤的子实体的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！