How do I access the VALUE using CosmosQueryableExtensions

Question

EFCore Cosmos provider does not implement subquery yet and so I have implemented the query using the following FromRawSql as per this post:

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

// Use CosmosQueryableExtensions instead of _context.Cases.FromSqlRaw to avoid ambiguous namespace.
// https://github.com/dotnet/efcore/issues/26502
return CosmosQueryableExtensions
    .FromSqlRaw(_contextCosmos.Tickets, TICKET_SQL, statusNewParam, statusInProgressParam, statusOnHoldParam, userMasterGuidParam)
    .OrderByDescending(t => t.CreatedDateTime)
    .ToListAsync();

When I execute this query in the Cosmos Data Explorer I get a valid result - an array of items.

SELECT * FROM c WHERE c.StatusGuid IN ('63295b5e-de34-4555-b736-408dae18aaa0', '55d05dde-6b71-475f-8ee5-5549e2187423', 'e5267754-d416-4d1f-b42f-700dc5bb13d3') AND EXISTS ( SELECT VALUE n FROM n IN c.caseservicepartner_collection WHERE n.PartnerAssignedUserGuid = 'f3e9dd05-c580-4390-8998-61ce915d2da3')

[
    {
        "CreatedDateTime": "2022-08-17T08:22:54.017000+00:00",
        "CaseNumber": 111,
        "AssignedTeamGuid": null,
        "TicketTypeGuid": "18ba2bba-557f-4bbd-9b45-029194761980",
        ...
    },
    {
     ...
    }
]

However, when I execute this using EFCore, it returns no data. Looking at the EFCore log, it seems to wrap this query in an outer select, as follows:

-- EFCore adds this
SELECT c
      FROM (
-- My Query
          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

...which when I plug into the Data Explorer, returns a nested structure like this:

[
    {
        "c": {
            "CreatedDateTime": "2022-08-17T08:22:54.017000+00:00",
            "CaseNumber": 111,
            "AssignedTeamGuid": null,
            "TicketTypeGuid": "18ba2bba-557f-4bbd-9b45-029194761980",
            ...
        }
    },
]

I suspect this is why the data is not being returned, perhaps due to a type mismatch.

Is there a way to fix this so the array is returned at the root, rather than nested within the c value?

Thanks

UPDATE

I removed the SqlParameters and instead used the string format-like option to pass parameters. That sorted out my issue and date is being returned now.

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