为何PostgreSQL查询在.NET Core 6中无法找到数据？

我有一段PostgreSQL 14的查询语句，在DBeaver 23.1.4中执行能正常返回记录，但复制到.NET Core 6（C#）代码的字符串变量中执行时，却无法找到数据。使用的组件版本为：Dapper 2.0.143、Npgsql 7.0.4、Npgsql.EntityFrameworkCore.PostgreSQL 7.0.4。补充信息：process_datetime和start_datetime字段类型为timestamptz。我尝试了两种C#执行方式，均未得到结果，请问问题出在哪里？

查询语句 #

select 
    t.organizations_pid, 
    coalesce(sum(t.sum_usage),0) * 1.05 totalUsage,   
    coalesce(count(t.iccid), 0) numSIMs 
from 
    (select 
         s.organizations_pid, c2.pid cdrs_pid, c2.cdr_id,  
         c2.iccid, c2.network, c1.sum_usage, 
         c2.start_datetime, c2.status 
     from
         (select 
              s.organizations_pid, c1.iccid, 
              sum(c1.usage) sum_usage, max(c1.process_datetime) max_process_datetime 
          from 
              cdrs c1 
          inner join 
              sims s on c1.iccid = s.iccid 
          where 
              s.organizations_pid = 29 
              and to_char(c1.start_datetime,'YYYY-MM-DD') >= '2023-08-01' 
              and to_char(c1.start_datetime,'YYYY-MM-DD') <= '2023-08-07' 
              and c1.is_beginbal = false 
          group by 
              s.organizations_pid, c1.iccid) c1 
     inner join 
         cdrs c2 on c2.process_datetime = c1.max_process_datetime 
                 and c2.iccid = c1.iccid 
     inner join 
         sims s on c2.iccid = s.iccid 
                and s.last_cdr_id = c2.cdr_id
     inner join 
         organizations o on s.organizations_pid = o.pid 
     where 
         s.organizations_pid = 29 
         and s.attributes->'subscription'->> 'subscriptionStatus' = 'ACTIVE') t 
group by 
    t.organizations_pid

尝试的C#代码 #

第一种执行方式：

using (var dr = conn.ExecuteReader(sql, new { }))
{
   if (dr.Read())
   {
     double totalUsage = dr.GetDouble(dr.GetOrdinal("totalUsage"));
   }
}

第二种执行方式（返回null）：

var result = conn.Query(sql, new { }).SingleOrDefault(); 

可能的问题及解决方法 #

• 时区不匹配：timestamptz类型会随连接时区变化解析结果。DBeaver可能使用本地时区，而Npgsql默认连接时区可能为UTC或其他时区，导致to_char转换后的日期范围和预期不符。
  解决：放弃to_char转换，直接用时间戳类型做范围比较，避免时区干扰：

  c1.start_datetime >= '2023-08-01'::timestamptz
  AND c1.start_datetime < '2023-08-08'::timestamptz
  

  或在Npgsql连接字符串中指定与DBeaver一致的时区，例如TimeZone=Asia/Shanghai。

• SQL字符串转义错误：复制SQL到C#字符串时，可能存在符号转义或截断问题（如->、->>被意外处理）。检查C#中的SQL变量是否与DBeaver中的原始查询完全一致。

• Dapper映射异常：使用Query方法时，匿名对象可能无法正确映射查询结果列。尝试定义强类型接收结果：

  public class UsageStats
  {
      public int organizations_pid { get; set; }
      public double totalUsage { get; set; }
      public int numSIMs { get; set; }
  }
  
  var result = conn.Query<UsageStats>(sql).SingleOrDefault();
  

• 连接或事务问题：检查数据库连接是否正常打开，是否存在未提交的事务导致查询无法读取数据。确保连接无隔离级别限制，能正常访问目标数据。

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