How to use IN with Dapper in C#

C# Using the IN Clause with Dapper

Your query might look something like this:

SELECT * FROM [Books] 
WHERE Title IN ('The Great Gatsby', 'Moby Dick')

Here’s how you would execute this query with Dapper:

static IEnumerable<Book> GetBooks(List<string> titles)
{
    using (SqlConnection con = new SqlConnection(GetConnectionString()))
    {
        return con.Query<Book>("SELECT * FROM Books WHERE Title IN @titles", 
        new { titles = titles });
    }
}

To call this method, you would do it like this:

var books = GetBooks(new List<string>() { "The Great Gatsby", "Moby Dick" });

There are two key things to understand in this example:

1. No Parentheses Around the Parameter: You pass a list of items (titles), but you don't include the parentheses in the query.
2. Dapper Handles Lists: The parameter @titles is a list, and Dapper automatically knows how to handle it.

Why You Shouldn't Include Parentheses

In standard SQL, you would use parentheses for the IN clause like this:

SELECT * FROM [Books] 
WHERE Title IN ('The Great Gatsby', 'Moby Dick')

However, when using Dapper, don't include the parentheses. 

What Happens When You Include Parentheses

If you try to include parentheses around the parameter, like this:

static IEnumerable<Book> GetBooks(List<string> titles)
{
    using (SqlConnection con = new SqlConnection(GetConnectionString()))
    {
        return con.Query<Book>("SELECT * FROM Books WHERE Title IN (@titles)", 
        new { titles = titles });
    }
}

You’ll encounter the following error:

System.Data.SqlClient.SqlException: Incorrect syntax near ','

This is because Dapper automatically adds parentheses around the values in your list, so your query becomes:

SELECT * FROM [Books] 
WHERE Title IN (('The Great Gatsby', 'Moby Dick'))

This is invalid SQL, and that's why the exception occurs.

So, always remember: when using lists with Dapper, don’t manually add parentheses around the parameter list.