Building up a where filter from a message based request

Question

I have a small request object to filter by.

public class BufferFlatViewFilter
{
    public bool? Active { get; set; }
    public int? CustomerId { get; set; }
    public int? TypeId { get; set; }
}

And I need to build up the where filter for the db request.

With Entity framework I was able to build up the Where Statement like so.

public List<BufferFlatView> GetBufferFlatView(BufferFlatViewFilter filter)
{
    var data = db.qryCampaignOverView
        .AsQueryable();

    if (filter.TypeId.HasValue)
        data = data.Where(x => x.TypeId == filter.TypeId.Value);

    if (filter.CustomerId.HasValue)
        data = data.Where(x => x.CustomerId == filter.CustomerId);

    if (filter.Active)
        data = data.Where(x => x.Active == filter.Active);

    return data.ToList();
}

I'm not sure of how to build up the where statement for OrmLight given that Entity framework query is lazy loaded but not the OrmLight query.

Answer 1

We recently added expression chaining in the OrmLite ExpressionVisitor - code copy & pasted from the unit tests:

var visitor = dbConnection.CreateExpression<Person>();
visitor.Where(x => x.FirstName.StartsWith("Jim")).And(x => x.LastName.StartsWith("Hen"));
var results = db.Select<Person>(visitor);
Assert.AreEqual(1,results.Count);

visitor.Where(x => x.Age < 30).Or(x => x.Age > 45);
results = db.Select<Person>(visitor);
Assert.AreEqual(5, results.Count);
Assert.IsFalse(results.Any(x => x.FirstName == "Elvis"));

Note: Where(x => predicate) and .And(x => predicate) are functionally the same.

You can also build up your Order By expression

visitor.OrderBy(x => x.Name).ThenByDescending(x => x.Age);

So your code becomes

public List<BufferFlatView> GetBufferFlatView(BufferFlatViewFilter filter)
{
    //assumes IDbConnection instance injected by IOC
    var ev = dbConnection.CreateExpression<Campaign>();

    if (filter.TypeId.HasValue)
        ev.Where(x => x.TypeId == filter.TypeId.Value);

    if (filter.CustomerId.HasValue)
        ev.Where(x => x.CustomerId == filter.CustomerId);

    if (filter.Active)
        ev.Where(x => x.Active == filter.Active);

    return dbConnection.Select<Campaign>(ev);

}