Linq where clause using filter object

Question

I have a piece of Linq that queries an EntityFramework context in my web controller and returns the result, as follows:

[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);
}

I want a new method that will take a "filter" object, where I can narrow down the results of the above. My filter object looks like this:

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; }
}

The minimum info that needs to be set in this class is BrandId. All other properties are options in the filter.

I need to write a new controller method that will utilise this, something like:

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

How do I linq query on properties that could potentially be null? Essentially, a dynamic query depending on the properties set in the filter object.

NOTE: I want this to affect the select statement that the EF runs, not just let EF get all the data, then filter the data set - the point of this is to make the db call more efficient.

Filter object might be sent where BrandId = 1, IsRuleActive = 1. Equally, it could be BrandId = 1, ServiceType = 3 (and therefore IsRuleActive is null so shouldn't be in the linq where clause).

I've tried this:

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 });

But the Where clause isn't applied to the generated Sql, it seems to pull back all records, then apply the where in memory, which isn't what I need.

Thanks in advance for any pointers!!

Answer 1

You can build an expression tree for this, but have you considered:

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

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

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

The EF engine is smart enough to combine the Where clauses (with AND of course).

Edit:

It wasn't clear if you wanted to filter on the joined result or only on the first table. In that case it would continue like

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

But I'm a little wary about that RoutingRulesDto(rr) constructor parameter.

Answer 2

Just to have my final solution in black and white, here's what I had in the end:

[HttpPost("filtered")]
public IActionResult GetFilteredRoutingRules([FromBody] RoutingSearchFilterDto filter)
{
    // Query to be build on the routing rules table.
    IQueryable<RoutingRules> query = _context.RoutingRules;

    // Populate the linked foreign key entities.
    query.Include(x => x.Hub).Include(y => y.DestinationZone).Include(z => z.OriginZone);

    // Build dynamic where statements.
    if (filter.BrandId != null)
        query = query.Where(r => r.BrandId == filter.BrandId);

    if (filter.OriginZoneId != null)            
        query = query.Where(r => r.OriginZoneId == filter.OriginZoneId);

    if (filter.DestinationZoneId != null)
        query = query.Where(r => r.DestinationZoneId == filter.DestinationZoneId);

    if (filter.IsRuleActive != null)
        query = query.Where(r => r.IsRuleActive == filter.IsRuleActive);

    if (filter.RuleRanking != null)
        query = query.Where(r => r.RuleRanking == filter.RuleRanking);

    // If you want to add paging:
    query = query.Skip(filter.PageSize * filter.PageNumber).Take(filter.PageSize);

    // Perform select on the table and map the results.
    var result = query.Select(r => new RoutingRulesDto
    {
        RoutingRuleId = r.RoutingRuleId,
        BrandId = r.BrandId,
        LastMileCarrierCode = r.LastMileCarrierCode,
        CashOnDelivery = r.CashOnDelivery,
        CreationTime = r.CreationTime,
        CurrencyCode = r.CurrencyCode,
        CurrencyDescription = Enum.Parse(typeof(Enumerations.CurrencyCode), r.CurrencyCode),
        DestinationZoneId = r.DestinationZoneId,
        EddFromDay = r.EddFromDay,
        EddToDay = r.EddToDay,
        ServiceType = r.ServiceType,
        ServiceTypeName = Enum.Parse(typeof(Enumerations.ServiceType), r.EshopServiceType),
        IsPickUpAvailable = r.IsPickUpAvailable,
        LastUpdateTime = r.LastUpdateTime,
        LastUpdateUser = r.LastUpdateUser,
        OriginZoneId = r.OriginZoneId,
        RuleRanking = r.RuleRanking,
        SignOnDelivery = r.SignOnDelivery,
        TermsOfDelivery = r.TermsOfDelivery,
        TermsOfDeliveryName = Enum.Parse(typeof(Enumerations.TermsOfDelivery), r.TermsOfDelivery),
        ValueOfGoods = r.ValueOfGoods,
        WeightLowerLimit = r.WeightLowerLimit,
        WeightUpperLimit = r.WeightUpperLimit,
        FirstMileCarrierCode = r.FirstMileCarrierCode,
        HubId = r.HubId,
        IsInsuranceAvailable = r.IsInsuranceAvailable,
        IsRuleActive = r.IsRuleActive,
        HubName = r.Hub.HubName,
        DestinationZoneName = r.DestinationZone.ZoneName,
        OriginZoneName = r.OriginZone.ZoneName,
    });

    // The SQL produced includes the joins and where clauses as well as only 
    // selecting the column names that are required in the flattened return object.

    return Ok(result);
}

Thanks for the help guys!

Answer 3

If you use the fluent API for LINQ, you can conditionally add Where clauses.

var query = _content.RoutingRules.Where(r => r.BrandId == filter.BrandId);
if (filter.OriginZoneId != null) {
    query = query.Where(r => r.OriginZoneId == filter.OriginZoneId);
}
if (filter.EshopServiceType != null) {
    query = query.Where(r => r.EshopServiceType == filter.EshopServiceType);
}
// etc...
var result = query.ToArray();