Npgsql Query Issue: where in array check

I keep encountering a very peculiar Npgsql query “issue,” which causes me to get stumped, look up the solution online, and continue my life. This happens once every 4–6 months. I don’t know why, but I keep forgetting the proper syntax, and I have decided to jot down the summary of what I am running into together with the solution. I hope this will make it stick in my brain, or at the very least, I will know exactly where to get a refresher.

What’s the issue? It’s best explained with an example. Imagine you have a users table with two columns, userid and state, and data that looks like this:

 userid | state
--------+-------
      1 | CA
      2 | CA
      3 | IL
      4 | IL
      5 | SD

Let’s say your app allows you to query for users for a set of states, e.g., give me all users located in IL and SD. Here is how you could write this in SQL:

SELECT * FROM users WHERE state IN ('IL', 'SD');
 userid | state
--------+-------
      3 | IL
      4 | IL
      5 | SD

Cool, that looks right. Now you go and implement a database layer code that will execute this, and you might be tempted to write this code as follows for the Npsql query:

SELECT * FROM Users WHERE state_of_residency IN @states

Where @states is an array of strings parameter. Run your code, and you will get an error:

 Error Message:
   Npgsql.PostgresException : 42601: syntax error at or near "$1"

What if we surrounded @states with parenthesis like this:

SELECT * FROM Users WHERE state_of_residency IN (@states)

Error again, but with a slightly different message:

 Error Message:
   Npgsql.PostgresException : 42883: operator does not exist: text = text[]

So what should your query look like instead? This works:

SELECT * FROM Users WHERE state_of_residency = ANY(@states)

Run it, and it will work. column = ANY(array_parameter) is the key

I can’t tell you how many times I was stumped by this and had to go and waste time online looking for solutions as to how to query this properly. Hopefully, this will help my future self and anyone else that runs into this issue.