postgresql 带有Postgres和“WHERE IN”的挡板不工作

h43kikqp  于 2023-01-12  发布在  PostgreSQL
关注(0)|答案(1)|浏览(111)

我不能得到一个“在哪里”的工作与衣冠楚楚，波斯特格雷斯。
我的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")
 );

我的C# POCO对象：

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

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

我的数据层代码。
我暂时硬编码了“宏状态键”...只是想弄清楚语法糖。
我找到了三个不同的“建议”......每个都给了我例外。

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;
    }

项目是目前在net6.0中的点-网-核。
接头参考：

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

下面是我得到的3个不同的例外：
尝试1个例外：

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

尝试2个例外：

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

尝试3个例外：

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

我在过去做过很多ORM编码。所以我“理解”了概念。不知道为什么这一个是我的战斗。
不确定这是否是postgres特有的问题（也许我明天会试试Ms-Sql-Server或其他什么）。
请注意，我还有其他查询（如下所示）不是“WHERE IN”类型......并且这些查询工作得很好。

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;
    }

这并不重要（代码永远不会到达数据库）..但为了完整性。Postgres版本如下。

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

“dapperContext”

public class MyDapperDbContext
{
    private readonly IDbConnection readWriteDbConnection;

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

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

IoC注册：

//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)));

和csproj参考：

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

1条答案

按热度按时间
jv2fixgn

jv2fixgn1#

多亏了一个评论（在原来的问题中）...我能够编码出解决方案。
我不认为这是一个重复的问题，因为在另一个问题的答案不是“超级完整”....但给出了提示。
这里是一个完整的答案（这是我提出的问题的回报）。

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;
    }
赞(0）分享  回复(0）举报  2023-01-12
我来回答

相关问题

    查看更多

    热门标签

    更多
    JavaquerypythonNode开发语言requestUtil数据库Table后端算法LoggerMessageElementParser

    最新问答

    更多
    • 蜀ICP备2022004421号-2 NULL123 https://www.null123.com © All rights reserved
    • 本站内容来源互联网,如果侵犯您的权益请联系我们删除, 联系方式：448109455@qq.com