Table of Contents

Pass a list of values (IN)

Pass an array or a list to an IN clause, make it an optional filter, or send it as one array with ANY on PostgreSQL.

Use this when a query must find the rows whose column matches one of several values that your code holds. Use it too when the list can be null or empty, or can hold thousands of values.

Pass a list after IN

The PostgreSQL example project writes this query in Methods/OrderQueries.sql:

-- name: ListOrdersByStatuses -> many
-- summary: The orders in any of the given statuses, or every order when the list is null, newest first.
-- member-location: class
-- visibility: public
SELECT
    id,
    status,
    placed_at
FROM orders
WHERE (@statuses IS NULL OR status IN @statuses)
ORDER BY placed_at DESC;

Its class, in Methods/OrderQueries.cs, passes an array:

    // A method of your own that passes a list: ListOrdersByStatusesParams.Statuses is an IEnumerable<string>?, which
    // takes this array, a List<string> or any other sequence, and the method sends one parameter for each value.  See
    // site/reference/list-parameters.md#list-parameters.
    public static Task<ListOrdersByStatusesDto[]> ListOrdersNotCancelledAsync(
        DbConnection connection,
        CancellationToken cancellationToken = default
    )
    {
        string[] notCancelled = ["pending", "shipped"];
        return ListOrdersByStatusesAsync(
            connection,
            new ListOrdersByStatusesParams(notCancelled),
            cancellationToken: cancellationToken
        );
    }

What you get. ListOrdersByStatusesParams.Statuses is an IEnumerable<string>?, which takes an array, a List<string> or any other sequence. The query method sends a parameter for each value, on PostgreSQL and on SQL Server. The test @statuses IS NULL makes the filter optional, as Filter by an optional list of values shows. In the example, the folder sets the output methods, and the file header turns on async-method-suffix. The query's -- member-location: class puts the method on its class. So the method is OrderQueries.ListOrdersByStatusesAsync, and takes the connection first.

The rules you need.

  • Write the parameter directly after IN, with no parentheses: WHERE id IN @ids. See list parameters.
  • The property of a list in the input model is an IEnumerable<T>, such as an IEnumerable<int> for int values. See list parameters.

Filter by an optional list of values

-- name: ListOrdersByStatuses
SELECT
    id,
    status,
    placed_at
FROM orders
WHERE (@statuses IS NULL OR status IN @statuses)
ORDER BY placed_at DESC;
string[] notCancelled = ["pending", "shipped"];
var shown = await orders.ListOrdersByStatuses(new ListOrdersByStatusesParams(notCancelled));
var everyOrder = await orders.ListOrdersByStatuses(new ListOrdersByStatusesParams(null));

What you get. shown holds the orders whose status is pending or shipped. everyOrder holds every order, since the test @statuses IS NULL is true for a null list. An empty list returns no order.

The rules you need.

On PostgreSQL: one array with ANY

On PostgreSQL, you can also send the values as one array, with = ANY(@ids) in place of IN @ids.

-- name: FindUsersByIds
SELECT id, name FROM users WHERE id = ANY(@ids);
HashSet<int> ids = [3, 1, 2];
FindUsersByIdsDto[] found = await users.FindUsersByIds(new FindUsersByIdsParams(ids));

What you get. FindUsersByIdsParams.Ids is an IEnumerable<int>, as the property of a list is. The method sends the values as one parameter, a PostgreSQL array.

The rules you need.

  • An array parameter is not a list. id = ANY(@ids) sends one parameter, whatever the count of values, so no limit of parameters applies to it. id IN @ids sends one parameter for each value. See Array parameters.
  • If one query must run on PostgreSQL and on SQL Server, which has no arrays, use IN @ids. See Array parameters and Lists on SQL Server.
  • If Dapper runs the query, use = ANY(@ids). Dapper sends a list to PostgreSQL as one array, which IN @ids cannot take. See List parameters.
  • Do not write IN (@ids), with parentheses. It compares with one array. See Array parameters.
  • Under the output models, the property of an array is a T[], and the property of a list an IEnumerable<T>. See Array parameters.

Large and empty lists

Read this if a list can be empty or null, or can hold thousands of values.

-- name: ListUsersByIds
SELECT
    id,
    name
FROM users
WHERE id IN @ids
ORDER BY id;

-- name: ListUsersExcept
SELECT id, name
FROM users
WHERE id NOT IN @ids
ORDER BY id;
int[] none = [];
ListUsersByIdsDto[] found = await users.ListUsersByIds(new ListUsersByIdsParams(none)); // no user
ListUsersExceptDto[] others = await users.ListUsersExcept(new ListUsersExceptParams(none)); // every user

What you get. The empty list finds no user after IN, and every user after NOT IN. Neither call throws.

The rules you need.

  • An empty list matches no row after IN, and every row after NOT IN. A null list does the same. But @ids IS NULL is true for a null list, and false for an empty one. See List parameters.
  • Keep nulls out of a list after NOT IN. A null value there makes the condition match no row. See List parameters.
  • The method pads each list to one of a few sizes with its last value, so it sends 3 values as 4. The result does not change, and the database keeps one plan for each size. To send each list at its own count, write -- generator: list-padding=off. See Padding.
  • Each value of a list counts toward the limit of parameters of the database, and so does every other parameter. PostgreSQL takes 65,535, and SQL Server 2,098. If a call would send more, the method throws ArgumentException before it sends anything. Padding alone never makes it throw. See The limit of parameters.

For more values than the limit, send them as one parameter. On SQL Server, send a JSON array in an nvarchar(max) parameter, and read it with OPENJSON. This needs a database whose compatibility level is 130 or later, and the -- param: line. See The limit of parameters and Lists on SQL Server.

Write id = ANY(@ids), as On PostgreSQL: one array with ANY shows.

If it does not work

  • ArgumentException, "The query 'ListUsersByIds' would send ... parameters". The lists hold more values than the database takes. Send the values as one parameter. See The limit of parameters.
  • SQLSRC127 on SQL Server. A list after a NOT stands inside an aggregate, a GROUP BY or an OUTPUT clause, where SQL Server refuses an empty list. Test the list in a WITH query, and use its result there. See SQLSRC127.
  • SqlException 130 on SQL Server, for an empty or null list. A list after a NOT stands inside an aggregate of your own. Test the list in a WITH query, and aggregate its result. See the limit.
  • An empty list matches the rows whose value is null, on SQL Server. The connection turned ANSI_NULLS off. Leave it on. See the limit.