Dapper with Postgres and "WHERE IN" not working

Question

I cannot get a "WHERE IN" to work with Dapper, Postgres.

My DDL

CREATE SCHEMA IF NOT EXISTS "orgschema";


CREATE TABLE IF NOT EXISTS orgschema."Department" (
 "DepartmentKey" BIGINT NOT NULL GENERATED BY DEFAULT AS IDENTITY,
 "DepartmentName" CHARACTER VARYING(256) NOT NULL,
 "DepartmentMacroStatusKey" INTEGER NOT NULL,
 CONSTRAINT PK_Department PRIMARY KEY ("DepartmentKey")
 );

My C# POCO object:

public partial class Department
{
    public long DepartmentKey { get; set; } /* PK */

    public string DepartmentName { get; set; }
    
    public int DepartmentMacroStatusKey { get; set; }
}

My DataLayer code.

I've temporarily hardcoded the "macro-status-keys" .. just trying to figure out the syntax sugar.

I've chased down three different "suggestions".....each of them giving me exceptions.

    public async Task<IEnumerable<Department>> FindByMacroStatusKeys(
        IReadOnlyCollection<int> departmentMacroStatusKeys,
        CancellationToken token)
    {
        IEnumerable<Department> returnItems = null;
        int tryNumber = 3; /* << CHANGE THIS TO TRY DIFFERENT THINGS */
        if (tryNumber == 1)
        {
            returnItems = await this.WhereInTryOne(departmentMacroStatusKeys, token);
        }

        if (tryNumber == 2)
        {
            returnItems = await this.WhereInTryTwo(departmentMacroStatusKeys, token);
        }
        
        if (tryNumber == 3)
        {
            returnItems = await this.WhereInTryThree(departmentMacroStatusKeys, token);
        }

        if (null == returnItems)
        {
            throw new ArgumentOutOfRangeException("Probably a bad tryNumber");
        }

        return returnItems;
    }

    private async Task<IEnumerable<Department>> WhereInTryOne(
        IReadOnlyCollection<int> departmentMacroStatusKeys,
        CancellationToken token)
    {
        IEnumerable<Department> returnItems = null;

        /* TryOne is "straight from https://www.learndapper.com/parameters */
        
        /* note the LACK OF () around the @MyCoolKeys */
        string query =
            @"SELECT * FROM ""orgschema"".""Department"" d WHERE d.""DepartmentMacroStatusKey"" IN @Ids;";

        var ids = new[] { 8001, 7, 12 };

        // https://www.learndapper.com/parameters
        //var sql = "SELECT * FROM Products WHERE ProductId IN @Ids;";
        //using (var connection = new SqlConnection(connectionString))
        //{
        //    connection.Open();
        //    var products = connection.Query<Product>(sql, new {Ids = ids }).ToList();
        //}

        using (IDbConnection connection = this.dapperContext.GetConnection())
        {
            returnItems = await connection.QueryAsync<Department>(query, new { Ids = ids });
        }

        return returnItems;
    }
    
    private async Task<IEnumerable<Department>> WhereInTryTwo(
        IReadOnlyCollection<int> departmentMacroStatusKeys,
        CancellationToken token)
    {
        IEnumerable<Department> returnItems = null;

        /* note () around the @MyCoolKeys */
        string query =
            @"SELECT * FROM ""orgschema"".""Department"" d WHERE d.""DepartmentMacroStatusKey"" IN (@MyCoolKeys);";

        List<int> theCoolKeys = new List<int>() { 8001, 13, 17 };

        /* from https://stackoverflow.com/a/73568667/214977 */
        DynamicParameters parameters = new DynamicParameters();
        parameters.Add("@MyCoolKeys", theCoolKeys );

        using (IDbConnection connection = this.dapperContext.GetConnection())
        {
            returnItems = await connection.QueryAsync<Department>(query, parameters);
        }

        return returnItems;
    }
    
    private async Task<IEnumerable<Department>> WhereInTryThree(
        IReadOnlyCollection<int> departmentMacroStatusKeys,
        CancellationToken token)
    {
        /* FROM https://makolyte.com/csharp-adding-dynamic-query-parameters-with-dapper/ */
        
        IEnumerable<Department> returnItems = null;

        /* note LACK OF () around the @MyCoolKeys */
        string query =
            @"SELECT * FROM ""orgschema"".""Department"" d WHERE d.""DepartmentMacroStatusKey"" IN @MyCoolKeys;";

        /* note the lack of "@" before MyCoolKeys below */
        var parameters = new Dictionary<string, object>()
        {
            ["MyCoolKeys"] = new List<int>()
            {
                33 , 44 , 55
            }
        };

        using (IDbConnection connection = this.dapperContext.GetConnection())
        {
            returnItems = await connection.QueryAsync<Department>(query, parameters);
        }

        return returnItems;
    }

