C# – How to use IN with Dapper

by

Let’s say you have a SQL Query that uses IN and you want to execute it using Dapper. Your query looks something like this:

SELECT * FROM [Articles] 
WHERE Author IN ('Bob', 'Alice')
Code language: SQL (Structured Query Language) (sql)

Here’s how you’d execute that with Dapper:

static IEnumerable<Article> GetArticles(List<string> authors)
{
	using (SqlConnection con = new SqlConnection(GetConnectionString()))
	{
		return con.Query<Article>("SELECT * FROM Articles WHERE Author IN @authors"
		, new { authors=authors });
	}
}
Code language: C# (cs)

Then you’d call it like this:

var articles = GetArticles(new List<string>() { "Bob", "Alice" });
Code language: C# (cs)

There are two key things to notice about this:

  1. You have to exclude the parentheses.
  2. I specified one parameter called @authors and passed in a list of authors. Dapper knows how to deal with lists.

Exclude the parentheses

In a normal SQL Query you have to use parentheses with IN. But when you’re executing the query with Dapper you must exclude them.

Take a look at what happens when I use IN (@authors)

static IEnumerable<Article> GetArticles(List<string> authors)
{
	using (SqlConnection con = new SqlConnection(GetConnectionString()))
	{
		return con.Query<Article>("SELECT * FROM Articles WHERE Author IN (@authors)"
		, new { authors=authors });
	}
}
Code language: C# (cs)

I get the following incomprehensible exception:

System.Data.SqlClient.SqlException: Incorrect syntax near ‘,’

This is because when you use IN with Dapper, it automatically puts parentheses in there for you.

It generates something equivalent to the following SQL Query:

SELECT * FROM [Articles] 
WHERE Author IN (('Bob', 'Alice'))
Code language: SQL (Structured Query Language) (sql)

If you tried to execute this invalid query in SSMS, you’d get the “Incorrect syntax near ‘,'” error.

Related Articles