Dapper Part 15 - Using the IN Clause

This is Part 15 of a series on using Dapper to simplify data access with ADO.NET

• Simpler .NET Data Access With Dapper - Part 1
• Dapper Part 2 - Querying The Database
• Dapper Part 3 - Executing Queries
• Dapper Part 4 - Passing Data To And From The Database
• Dapper Part 5 - Passing Data In Bulk To The Database
• Dapper Part 6 - Returning Multiple Sets Of Results
• Dapper Part 7 - Adding DateOnly & TimeOnly Support
• Dapper Part 8 - Controlling Database Timeouts
• Dapper Part 9 - Using Dynamic Types
• Dapper Part 10 - Handling Cancellations
• Dapper Part 11 - Using Inheritance
• Dapper Part 12 - Alternative Bulk Insert Technique
• Dapper Part 13 - Using Transactions
• Dapper Part 14 - Multithreading
• Dapper Part 15 - Using The IN Clause (This Post)

In the last post, we looked at considerations for multithreading.

This post will discuss how to use the SQL IN clause.

In a previous post, we saw how to use table-valued parameters to bulk-send data to the database.

Sometimes, you have a situation where your data is in a simple list of primitives.

In such a situation, you do not need the overhead of a user-defined type and a table-valued parameter.

Let us take a situation where we need to load the details of several Spy entities with their SpyIDs provided at runtime.

If we knew the IDs in advance, we would write a query like this:

SELECT *
FROM Spies
WHERE Spies.SpyID in (1, 2 ,3 ,4, 6, 10, 13, 56)

However, if the IDs are being provided at runtime, we do something like this:

app.MapGet("/List", async (SqlConnection cn) =>
{
    // create query
    const string query = """
                         SELECT * FROM Spies
                         WHERE Spies.SpyID IN @Spies
                         """;
    // define a collection to store the IDs
    int[] ids = [1, 2, 3, 4, 6, 10, 13, 56];

    // Fetch the spies
    var spies = await cn.QueryAsync<Spy>(query, new { Spies = ids});
    return spies;
});

This will return the following:

The magic is taking place here:

 // Fetch the spies
 var spies = await cn.QueryAsync<Spy>(query, new { Spies = ids});

Dapper can use the list to construct a valid IN query.

Note that the query itself does not have brackets around the parameter.

SELECT * FROM Spies
WHERE Spies.SpyID IN @Spies

TLDR

Dapper can construct and execute valid IN clause statements using collections.

The code is in my GitHub.

Happy hacking!