C sharp Dapper ms access query like parameters

Question

I have a list of parameters : numericPartList : 029%, 035% for example.

As mentioned in the code, with only 1 parameter it works, but more, no...

public static List<tLicencie> GetDepartement(List<string> numericPartList)
{
    ConnectionStringSettings connex = ConfigurationManager.ConnectionStrings["MaConnection"];
    string connString = connex.ProviderName + connex.ConnectionString;
    using (OleDbConnection con = new OleDbConnection(connString))
    {
        string sql;
        if (numericPartList.Count < 2)
        {
            sql = "select * from tLicencie where NOCLUB like @numericPartList "; // Works !
        }
        else
        {
            sql = "select * from tLicencie where NOCLUB like @numericPartList "; // Does not Work
        }
        return (List<tLicencie>)con.Query<tLicencie>(sql, new { numericPartList }); 
    }
}

I get an error message :

Syntax error (comma) in expression 'NOCLUB like (@ numericPartList1, @ numericPartList2)'. "

How to solve this problem?

A solution for the moment: I add DapperExtentions then

var pga = new PredicateGroup { Operator = GroupOperator.Or, Predicates = new List<IPredicate>() };

                pga.Predicates.Add(Predicates.Field<tLicencie>(f => f.NOCLUB, Operator.Like, "029%"));
                pga.Predicates.Add(Predicates.Field<tLicencie>(f => f.NOCLUB, Operator.Like, "035%"));

                IEnumerable<tLicencie> list = con.GetList<tLicencie>(pga);

Answer 1

The dapper list expansion is only intended for use with in, i.e. it would expand:

where foo in @x

to

where foo in (@x0, @x1, @x2)

(or some other variants, depending on the scenario).

It cannot be used with like in this way, as it will give invalid SQL; you would need to compose your SQL and parameters in a more manual fashion, perhaps using DynamicParameters which works more like a dictionary; i.e. you would need to loop in such a way as to construct

where (foo like @x0 or foo like @x1 or foo like @x2)