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

c# dapper sql sql-server sql-server-2008

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);
}

Accepted Answer

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.


Popular Answer

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)


Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why