How do I add multiple "WHERE conditionA OR conditionB OR conditionC" in SQL parameter?

Question

I am doing pagination. What I have now is:

public void GetAllDevices(int siteId, List<int> deviceTypes, int rowsOffset, int pageSize)
{
    var param = new
    {
        SiteId = siteId,
        RowsOffset = rowsOffset,
        PageSize = pageSize
    };

    string sqlQuery = @"SELECT * FROM device WHERE site_id = @SiteId
                        ORDER BY site_id
                        OFFSET @RowsOffset ROWS
                        FETCH NEXT @PageSize ROWS ONLY";

    var response = await conn.QueryAsync<Model.Device>(sqlQuery, param);
}

All pretty standard stuffs. Now, the user can also filter many times on the device's type, so if I am going to write raw SQL it would look like this:

SELECT * FROM device WHERE site_id = @SiteId
WHERE device_type_id = 1 OR device_type_id = 2 OR device_type_id = 3
ORDER BY site_id
OFFSET @RowsOffset ROWS
FETCH NEXT @PageSize ROWS ONLY

My problem is how do I write that WHERE clause's param? What do I add here:

var param = new
{
    SiteId = siteId,
    RowsOffset = rowsOffset,
    PageSize = pageSize
};

I'm using Dapper and PostgreSQL 13, running on .NET5.

Additional information: See the accepted answer on how to do this. Be aware that it would work for SQL Server but not PostgreSQL. PostgreSQL does not support "IN", so you have to do "ANY" instead. Refer: Dapper.net "where ... in" query doesn't work with PostgreSQL

squillman · Accepted Answer · 2021-08-27T15:04:57.683

You just use more parameters:

SELECT *
FROM device WHERE site_id = @SiteId
AND (device_type_id = @DeviceTypeID1
    OR device_type_id = @DeviceTypeID2
    OR device_type_id = @DeviceTypeID3)
ORDER BY site_id
OFFSET @RowsOffset ROWS
FETCH NEXT @PageSize ROWS ONLY

var param = new
{
    SiteId = siteId,
    RowsOffset = rowsOffset,
    PageSize = pageSize,
    DeviceTypeID1 = deviceTypeID1,
    DeviceTypeID2 = deviceTypeID2,
    DeviceTypeID3 = deviceTypeID3
};

This is assuming the names of the variables that you're using for your user input for device type. You many need to deconstruct that differently depending on how you're gathering the input from the user. You might need to dynamically construct the SQL string based on your data structures.

(Side note: you should avoid using SELECT * and rather specifically spell out the columns you need in your results)

Since you're passing in a List<int> for device type you can rewrite your query using IN and just pass the list in a single parameter. Dapper allows you to pass an IEnumerable and will do the work of constructing the variable list for you:

SELECT *
FROM device WHERE site_id = @SiteId
AND device_type_id IN @DeviceTypeIDs
ORDER BY site_id
OFFSET @RowsOffset ROWS
FETCH NEXT @PageSize ROWS ONLY

var param = new
{
    SiteId = siteId,
    RowsOffset = rowsOffset,
    PageSize = pageSize,
    DeviceTypeIDs = deviceTypes
};

I've edited the function in my question, my function takes in a List deviceTypes. I don't have problem building the SQL query, I can loop through the variable and add accordingly. But how do I dynamically add a parameter named "DeviceTypeID1", "...2", "...3" , ... to the anonymous object? — Farid, Aug 27 '21 at 13:22
I knew that there must be a simple solution to this, never knew Dapper could do this. Thanks! :) — Farid, Aug 27 '21 at 13:29
You bet! Here's more info on it: https://github.com/DapperLib/Dapper#list-support — squillman, Aug 27 '21 at 13:31

How do I add multiple "WHERE conditionA OR conditionB OR conditionC" in SQL parameter?

1 Answers1