Selection with Dapper using IN clause

Question

My idea is to generate a search in my bank using the clause in through the Dapper. For example:

select * from product
where CasNo in(@filtro)

Remembering that inside this @filtro has a list of Casno who’s kind List<String>.

Testing

List<Product> IProductRepository.RetornarPesquisaProdutos(List<string> filtroId) =>
    conn.Query<Product>(
        @"
        SELECT 
            pr.IdProduct as IdProduct, 
            pr.Name as Name,
            sup.Name as Supplier,
            sup.IdSupplier as IdSupplier
        FROM Product pr    
            FULL OUTER JOIN Supplier AS sup ON (pr.IdSupplier = sup.IdSupplier)
        where
            pr.IdProduct in (@filtroId) or
            pr.CASNo     in (@filtroId)
        ORDER BY pr.CommercialName",
        param: new { filtroId }).ToList();

That didn’t work out.

I also tried to do as I did on ADO.NET (which worked out there, but it was in a very nasty way that I had done) by concatenating all the items I needed right into this list. Example filtro = "'123-1', '326-3', '684-8'"); and send this filter directly in my Query without do the cmd.Parameters.AddWithValue(filtro);.

But now I need to do in Dapper this idea of passing a list within the IN

Answer 1

Just take out the parentheses. Dapper already does that.

select * from product
where CasNo in @filtro