.net 用于创建具有内部Collection的where predicate 的动态查询

nwlls2ji  于 2023-02-14  发布在  .NET
关注(0)|答案(2)|浏览(133)

我正在为我的MVCEF应用程序创建搜索功能。我正在使用动态查询创建它。并遵循以下方法https://www.codeproject.com/Articles/493917/Dynamic-Querying-with-LINQ-to-Entities-and-Express
它用于为实体的boolstring字段创建 predicate 。我的应用中的主实体是Applicant
以下是EDMX Applicant

public partial class Applicant
    {

      public Applicant()
       {
         this.ApplicantEducations = new HashSet<ApplicantEducation>();
         this.ApplicantSkills = new HashSet<ApplicantSkill>();
         this.Applications = new HashSet<Application>();
         this.Experiences = new HashSet<Experience>();
        }

    public int Id { get; set; }
    public string Name { get; set; }
    public string Description { get; set; }
    public byte[] CV_Upload1 { get; set; }
    public string CV_Upload2 { get; set; }
    public string email { get; set; }
    public string password { get; set; }
    public Nullable<System.DateTime> DOB { get; set; }

   virtual ICollection<ApplicantEducation> ApplicantEducations { get; set; }
   virtual ICollection<ApplicantSkill> ApplicantSkills { get; set; }
   virtual ICollection<Application> Applications { get; set; }
   virtual ICollection<Experience> Experiences { get; set; }
}

我想搜索,即与机构的名称,这是提交在类型的研究所的申请人教育。申请人可以有一个或多个申请人教育对象。
以下是我的应用程序教育的EDMX类

public partial class ApplicantEducation
{
    public int id { get; set; }
    public Nullable<int> ApplicantId { get; set; }
    public Nullable<int> InstituteId { get; set; }
    public Nullable<int> EducationLevelId { get; set; }
    public Nullable<bool> IsComplete { get; set; }
    public Nullable<System.DateTime> DateStart { get; set; }
    public Nullable<System.DateTime> DateEnd { get; set; }
    public Nullable<short> GPA { get; set; }

    public virtual EducationLevel EducationLevel { get; set; }
    public virtual Institute Institute { get; set; }
    public virtual Applicant Applicant { get; set; }
}

我的Institute实体类是这样的

public class Institute
  {
         public int Id { get; set; }
         public string Name { get; set; }

  }

因此,用户将通过指定机构名称进行搜索,所有申请人将从该机构获得教育检索。
正如我上面提到的链接。下面的例子演示了字符串字段 predicate 的构建

private static Expression<Func<TDbType, bool>> ApplyStringCriterion<TDbType,
        TSearchCriteria>(TSearchCriteria searchCriteria, PropertyInfo searchCriterionPropertyInfo,
        Type dbType, MemberInfo dbFieldMemberInfo, Expression<Func<TDbType, bool>> predicate)
    {
        // Check if a search criterion was provided
        var searchString = searchCriterionPropertyInfo.GetValue(searchCriteria) as string;
        if (string.IsNullOrWhiteSpace(searchString))
        {
            return predicate;
        }
        // Then "and" it to the predicate.
        // e.g. predicate = predicate.And(x => x.firstName.Contains(searchCriterion.FirstName)); ...
        // Create an "x" as TDbType
        var dbTypeParameter = Expression.Parameter(dbType, @"x");
        // Get at x.firstName
        var dbFieldMember = Expression.MakeMemberAccess(dbTypeParameter, dbFieldMemberInfo);
        // Create the criterion as a constant
        var criterionConstant = new Expression[] { Expression.Constant(searchString) };
        // Create the MethodCallExpression like x.firstName.Contains(criterion)
        var containsCall = Expression.Call(dbFieldMember, StringContainsMethod, criterionConstant);
        // Create a lambda like x => x.firstName.Contains(criterion)
        var lambda = Expression.Lambda(containsCall, dbTypeParameter) as Expression<Func<TDbType, bool>>;
        // Apply!
        return predicate.And(lambda);
    }

以上代码用于为包含在主实体类(申请人)中的简单字符串字段构建 predicate 。但是申请人也有ApplicationEducation集合,所以我的问题是如何为linq的where子句(方法)创建动态查询( predicate ),以便当用户搜索学院名称时,检索到的所有申请人都具有相同的教育背景。
我的搜索条件如下所示,

public class SearchCriteriaVM
{
    public int Id { get; set; }
    public string Name { get; set; }
    public DateTime? DOB { get; set; }     
    public string Description { get; set; }

    public ICollection<Models.ApplicantEducationVM> ApplicantEducations { get; set; }
    public ICollection<Models.ExperienceVM> Experiences { get; set; }
    public ICollection<Models.ApplicantSkillsVM> ApplicantSkills { get; set; }

