ormpetapoconpoco

How do I use the SQL WHERE IN construct with PetaPoco?

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...)

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);

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)

which gets me what I want while using sqlparameters. So I guess it's good enough for now, although not super pretty.

/Mike

Solution

  • 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);

    This will result in 

    select * from Tags where name in (@0, @1);
@0 = SqlServer, @1 = IIS