How to pass multiple condition in dynamic parameter in Dapper Query like we pass for IN operator

Question

I want to pass multiple entity code while querying my PostgreSQL db. I am getting error with below code: I want query to work like below:

        SELECT * FROM public.test WHERE entity_code IN('value1', 'value2');

Below is my code which works in case I am passing single value:

        string query = "SELECT * FROM public.test WHERE entity_code = @Entity_Code";

        Dictionary<string, object> dictionary = new Dictionary<string, object>();
        dictionary.Add("@Entity_Code", "value1");

        var connection = _postgreSqlContext.Database.GetDbConnection();
        var results = connection.Query<Test>(query, new DynamicParameters(dictionary));

And below is the code which is not working in case multiple values added against same parameter:

    string query = "SELECT * FROM public.test WHERE entity_code IN @Entity_Code";

    Dictionary<string, object> dictionary = new Dictionary<string, object>();
    dictionary.Add("@Entity_Code", new string[]{ "value1", "value2" });

    var connection = _postgreSqlContext.Database.GetDbConnection();
    var results = connection.Query<Test>(query, new DynamicParameters(dictionary));

score 1 · Accepted Answer · answered May 18 '21 at 11:31

Postgres has a slightly different IN-syntax. You should use ANY:

string query = "SELECT * FROM public.test WHERE entity_code = ANY(@Entity_Code)";

var dictionary = new Dictionary<string, object>();
dictionary.Add("@Entity_Code", new string[]{ "value1", "value2" });

var connection = _postgreSqlContext.Database.GetDbConnection();
var results = connection.Query<Test>(query, new DynamicParameters(dictionary));

See the answer to this question: IN vs ANY operator in PostgreSQL

How to pass multiple condition in dynamic parameter in Dapper Query like we pass for IN operator

1 Answers1