Project is a Dot-Net-Core currently at net6.0.

Dapper reference:

<ItemGroup>
    <PackageReference Include="Dapper" Version="2.0.123" />
</ItemGroup>

Below are the 3 different exceptions I'm getting:

Try 1 Exception:

Npgsql.PostgresException (0x80004005): 42601: syntax error at or near "$1"

POSITION: 80
   at Npgsql.Internal.NpgsqlConnector.<ReadMessage>g__ReadMessageLong|215_0(NpgsqlConnector connector, Boolean async, DataRowLoadingMode dataRowLoadingMode, Boolean readingNotifications, Boolean isReadingPrependedMessage)
   at Npgsql.NpgsqlDataReader.NextResult(Boolean async, Boolean isConsuming, CancellationToken cancellationToken)
   at Npgsql.NpgsqlCommand.ExecuteReader(CommandBehavior behavior, Boolean async, CancellationToken cancellationToken)
  Exception data:
    Severity: ERROR
    SqlState: 42601
    MessageText: syntax error at or near "$1"
    Position: 80
    File: scan.l
    Line: 1180
    Routine: scanner_yyerror

Try 2 Exception:

Npgsql.PostgresException (0x80004005): 42883: operator does not exist: integer = integer[]

POSITION: 77
   at Npgsql.Internal.NpgsqlConnector.<ReadMessage>g__ReadMessageLong|215_0(NpgsqlConnector connector, Boolean async, DataRowLoadingMode dataRowLoadingMode, Boolean readingNotifications, Boolean isReadingPrependedMessage)
   at Npgsql.NpgsqlDataReader.NextResult(Boolean async, Boolean isConsuming, CancellationToken cancellationToken)
   at Npgsql.NpgsqlCommand.ExecuteReader(CommandBehavior behavior, Boolean async, CancellationToken cancellationToken)
  Exception data:
    Severity: ERROR
    SqlState: 42883
    MessageText: operator does not exist: integer = integer[]
    Hint: No operator matches the given name and argument types. You might need to add explicit type casts.
    Position: 77
    File: parse_oper.c
    Line: 731
    Routine: op_error

Try 3 Exception:

Npgsql.PostgresException (0x80004005): 42601: syntax error at or near "$1"

POSITION: 80
   at Npgsql.Internal.NpgsqlConnector.<ReadMessage>g__ReadMessageLong|215_0(NpgsqlConnector connector, Boolean async, DataRowLoadingMode dataRowLoadingMode, Boolean readingNotifications, Boolean isReadingPrependedMessage)
   at Npgsql.NpgsqlDataReader.NextResult(Boolean async, Boolean isConsuming, CancellationToken cancellationToken)
   at Npgsql.NpgsqlCommand.ExecuteReader(CommandBehavior behavior, Boolean async, CancellationToken cancellationToken)
  Exception data:
    Severity: ERROR
    SqlState: 42601
    MessageText: syntax error at or near "$1"
    Position: 80
    File: scan.l
    Line: 1180
    Routine: scanner_yyerror

I've done a lot of ORM coding in the past. So I "get" the concepts. Not sure why this one is fighting me.

