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

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 returns 73 results, while the other with passed parameters returns 0 results.

Is there anything I am missing or doing wrong?

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

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 returns 73 results, while the other with passed parameters returns 0 results.

Is there anything I am missing or doing wrong?

• c#
• entity-framework-core
• azure-cosmosdb
• azure-cosmosdb-sqlapi

• 2 Why SqlParameter if you aren't using SQL Server? Shouldn't you be using the Cosmos QueryDefinition if you want raw access? Also why isn't your interpolated string version using FormattableString and FromSqlInterpolated? – Charlieface Commented Mar 4 at 0:16
• github/Azure/azure-cosmos-dotnet-v3/blob/master/… – mjwills Commented Mar 4 at 0:23
• @Charlieface the method takes input as an array of objects. I even tried to send the list of these objects with name and value pairs but still didn't get any response. I am not using the Cosmos SDK, it is EF Core cosmos and this is the extension method top of it. – Humzamanzur Commented Mar 4 at 1:20
• @mjwills Isn't it Cosmos SDK sample code? I am working with EF Core Cosmos. – Humzamanzur Commented Mar 4 at 1:21
• 2 So again: why are you using SQL Server specific SqlParameter, why do you think it would work with Cosmos which is an entirely different product? Again: why aren't you using the Cosmos SDK if you want raw access, given that EF Core doesn't fit requirements? And again: why is your interpolated string using unsafe interpolation with FromSqlRaw, rather than FormattableString with FromSqlInterpolated? I'm not trying to shut you down, we really need to know. Until you answer those questions, we can't advise you, as you're going against best practice and any advice we would give. – Charlieface Commented Mar 4 at 1:56

1 Answer 1

CosmosQueryableExtensions method FromSqlRaw returning 0 results when passed parameters

It is returning result as 0 because, the code uses parameterized SQL query in EF core which does not support by Cosmos DB. The below code works successfully and shows the result as Total count: 2 by using FromSqlRaw() function and it uses EF Core parameter replacement with {0}, {1}, which works correctly with Azure CosmosDB as you can see in the output below.

public class AppDbContext : DbContext
{
    public DbSet<Customer> Customers { get; set; }

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        optionsBuilder.UseCosmos(
            "https://<accName>.documents.azure:443/",
            "<primaryKey>",
            "db1"
        );
    }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Customer>()
            .ToContainer("cont1")      
            .HasKey(c => c.Id);        

        modelBuilder.Entity<Customer>().HasNoDiscriminator();  
    }
}

class Program
{
    static async Task Main()
    {
        using (var context = new AppDbContext())
        {
            Guid partnerId = new Guid("550e8400-e29b-41d4-a716-446655440000");  

            var queryString = @"SELECT * FROM c WHERE c.Discriminator = {0} AND c.PartnerId = {1}";

            var customers = await context.Customers
                .FromSqlRaw(queryString, "testEntity", partnerId.ToString())  
                .ToListAsync();

            foreach (var customer in customers)
            {
                Console.WriteLine($"Id: {customer.Id}, Name: {customer.Name}, PartnerId: {customer.PartnerId}");
            }

            Console.WriteLine($"Total count: {customers.Count}");
        }
    }
}

Output:

Id: 1, Name: Alice Brown, PartnerId: 550e8400-e29b-41d4-a716-446655440000
Id: 2, Name: John Doe, PartnerId: 550e8400-e29b-41d4-a716-446655440000
Total count: 2