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