EF Core Cosmos使用CosmosQueryableExtensions查询无数据的原因及解决

问题背景 #

EF Core Cosmos Provider 暂未实现子查询支持，因此采用 FromRawSql 结合 CosmosQueryableExtensions 编写原生查询，并通过 SqlParameter 传递参数：

SqlParameter userMasterGuidParam = new("userMasterGuid", userMasterGuid);
SqlParameter statusNewParam = new("statusNew", CaseStatusGuids.New);
SqlParameter statusInProgressParam = new("statusInProgress", CaseStatusGuids.InProgress);
SqlParameter statusOnHoldParam = new("statusOnHold", CaseStatusGuids.OnHold);

const string TICKET_SQL =
     @"SELECT * FROM c " +
      "WHERE c.StatusGuid IN (@statusNewParam, @statusInProgress, @statusOnHold) " +
      "AND EXISTS ( " +
      "SELECT VALUE n FROM n IN c.caseservicepartner_collection " +
                    "WHERE n.PartnerAssignedUserGuid = @userMasterGuid) ";

// 使用 CosmosQueryableExtensions 避免命名空间冲突
return CosmosQueryableExtensions
    .FromSqlRaw(_contextCosmos.Tickets, TICKET_SQL, statusNewParam, statusInProgressParam, statusOnHoldParam, userMasterGuidParam)
    .OrderByDescending(t => t.CreatedDateTime)
    .ToListAsync();

在 Cosmos 数据资源管理器中执行上述原生SQL，能得到根数组形式的正确结果；但通过EF Core执行时，框架会自动为查询添加外层SELECT：

-- EF Core自动添加的外层查询
SELECT c
      FROM (
-- 自定义的原生查询
          SELECT * FROM c WHERE c.StatusGuid IN (@statusNewParam, @statusInProgress, @statusOnHold) AND EXISTS ( SELECT VALUE n FROM n IN c.caseservicepartner_collection WHERE n.PartnerAssignedUserGuid = @userMasterGuid)
      ) c

该语句返回的结果会嵌套在c字段中，导致EF Core无法正确映射到实体模型。

解决方法 #

移除SqlParameter，改用字符串格式化方式传递参数，即可解决数据映射问题，返回正常结果：

string TICKET_SQL =
     "SELECT * FROM c " +
     "WHERE c.StatusGuid IN ({0}, {1}, {2}) " +
     "AND EXISTS (SELECT VALUE n FROM n IN c.caseservicepartner_collection WHERE n.PartnerAssignedUserGuid = {3})";

return CosmosQueryableExtensions
    .FromSqlRaw(contextCosmos.Tickets, TICKET_SQL, CaseStatusGuids.New, CaseStatusGuids.InProgress, CaseStatusGuids.OnHold, userMasterGuid)
    .OrderByDescending(t => t.CreatedDateTime)
    .ToList();

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