OR statements with null parameters

ServiceStack.OrmLite
1

I’m just getting started with ORMLite and am not super familiar with the API so please bear with me if this is a simple question.

I typically write my filter queries using the following pattern:

create procedure FindThings
   @field1Filter varchar(50) = null
   @field2Filter varchar(50) = null
as
begin

   select *
   from MyTable
   where (@field1Filter is null or Field1 = @field1Filter)
      and (@field2Filter is null or Field2 = @field2Filter)

end

Is there an equivalent way to achieve this in ORMLite other than writing it out as a stored procedure and then calling the stored procedure? This is specifically for SQLServer

2

You can use a typed SQL Expression for this:

string field1Filter = ...
string field2Filter = ...

var results = db.Select<Table>(q =>
    q.Where(x => field1Filter == null || x.Field1 == field1Filter)
       .And(x => field2Filter == null || x.Field2 == field2Filter));

There’s also API’s that accept Custom SQL that you could use.

1 Like
3

Excellent thanks!

I ended up needing to make some modifications to the query you provided but was able to get it working. Kept getting a cannot convert lambda expression because it is not a delegate type on the q.Where and wasn’t able to find a fix.

I ended up with

 Db.Select<MyTable>(q => 
     (request.Field1Filter == null || q.Field1 == request.Field1Filter) &&
     (request.Field2Filter == null || q.Field2 == request.Field2Filter));

Thanks again!

4

ok no worries, although I’m not sure what issue you ran into, I’ve added a working example of this in this commit:

using (var db = OpenDbConnection())
{
    db.DropAndCreateTable<Person>();
    db.InsertAll(Person.Rockstars);

    var firstName = "Kurt";
    string lastName = null;

    var results = db.Select<Person>(q =>
        q.Where(x => firstName == null || x.FirstName == firstName)
            .And(x => lastName == null || x.LastName == lastName));

    db.GetLastSql().Print();

    results.PrintDump();

    Assert.That(results[0].LastName, Is.EqualTo("Cobain"));
}