Sieve is a great tool, but it has an issue with SQL parameters. Fortunately, it’s easy to solve!

What is Sieve

First, let me introduce Sieve: it’s a lightweight tool for sorting, filtering and paging, mostly usable for GET queries in ASP.NET Core applications.

I strongly prefer it over solutions such as OData that seem too enterprise and complicated to me – plus they require you to pass IQueryables to your controllers, which can siginificantly affect your architecture, caching etc.

To use, you just need to annotate properties in your models:

public class Book
{
    public int Id { get; set; }

    [Sieve(CanFilter = true, CanSort = true)]
    public string Name { get; set; }

    public string Author { get; set; }

    [Sieve(CanFilter = true, CanSort = true)]
    public int YearPublished { get; set; }
}

Then accept SieveModel as a parameter in your endpoints and apply it to your queries:

[HttpGet]
public async Task<ActionResult<IEnumerable<Book>>> Get([FromQuery] SieveModel sieveModel)
{
    var books = dbContext.Books.AsNoTracking();

    var filtered = await sieveProcessor.Apply(sieveModel, books).ToListAsync();

    return Ok(filtered);
}

Problem

We want to filter the books from our database based on the year they were published, so we add the query to our URL and send a GET request:

api/books?filters=yearPublished>1900

Now, let’s look at the SQL query that was generated.

Note: You can see the SQL queries in the application output if you add the following section to your appsettings.json:

"Logging": {
    "LogLevel": {
        "Microsoft.EntityFrameworkCore.Database.Command": "Information"
    }
}

The generated SQL query is:

SELECT "e"."Id", "e"."Author", "e"."Name", "e"."YearPublished"
FROM "Books" AS "e"
WHERE "e"."YearPublished" > 1900

Did you spot the problem? It’s that 1900 which definitely shouldn’t be there for two reasons:

1. Security: Filter parameter might contain sensitive data and those data shouldn’t appear in logs.
2. Performance: EF Core caches the generated queries. Unfortunately, each time you use the same filter with a different parameter, it’s a different expression and requires a new query to be generated.

What we want here is that the filter parameter is also passed as a parameter to the SQL query.

Solution

The trouble happens when SieveProcessor processes the query. This involves creation of expressions and constant are just not handled properly.

What’s funny is that there is a comment above the troubled method which says that the method should actually solve the problem we are trying to solve here! Apparently, it doesn’t.

The only thing we need to do to fix it is to replace the rwo with Expression.Constant:

private Expression GetClosureOverConstant<T>(T constant, Type targetType)
{
    return Expression.Constant(constant, targetType);
}

With the following:

private Expression GetClosureOverConstant<T>(T constant, Type targetType)
{
    Expression<Func<T>> hoistedConstant = () => constant;
    return Expression.Convert(hoistedConstant.Body, targetType);
}

And voila! The SQL query now has a parameter:

SELECT "e"."Id", "e"."Author", "e"."Name", "e"."YearPublished"
FROM "Books" AS "e"
WHERE "e"."YearPublished" > @__constant_0

The solution is from this article about investigating memory leaks in EF Core – it also talks about the caching issue mentioned above and I definitely recommend it.

I have already created a Github issue and a pull request. However, it seems that the owner of the repository is not very active. Until the PR gets merged, you can fork the repo and use this fix to resolve the issue for yourself.

Last modified on 2021-02-26