如何在PetaPoco中使用SQL WHERE IN构造? [英] How do I use the SQL WHERE IN construct with PetaPoco?
问题描述
我有一个名为标签(ID,名称)的数据库表,我想从中选择名称与列表中的名称匹配的表.在SQL中,我将使用类似的内容:
I have a database table named Tags (Id, Name) from which I would like to select the ones where the name matches a name in a list. In SQL I would use something like:
Select * from Tags Where Name In ('Name1', 'Name2', 'xxx...)
但是现在在一个ASP.Net MVC3项目中使用PetaPoco,我一直在努力弄清楚如何正确地做到这一点.到目前为止,我已经尝试过:
But now using PetaPoco in an ASP.Net MVC3 project I'm stuck figuring out how to do it properly. So far I've tried:
var tagsToFind = new string[] { "SqlServer", "IIS" };
var sql = PetaPoco.Sql.Builder.Select("*").From("Tags").Where("Name in (@0)", tagsToFind);
var result = db.Query<Tag>(sql);
这将导致以下SQL,其中我的tagToFind列表中只有名字用于匹配表数据,而不是所有表数据.
Which results in the following SQL, where only the first name in my list of tagsToFind is used to match the table data as opposed to all of them.
SELECT * FROM Tags WHERE (Name in (@0)) -> @0 [String] = "SqlServer"
有点沮丧,知道这可能并不难..任何帮助,感激不尽!
It's a little frustrating, knowing this probably isn't so hard.. any help is appreciated!
更新: 我发现可以用另一种方式完成
Update: I found out that it can be done in another way
var sql = PetaPoco.Sql.Builder.Append("Select * from tags Where Name IN (@0", tagNames[0]);
foreach (string tagName in tagNames.Where(x => x != tagNames[0])) {
sql.Append(", @0", tagName);
}
sql.Append(")");
var result = db.Query<Tag>(sql)
这使我在使用sqlparameters时得到想要的东西.因此,我认为它现在已经足够好了,尽管还不是很漂亮.
which gets me what I want while using sqlparameters. So I guess it's good enough for now, although not super pretty.
/迈克
推荐答案
这将起作用,除非您不能使用@ 0(常规)语法.您必须使用命名参数,否则它会认为它们是单个参数.
This will work except you can't use the @0 (ordinal) syntax. You must use named parameters, otherwise it thinks they are individual parameters.
var tagsToFind = new string[] { "SqlServer", "IIS" };
var sql = PetaPoco.Sql.Builder.Select("*").From("Tags").Where("Name in (@tags)", new { tags = tagsToFind });
var result = db.Query<Tag>(sql);
这将导致
select * from Tags where name in (@0, @1);
@0 = SqlServer, @1 = IIS
这篇关于如何在PetaPoco中使用SQL WHERE IN构造?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!