c# - 使用过滤器对象的 Linq where 子句

Question

我有一段 Linq 查询我的网络 Controller 中的 EntityFramework 上下文并返回结果，如下所示:

[HttpGet]
public IActionResult GetRoutingRules()
{
     var query = (from rr in _context.RoutingRules
          join dest in _context.RoutingZones on rr.DestinationZoneId equals dest.ZoneId
          join origin in _context.RoutingZones on rr.OriginZoneId equals origin.ZoneId
          join hub in _context.RoutingHub on rr.HubId equals hub.HubId
          select new RoutingRulesDto(rr) { DestinationZoneName = dest.ZoneName, OriginZoneName = origin.ZoneName, HubName = hub.HubName });

     return Ok(query);
}

我想要一个新的方法，它将采用一个“过滤器”对象，我可以在其中缩小上述结果的范围。我的过滤器对象如下所示:

public class RoutingSearchFilterDto
{
     public int BrandId { get; set; }
     public int? ServiceType { get; set; }
     public long? OriginZoneId { get; set; }
     public long? DestinationZoneId { get; set; }
     public int? RuleRanking { get; set; }
     public bool? IsRuleActive { get; set; }
}

该类最少需要设置的信息是BrandId。所有其他属性都是过滤器中的选项。

我需要编写一个新的 Controller 方法来利用它，例如:

[HttpPost("filtered")]
public IActionResult GetFilteredRoutingRules([FromBody] RoutingSearchFilterDto filter)
{
    ...
}

如何对可能为空的属性进行 linq 查询？本质上，动态查询取决于过滤器对象中设置的属性。

注意:我希望这会影响 EF 运行的 select 语句，而不仅仅是让 EF 获取所有数据，然后过滤数据集 - 这样做的目的是提高 db 调用的效率。

过滤器对象可能会发送到 BrandId = 1、IsRuleActive = 1 的位置。同样，它可能是 BrandId = 1、ServiceType = 3(因此 IsRuleActive 为空，因此不应出现在 linq where 子句中)。

我已经试过了:

var param = (Expression.Parameter(typeof(RoutingRules), "rr"));

Expression combinedExpr = null;
if (filter.BrandId != null)
{
    var exp = Expression.Equal(Expression.Property(param, "BrandId"), Expression.Constant(filter.BrandId));
    combinedExpr = exp;
}

if (filter.DestinationZoneId != null)
{
    var exp = Expression.Equal(Expression.Property(param, "DestinationZoneId"), Expression.Constant(filter.DestinationZoneId));
    combinedExpr = (combinedExpr == null ? exp : Expression.AndAlso(combinedExpr, exp));
}

if (filter.OriginZoneId != null)
{
    var exp = Expression.Equal(Expression.Property(param, "OriginZoneId"), Expression.Constant(filter.OriginZoneId));
    combinedExpr = (combinedExpr == null ? exp : Expression.AndAlso(combinedExpr, exp));
}

if (filter.EshopServiceType != null)
{
    var exp = Expression.Equal(Expression.Property(param, "EshopServiceType"), Expression.Constant(filter.EshopServiceType));
    combinedExpr = (combinedExpr == null ? exp : Expression.AndAlso(combinedExpr, exp));
}

if (filter.IsRuleActive != null)
{
    var exp = Expression.Equal(Expression.Property(param, "IsRuleActive"), Expression.Constant(filter.IsRuleActive, typeof(bool?)));
    combinedExpr = (combinedExpr == null ? exp : Expression.AndAlso(combinedExpr, exp));
}

if (filter.RuleRanking != null)
{
    var exp = Expression.Equal(Expression.Property(param, "RuleRanking"), Expression.Constant(filter.RuleRanking));
    combinedExpr = (combinedExpr == null ? exp : Expression.AndAlso(combinedExpr, exp));
}

if (combinedExpr == null)
    combinedExpr = Expression.Default(typeof(bool));

var compiled = Expression.Lambda<Func<RoutingRules, bool>>(combinedExpr, param).Compile();


var results = (from rr in _context.RoutingRules.Where(compiled)
                join dest in _context.RoutingZones on rr.DestinationZoneId equals dest.ZoneId
                join origin in _context.RoutingZones on rr.OriginZoneId equals origin.ZoneId
                join hub in _context.RoutingHub on rr.HubId equals hub.HubId
                where rr.BrandId == 21
                select new RoutingRulesDto(rr) { DestinationZoneName = dest.ZoneName, OriginZoneName = origin.ZoneName, HubName = hub.HubName });

但是Where子句并没有应用到生成的Sql中，好像是拉回所有记录，然后应用内存中的where，这不是我需要的。

在此先感谢您的指点!!

Answer 1

您可以为此构建一个表达式树，但您是否考虑过:

IQueryable<...> query = ...;

if (routingSearchFilter.ServiceType != null)
  query = query.Where(e => e.ServiceType == routingSearchFilter.ServiceType);

if (...) 
   query = query.Where(....);

EF 引擎足够智能，可以组合 Where 子句(当然还有 AND)。

编辑:

不清楚您是想过滤连接结果还是只过滤第一个表。在那种情况下它会继续像

var result = (from rr in query
          join dest in _context.RoutingZones on rr.DestinationZoneId equals dest.ZoneId
          join ...
          select new RoutingRulesDto(rr) .... ).ToSometing();

但我对 RoutingRulesDto(rr) 构造函数参数有点担心。