我想在查询PostgreSQL数据库时传递多个实体代码。我收到以下代码的错误:我希望查询工作如下:
SELECT * FROM public.test WHERE entity_code IN('value1', 'value2');
以下是我的代码,在我传递单个值的情况下有效:
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));
下面是在针对同一参数添加多个值的情况下不起作用的代码:
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));
Postgres的IN
-语法略有不同。您应该使用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));
查看这个问题的答案:PostgreSQL 中的IN与ANY运算符