Use OrmLite select with nullable query parameters?

ServiceStack.OrmLite
1

I would like to replace AutoQuery with OrmLite to read SQL Server data (because AutoQuery is slow ( see https://forums.servicestack.net/t/autoquery-slow-on-200-column-query-150-rows/11840/7).

However, I need to handle nullable parameters and I cannot see how that can be easily done with OrmLite’s SELECT API.

Referring to the simplified code snippets below. The Completed query bool? parameter has three purposes:

  1. false: get incomplete transactions only.
  2. true: get completed transactions only.
  3. null: get all transactions (incomplete and completed).

I have similar needs for other nullable types (e.g. Guid, DateOnly, etc.).

So, when a query parameter is null it should not restrict the query (i.e. effectively be ignored).

This works perfectly using AutoQuery (shown below), but I cannot get the OrmLite Select API to work, nor find any examples of how to handle nullable parameters.

Any hints would be appreciated.

//Service Interface:
public class TransactionService : Service
{
    public IAutoQueryDb? AutoQuery { get; set; } = null;
    
    public CrudResponse<Transaction> Get(QueryTransaction request)
    {
        var response = GetTransactionsAsync(request.TransactionId, request.Completed);

        return new CrudResponse<Transaction>
        {
            Result = response.Result
        };
    }

    private async Task<List<Transaction>> GetTransactionsAsync(Guid? transactionId, bool? completed)
    {
        using var db = AutoQuery!.GetDb<Transaction>(Request);
        var find = new QueryTransaction { TransactionId = transactionId, Completed = completed };
        var q = AutoQuery.CreateQuery(find, Request, db);
        var result = await AutoQuery.ExecuteAsync(find, q, Request, db);
        return result.Results;
    }
}


//Service Model:
[Route("/api/Transaction", "GET")]
public class QueryTransaction : QueryDb<Transaction>, IGet
{
	public Guid? Id { get; init; }
	public bool? Completed { get; init; }
}


//Service Model Type:
[Schema("Transaction")]
[DataContract]
public record Transaction
{
    [DataMember, Required]
    public Guid Id { get; init; }

    [DataMember, Required]
    public bool Completed { get; init; }

    [DataMember]
    public string? Description { get; init; }
}

public class CrudResponse<T> : IHasResponseStatus where T : new ()
{
    public T Result { get; init; } = default!;
    public ResponseStatus? ResponseStatus { get; set; }
}
2

You’re using the same APIs AutoQuery uses so you should have access to the same functionality AutoQuery does.

You should check the HTTP Request you’re trying to replicate as it should provide better insight into what conditions it’s adding. E.g. The implicit convention to filter for null or not null is %IsNull and %IsNotNull which you should be able to add with:

public class QueryTransaction : QueryDb<Transaction>
{
	public Guid? Id { get; init; }
	public bool? Completed { get; init; }
	public bool? CompletedIsNull { get; init; }
}

Otherwise CreateQuery returns a populated OrmLite SqlExpression<Table> which you can further modify with custom conditions, e.g:

var q = AutoQuery.CreateQuery(find, Request, db);
if (completed == null)
{
    q.And(x => x.Completed == null);
}
3

My API is clear for the (internal) people that use it. there are also too many nullable parameters to add XIsNull properties as you suggest. This is for querying data for an internal reporting application.

Also, I am not looking for x.Completed == null. It is a query parameter that must be interpreted as no x.Completed condition when null. As I stated, I have no issues using AutoQuery, it is just too slow for my needs, as I described in the provided link.

If I have access to the same functionality as AutoQuery, what would be the db.Select OrmLite syntax that would generate the same SQL statement?

4

AutoQuery returns an OrmLite SqlExpression so this is the OrmLite syntax:

if (completed == null)
{
    q.And(x => x.Completed == null);
}

Split out into different statements:

var q = Db.From<Table>();
q.Where(x => x.Completed == null);
var results = Db.Select(q);
5

Maybe I am not explaining enough, using your code snippet, I want:

if (request.Completed == null)
{
    -- there should be NO condition for x.Completed
}
else 
{
    (x => x.Completed == request.Completed); -- false or true
}

But, I was hoping that I would not need to write explicit code to handle this scenario, as for AutoQuery.

6

I’m not following, if it was null no condition would be added so you can just add the condition when it’s not null:

if (request.Completed != null)
{
    q.Where(x => x.Completed == request.Completed);
}

As for AutoQuery, I’ve described above how it queries for null and not null with the implicit conventions.

7

Following on from the information provided.

I simply changed two lines, replacing the AutoQuery.CreateQuery with db.SelectAsync and acheived exactly what I wanted.

    private async Task<List<Transaction>> GetTransactionsAsync(Guid? transactionId, bool? completed)
    {
        using var db = AutoQuery!.GetDb<Transaction>(Request);
        var find = new QueryTransaction { TransactionId = transactionId, Completed = completed };
        var q = AutoQuery.CreateQuery(find, Request, db);

        // autoQuery
        //var result = await AutoQuery.ExecuteAsync(find, q, Request, db);
        //return result.Results;

        // OrmLite Select
        var results = db.SelectAsync(q);
        return result.Results;
    }

My C# query duration has gone from 53 seconds to 0.6 seconds, which matches nearly the database query duration. thank you for taking the time to help me improve my performance, very much appreciated, as always.

1 Like
8

That’s a pretty crazy difference, can’t really explain what would cause such a huge discrepancy. If you can provide the SQL AutoQuery generates for the slow 53s query (and row counts of tables queried) that may provide some insight on what may be causing it.

An invite to a protected github repo to @mythz will allow you to share logs with SQL & other info privately.