    public ICollection<Models.ApplicationsVM> Applications { get; set; }


}

我有点搞不懂这怎么可能。
谢谢

nr7wwzry

nr7wwzry1#

在您的案例中,我们需要的基本内容是使用EF的动态查询构建器。即,包含IQueryable格式数据的基本“Match”方法、搜索项和属性(通过这些属性过滤记录)。“Match”方法是我们需要在代码中使用的方法。

public static IQueryable<T> Match<T>(
    IQueryable<T> data,
    string searchTerm,
    IEnumerable<Expression<Func<T, string>>> filterProperties)
{
    var predicates = filterProperties.Select(selector =>
            selector.Compose(value => 
                value != null && value.Contains(searchTerm)));
    var filter = predicates.Aggregate(
        PredicateBuilder.False<T>(),
        (aggregate, next) => aggregate.Or(next));
    return data.Where(filter);
}

要构建这个表达式方法,我们需要一个***Compose***方法,以便它能够接受需要搜索的参数。

public static Expression<Func<TFirstParam, TResult>>
    Compose<TFirstParam, TIntermediate, TResult>(
    this Expression<Func<TFirstParam, TIntermediate>> first,
    Expression<Func<TIntermediate, TResult>> second)
{
    var param = Expression.Parameter(typeof(TFirstParam), "param");

    var newFirst = first.Body.Replace(first.Parameters[0], param);
    var newSecond = second.Body.Replace(second.Parameters[0], newFirst);

    return Expression.Lambda<Func<TFirstParam, TResult>>(newSecond, param);
}

这将组合并返回一个lambda表达式,但要构建此方法,我们需要“Replace”扩展方法。顾名思义,此方法将把一个表达式的所有示例替换为另一个表达式。

public static Expression Replace(this Expression expression,
    Expression searchEx, Expression replaceEx)
{
    return new ReplaceVisitor(searchEx, replaceEx).Visit(expression);
}
internal class ReplaceVisitor : ExpressionVisitor
{
    private readonly Expression from, to;
    public ReplaceVisitor(Expression from, Expression to)
    {
        this.from = from;
        this.to = to;
    }
    public override Expression Visit(Expression node)
    {
        return node == from ? to : base.Visit(node);
    }
}

回到实际的“Match”方法,我们需要一个 predicate 构建器来帮助我们呈现与搜索相关的AND、OR查询。
因此, predicate 构建器将如下所示:

public static class PredicateBuilder
{
    public static Expression<Func<T, bool>> True<T>() { return f => true; }
    public static Expression<Func<T, bool>> False<T>() { return f => false; }

    public static Expression<Func<T, bool>> Or<T>(
        this Expression<Func<T, bool>> expr1,
        Expression<Func<T, bool>> expr2)
    {
        var secondBody = expr2.Body.Replace(
            expr2.Parameters[0], expr1.Parameters[0]);
        return Expression.Lambda<Func<T, bool>>
              (Expression.OrElse(expr1.Body, secondBody), expr1.Parameters);
    }

    public static Expression<Func<T, bool>> And<T>(
        this Expression<Func<T, bool>> expr1,
        Expression<Func<T, bool>> expr2)
    {
        var secondBody = expr2.Body.Replace(
            expr2.Parameters[0], expr1.Parameters[0]);
        return Expression.Lambda<Func<T, bool>>
              (Expression.AndAlso(expr1.Body, secondBody), expr1.Parameters);
    }
}

因此,我们需要的是一个Match方法,它将按照您的要求运行。
请让我知道,如果你需要任何进一步的援助,在这方面,根据您的模型结构。

8cdiaqws

8cdiaqws2#

您可以使用以下方法在Lambda表达式 * 中创建 * 动态Where子句:

public ActionResult GetRecords(int? classId, string name, bool isAll = false)
{
    var allRecords = repository.Students;

    if (!isAll)
    {
        //Retrieve active records only
        allRecords = allRecords.Where(m => m.StatusId == 1);
    }
    if (!string.IsNullOrEmpty(name))
    {
        allRecords = allRecords.Where(m => m.Name.StartsWith(name));
    }
    if (classId.HasValue)
    {
        allRecords = allRecords.Where(m => m.ClassId == classId);
    }
    // other stuff
}

类似地,为了仅检索以 “query” 参数值开始的记录并且如果 “query” 参数值为空则检索所有记录,可以应用以下方法:

IQueryable<StudentViewModel> students = repository.Students.Select(m => 
    new StudentViewModel
{
    Id = m.Id,
    Name = m.Name + " " + m.Surname
});
if (!string.IsNullOrEmpty(query))
{
    students = students.Where(m => m.Name.StartsWith(query));
}

或者用另一种方式来形容“表现不佳”:

.Where(m => string.IsNullOrEmpty(query) || m.Name.StartsWith(query));

相关问题