Passing in a list of items (that can possibly be null) as params for an IN clause using Dapper

Question

I am getting a NULL Reference Exception when trying to pass a NULL list of items as a parameter using Dapper. Normally in my where clause I would simply do the following:

"AND (@Sections IS NULL OR Section IN @Sections)";

But I am unable to do this because it won't work even when there are items in the sections list. Dapper adds them as parameters and (@sections1,@sections2 IS NULL OR) will error out. If I leave my sections list null because I don't want to use it as a filter I get a NULL reference exception.

My function must have a list of sections as an optional parameter. This way in my code I do not always have to add sections filter to my query. How can I make sections a nullable list in my function params but also work with Dapper when NULL?

Here is my code:

public static IEnumerable<Product> GetProformaFormularies(int? categoryId = null, IEnumerable<int> sections = null)
{
    using (var context = new AppContext())
    {
        var sql =
        "SELECT * " +
        "FROM Products " +
        "WHERE (@CategoryId IS NULL OR CategoryId = @CategoryId) " +
          "AND (Section IN @Sections)";

        return context.Database.Connection.Query<Product>(sql, 
        new { 
                CategoryId = categoryId,
                Sections = sections
            }).ToList();
    }
}

The only solution that I came up with is using Dynamic Parameters. Is there a better way than this?

var sql =
    "SELECT * " +
    "FROM ProformaFormulary " +
    "WHERE (@CategoryId IS NULL OR CategoryId = @CategoryId) " +

if (sections != null)
{
    sql += " AND (Section IN @Sections)";
}

var parameters = new DynamicParameters();
parameters.Add("@CategoryId", categoryId);
if (sections != null)
{
    parameters.Add("@Sections", sections);
}

Answer 1

You could just omit that portion of the WHERE clause if sections is null:

var sql =
    "SELECT * " +
    "FROM Products " +
    "WHERE (@CategoryId IS NULL OR CategoryId = @CategoryId) ";

if (sections != null)
{
    sql += "AND (Section IN @Sections)"
}

return context.Database.Connection.Query<Product>(sql, 
    new { 
            CategoryId = categoryId,
            Sections = sections
        }).ToList();

It looks like dapper will just ignore the Sections property on the object you're passing if it doesn't apply.

Answer 2

In my opinion the if statement in code is not what you want sometimes. I sometimes just execute .sql files. So the only thing that worked for me is this.

var parameters = new
            {
                ApplySectionFilter = stringsToFilter != null,
                Sections = stringsToFilter 
            };

There is a bit overhead, but in the end, it's more clean. In my opinion.

SELECT * FROM Products
    WHERE (@ApplySectionFilter = 0 OR Section IN @Sections)