Selection with Dapper using IN clause

2

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

select * from product
where CasNo in(@filtro)

Remembering that within this @filtro has a list of CasNo that is of type 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();

This did not work out.

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

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

    
asked by anonymous 22.10.2018 / 17:53

1 answer

3

Just take the parentheses. Dapper already does that.

select * from product
where CasNo in @filtro
    
22.10.2018 / 18:36