Generic Filtering with Ormlite

A Practical example of using Func

I use the ServiceStack framework, and for me it’s worth every single penny. The sheer effort that goes into this framework is mind boggling, to the point where it can sometimes be a little tricky to find what you are looking for. That’s not a criticism, it’s a reflection of the sheer power of the framework.

Ormlite is the ORM used by ServiceStack and for a lightweight ORM it’s pretty powerful, it can certainly do pretty much anything I need it to. As with everything ServiceStack it’s pretty easy to setup and use. Assuming you have it configured it can connect to a variety of databases and works pretty logically by mapping a table to a DTO.

I had a simple, single table that I wanted to query by different fields e.g. Post code, case reference etc. so my query looks something like this

public IEnumerable<SRequest> Search(string postCode)
                    {
List<SRequest> data;
var dbFactory = new OrmLiteConnectionFactory(
                            DBConnection,
                            SqlServerOrmLiteDialectProvider.Instance);
using (var db = dbFactory.OpenDbConnection())
                            {
                                data = db.Select<SRequest>(x => x.PostCode == postCode);
                            }
return data.AsEnumerable();
                    }
public IEnumerable<SRequest> Search(int caseReference)
                    {
List<SRequest> data;
var dbFactory = new OrmLiteConnectionFactory(
                            DBConnection,
                            SqlServerOrmLiteDialectProvider.Instance);
using (var db = dbFactory.OpenDbConnection())
                            {
                                data = db.Select<SRequest>(x => x.CaseReference == caseReference);
}
return data.AsEnumerable();
                    }

And that’s great, it’s simple and pretty easy to understand, however I then needed to add another filter on a different field again. Clearly a simple cut and paste with a few modications would suffice.

If you’re like me you’re now screaming at the screen that this is all basically duplicated code and it can be simplified as all that changes is the filter. Fortunately C# has the Func delegate and this can be used to create a filter to simplify our code further. Note that this is part of .Net Standard so can be used in core or framework code.

In order to make the simplifications we want to, then we need to declare a Func in each method e.g.

var filter = new Func<SRequest, bool>(x => x.PostCode == postCode);

Firstly note that this filter is the same as the lambda in our Select statement above. Secondly the Func has two elements — the SRequest which is the type of object we are interested in and secondly a bool. As the bool is the last item in the list it’s the one that’s returned. In this case it returns true if x.PostCode == postCode, false otherwise. As we have declared the SRequest object type, intellisense kicks in and allows us to select the PostCode property when we are writing this line, which is a very nice touch.

Now we have our fillter declared we can write out generic query function e.g.

private List<SRequest> Query(Func<SRequest,bool> filter)
                    {
                        var dbFactory = new OrmLiteConnectionFactory(
                            DBConnection,
                            SqlServerOrmLiteDialectProvider.Instance);
IEnumerable<SRequest> data;
using (var db = dbFactory.OpenDbConnection())
                            {
                                data = db.Select<SRequest>().Where(filter);
Log.Debug($@"{db.GetLastSql()}");
                            }
return data.ToList();
                    }

So now we have our filter parameter used in a where statement so we can send whatever filter we require, it can be as complex as we like. You may also notice I’ve left in a line for debugging. While we are still within our db context, after the call we can find the SQL that Ormlilte generated using

db.GetLastSql()

This is incredibly useful if your SQL isn’t producing what you expect and note that this works regardless of database type, not just SQL Server that I am using.

So now we have made the filter generic our code looks something like this

public IEnumerable<SRequest> Search(string postCode)
                    {
var filter = new Func<SRequest, bool>(x => x.PostCode == postCode);
return Query(filter).AsEnumerable();
                    }
public IEnumerable<SRequest> Search(int caseReference)
                    {
var filter = new Func<SRequest, bool>(x => x.CaseReference == caseReference);
return Query(filter).AsEnumerable();
}
private List<SRequest> Query(Func<SRequest,bool> filter)
                    {
                        var dbFactory = new OrmLiteConnectionFactory(
                            DBConnection,
                            SqlServerOrmLiteDialectProvider.Instance);
IEnumerable<SRequest> data;
using (var db = dbFactory.OpenDbConnection())
                            {
                                data = db.Select<SRequest>().Where(filter);
                            }
return data.ToList();
                    }

This means I can add as many new filters as I like in only a few lines of code.

Async Querying

Converting this to an Async Query proved more tricky than expected. My first attempt at a QueryAsync method looked something like this

private async Task<List<SRequest>> QueryAsync(Func<SRequest,bool> filter)
                    {
                        var dbFactory = new OrmLiteConnectionFactory(
                            DBConnection,
                            SqlServerOrmLiteDialectProvider.Instance);
IEnumerable<SRequest> data;
using (var db = dbFactory.OpenDbConnection())
                            {
                                data = await db.SelectAsync<SRequest>().Where(filter);
}
return data.ToList();
                    }

but this gave an error that seemed to be related to the where statement. The solution to this isn’t obvious, it is however to create an Expression such as this

Expression<Func<SRequest, bool>> filter = x => x.PostCode == postCode;

which can then be passed to the new QueryAsync method which looks something like this

private async Task<List<SRequest>> QueryAsync(Expression<Func<SRequest, bool>> filter)
                    {
                        var dbFactory = new OrmLiteConnectionFactory(
                            DBConnection,
                            SqlServerOrmLiteDialectProvider.Instance);
List<SRequest> data;
                        using (var db = dbFactory.OpenDbConnection())
                            {
                                data = await db.SelectAsync(filter).ConfigureAwait(false);
                            }
return data.ToList();
                    }