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

Question

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

score 60 · Accepted Answer · answered Aug 05 '11 at 14:04

60

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

answered Aug 05 '11 at 14:04

Schotime

15,707
10
46
75

3

Great, that helped me with using multiple parameters in general with PetaPoco, thanks! – Mitch99 Aug 05 '11 at 15:05
1

When I tried that with an integer array, it simply left the clause as "field in (@0)" Instead, I performed a string.Join(",", integerArray) as the parameter. This seemed more efficient anyway, as the PetaPoco ParametersHelper class iterates the enumerable element. – Antony Booth Nov 05 '15 at 22:59
Holy smokes! That's some outside-the-box thinking – Gaspa79 Dec 28 '17 at 16:34
Version 4.01 of nPoco requires using .WhereSql() instead of .Where(). – Alien Technology Jul 08 '19 at 22:14

score 17 · Answer 2 · answered Aug 14 '13 at 23:10

Posting this for future seekers. This works.

    public IEnumerable<Invoice> GetInvoicesByStatus(List<string> statuses)
    {
        return _database.Fetch<Invoice>(@"
            select *
            from Invoices                   
            where Status IN (@statuses)",
            new { statuses });
    }

score 8 · Answer 3 · answered Mar 08 '16 at 12:59

8

If you want to use array class with Petapoco you can use this

string[] array = new string[] {"Name1","Name2" };

var foo = BasicRepository<Personnel>.Fetch("WHERE PersonnelId IN (@0)", array.ToArray());

answered Mar 08 '16 at 12:59

murat önal

81
1
2

That ``.ToArray()`` fixed the problem I was having. ``itemLists = rep.Find("WHERE ItemId IN (@0)", items.ToArray()).ToList()`` thanks. – iiminov Apr 08 '16 at 07:50

guest · Answer 4 · 2014-05-04T16:55:42.947

Here is an another sample:

program.cs:

public static void Main(string[] args)
{
    using (var db = new PetaPoco.Database("Northwind"))
    {
        var sql = "Select * from customers where Country in (@Countries)";
        var countries = new { @Countries = new string[] { "USA", "Mexico" } };
        var customers = db.Query<Customer>(sql, countries);
        foreach (var customer in customers)
        {
            Console.WriteLine("{0} - {1} from {2}", customer.CustomerID, customer.CompanyName, customer.Country);
        }
    }
}

customer.cs:

public class Customer
{
    public string CustomerID { get; set; }
    public string Address { get; set; }
    public string City { get; set; }
    public string CompanyName { get; set; }
    public string ContactName { get; set; }
    public string ContactTitle { get; set; }
    public string Country { get; set; }
    public string Fax { get; set; }
    public string Phone { get; set; }
    public string PostalCode { get; set; }
    public string Region { get; set; }
}

App.config: (Connectionstring uses localdb connectionstring, so you might change it.)

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
    <startup> 
        <supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.5" />
    </startup>
  <connectionStrings>
    <clear/>
    <add name="Northwind"
         connectionString="Data Source=(localdb)\v11.0;Initial Catalog=northwind;Integrated Security=True;"
         providerName="System.Data.SqlClient"/>
  </connectionStrings>
</configuration>

score -2 · Answer 5 · edited Sep 12 '17 at 21:38

-2

Maybe, it's not a good way setting too many params in sql, the max params limit is 2100.

@Murat

string[] array = new string[] {"Name1","Name2" };
var foo = BasicRepository<Personnel>.Fetch("WHERE PersonnelId IN > (@0)", array.ToArray());

Constructing stander SQL in string, and check the LAST excute-sql, alway match your need.

var userIDs = from user in UserList select user.UserID;
db.Delete<User>("where UserID in (" + string.Join(",", userIDs) + ")");

edited Sep 12 '17 at 21:38

mjwills

23,389
6
40
63

answered Aug 31 '17 at 08:32

Marksion

1

4

Actually DON'T do this. You have just introduced SQL Injection vulnerability by bypassing the SQL Parameters. – Oblivion2000 Nov 28 '18 at 14:36
Use a Table Valued Parameter instead. Then you can pass the Datatable of IDs and not worry about hitting the parameters limit. – Taraz Aug 24 '23 at 17:28

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

5 Answers5

Linked