CosmosQueryableExtensions method FromSqlRaw returning 0 results when passed parameters

Question

I am working with EntityFramework.cosmos and due to some requirements, I have to use this extension method, FromRawSql(), to pass the raw query.

 Guid partnerId = new Guid([myPartnerId])
    
    // string Interpolated Query
    var queryString = $@"SELECT * FROM c WHERE c.Discriminator = 'testEntity' AND c.PartnerId = '{partnerId}' ";
    int totalCount = await _context.testEntity.FromSqlRaw(queryString).CountAsync(); // 73 results
    var parameters = new List<SqlParameter>
    {
        new SqlParameter("@Discriminator", "testEntity"),
        new SqlParameter("@PartnerId", partnerId.ToString())
    };
    
    // Parameterized Query
    var queryStringParameterized = $@"SELECT * FROM c WHERE c.Discriminator = @Discriminator AND c.PartnerId = @PartnerId ";
    int totalCountParameterized = await _context.testEntity.FromSqlRaw(queryStringParameterized, parameters.ToArray()).CountAsync(); // 0 results

PROBLEM:* When I run this, the one with interpolated parameters gives 73 results while the other with passed parameters gives 0 results.

Is there anything I am missing or doing wrong?

Answer 1

Hi Humza Manzoor,

Thank you for reaching out to Microsoft Q & A forum. 

The issue is likely due to how Cosmos DB handles query parameters differently than SQL Server. Here’s what you should check: 

1.Type Mismatch: Cosmos DB is strict with data types. Ensure PartnerId is passed as a Guid, not a string. 

2.Query Parameterization Issues: Unlike SQL Server, Cosmos DB may not support parameters in FromSqlRaw as expected. 

3.EF Core Cosmos Limitations: FromSqlRaw is mainly designed for relational databases, and Cosmos DB might not handle it properly. 

4.Use LINQ Instead (Recommended for Cosmos DB): 

int totalCount = await _context.testEntity
    .Where(c => c.Discriminator == "testEntity" && c.PartnerId == partnerId)
    .CountAsync();

 This ensures EF Core translates the query correctly. 

5.Check Parameter Types  If using FromSqlRaw, make sure parameters match Cosmos DB’s expected types: 

var parameters = new[]
{
    new SqlParameter("@Discriminator", "testEntity"),
    new SqlParameter("@PartnerId", partnerId) // Ensure it's a Guid
};

The best approach is to use LINQ instead of FromSqlRaw to avoid Cosmos DB compatibility issues. If you must use raw SQL, ensure parameter types match exactly. 

Please feel free to contact us if you have any additional questions.     

If you have found the answer provided to be helpful, please click on the "Accept answer/Upvote" button so that it is useful for other members in the Microsoft Q&A community.