使用C# 11原始字符串字面量与ExecuteSqlRawAsync操作PostgreSQL遇大小写问题

可行方案1：在PL/pgSQL块内部接收外部参数 #

PostgreSQL的DO $$匿名块有独立作用域，外部传入的参数无法直接在块内使用，需先声明变量接收参数，同时确保带大小写的列名始终用双引号包裹：

var userIdParam = new NpgsqlParameter("UserId", userId);
await dbContext.Database.ExecuteSqlRawAsync(
"""
DO $$ 
DECLARE
    target_user_id INT := @UserId; -- 声明变量接收外部参数
BEGIN 
    IF (SELECT COUNT(*) FROM "Notification" WHERE "UserId" = target_user_id) > 20 THEN
        DELETE FROM "Notification"
        WHERE "UserId" = target_user_id 
          AND "IsReceived" = TRUE 
          AND "ContentId" NOT IN (
              SELECT "ContentId" FROM "Notification"
              WHERE "UserId" = target_user_id
              ORDER BY "CreatedAt" DESC
              LIMIT 20
          );
    END IF;
END $$
""", userIdParam);

可行方案2：移除PL/pgSQL块，改用纯SQL语句 #

把IF逻辑整合到DELETE的WHERE条件中，避免使用匿名块，参数可直接生效，代码更简洁：

await dbContext.Database.ExecuteSqlRawAsync(
"""
DELETE FROM "Notification"
WHERE "UserId" = @UserId 
  AND "IsReceived" = TRUE 
  AND "ContentId" NOT IN (
      SELECT "ContentId" FROM "Notification"
      WHERE "UserId" = @UserId
      ORDER BY "CreatedAt" DESC
      LIMIT 20
  )
AND (SELECT COUNT(*) FROM "Notification" WHERE "UserId" = @UserId) > 20;
""", new NpgsqlParameter("UserId", userId));

之前错误的原因分析 #

1. 原始字符串字面量版本错误：DO $$块内部无法直接引用外部命名参数（@UserId），PL/pgSQL不会自动将外部参数导入块作用域，导致参数未被正确解析，进而触发列名识别错误。
2. ExecuteSqlInterpolatedAsync版本错误：插值参数{userId}会被Npgsql替换为内部占位符（如p0），但这个占位符在PL/pgSQL块内会被当作列名处理，因此报错column "p0" does not exist。
3. 原始版本：直接拼接userId到SQL字符串中，存在严重的SQL注入风险，必须废弃。

内容的提问来源于stack exchange，提问作者Fatorin