Not sure if this is a postgres specific issue (maybe I'll try Ms-Sql-Server tomorrow or something)........

Note that I have other queries (seen below) that are not "WHERE IN" types..and those work fine.

    public async Task<IEnumerable<Department>> GetAllAsync(CancellationToken token)
    {
        IEnumerable<Department> returnItems = null;
        string query = @"SELECT * FROM ""orgschema"".""Department"";";
        using (IDbConnection connection = this.dapperContext.GetConnection())
        {
            returnItems = await connection.QueryAsync<Department>(query);
        }

        return returnItems;
    }


    public async Task<Department> GetSingleAsync(long keyValue, CancellationToken token)
    {
        this.logger.LogDebug("GetSingleAsync called");
        Department returnItem = null;
        string query = @"SELECT * FROM ""orgschema"".""Department"" d WHERE d.""DepartmentKey"" = @DepartmentKey;";
        IDictionary<string, object> dictionary = new Dictionary<string, object>
        {
            { "@DepartmentKey", keyValue }
        };
        DynamicParameters parameters = new DynamicParameters(dictionary);
        using (IDbConnection connection = this.dapperContext.GetConnection())
        {
            returnItem = await connection.QuerySingleOrDefaultAsync<Department>(query, parameters);
        }

        return returnItem;
    }

Not that it matters (the code never gets to the database)..but for completeness. Postgres version below.

PostgreSQL 13.2 (Debian 13.2-1.pgdg100+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 8.3.0-6) 8.3.0, 64-bit

The "dapperContext"

public class MyDapperDbContext
{
    private readonly IDbConnection readWriteDbConnection;

    public MyDapperDbContext(IDbConnection readWriteDbConnection)
    {
        this.readWriteDbConnection = readWriteDbConnection;
    }

    public IDbConnection GetConnection()
        => this.readWriteDbConnection;
}

IoC registration:

//IServiceCollection services

string completeConnectionString = "User ID=root;Password=myPassword;Host=localhost;Port=5432;Database=myDataBase;";

services.AddTransient<MyDapperDbContext>(sp => new MyDapperDbContext(new Npgsql.NpgsqlConnection(completeConnectionString)));

and csproj reference:

 <PackageReference Include="Npgsql" Version="7.0.1" />

Does this answer your question? [Dapper.net "where ... in" query doesn't work with PostgreSQL](https://stackoverflow.com/questions/38188558/dapper-net-where-in-query-doesnt-work-with-postgresql) — Palle Due, Jan 12 '23 at 09:59
Thanks Palle. While the answer you reference was "not super complete", it definitely gave the super hint of ANY. — granadaCoder, Jan 12 '23 at 14:47

score 0 · Answer 1 · answered Jan 12 '23 at 14:52

Thanks to a comment (in the original question).... I was able to code up the solution.

I am not considering this a duplicate question because the answer at the other question was not "super complete"....but gave the hint.

Here is a COMPLETE answer (that reciprocates the question I posed).

    private async Task<IEnumerable<Department>> WhereInTryFour(
        IReadOnlyCollection<int> departmentMacroStatusKeys,
        CancellationToken token)
    {

        IEnumerable<Department> returnItems = null;

        /* use ANY for postgres */
        string query =
            @"SELECT * FROM ""orgschema"".""Department"" d WHERE d.""DepartmentMacroStatusKey"" = ANY(@MyCoolKeys);";

        /* here is a "hard coded" parameters as example */
        IDictionary<string, object> xparameters = new Dictionary<string, object>()
        {
            ["@MyCoolKeys"] = new List<int>()
            {
                8001 , 44 , 55
            }
        };

        /* now that it works!  use the input parameter to create the parameters */
        IDictionary<string, object> parameters = new Dictionary<string, object>()
        {
            ["@MyCoolKeys"] = new List<int>(departmentMacroStatusKeys)
        };
        
        using (IDbConnection connection = this.dapperContext.GetConnection())
        {
            returnItems = await connection.QueryAsync<Department>(query, parameters);
        }

        return returnItems;
    }

Dapper with Postgres and "WHERE IN" not working

1 Answers1

Linked