Get to underlying SQL for Cosmos DB Query generated via LINQ

Question

I am creating a query to cosmos using Linq

This is converted into SQL which is then run to do the search

var modelName = "Mondeo";
var baseQuery = client.CreateDocumentQuery<Car>(StaticSettings.ProjectionsCollectionUri,
  new FeedOptions { MaxItemCount = maxItemCount, PartitionKey = new PartitionKey(partitionKey) })
                .Where(order => car.ModelName == modelName);

If I run this code and put a breakpoint after this statement, I can see the raw SQL query generated

This is shown in the first line of the inspector

{{"query":"SQL HERE"}}

How can I get to this via code?

I am looking to get to this SQL to ensure that it is what I want it to be and I can use it in my tests

Paul

bugged87 · Accepted Answer · 2021-03-03T20:36:12.090

Assuming you're using Visual Studio, the debugger by default would be showing you the output of ToString() in the inspector for any given object.

With that knowledge, you could retrieve that same query string object with the following code.

var serializedQuery = baseQuery.ToString(); // "{{\"query\":\"SQL HERE\"}}"

The result appears to be a serialized JSON object that wraps the actual SQL query. You can easily extract the SQL with Newtonsoft.Json using the following code.

var sql = JObject.Parse(serializedQuery)["query"].Value<string>(); \\ "SQL HERE"

EDIT: In current versions of the SDK, the solution would look like the following instead.

var baseQuery = container
               .GetItemLinqQueryable<Car>(
                    requestOptions: new QueryRequestOptions
                    {
                        MaxItemCount = maxItemCount,
                        PartitionKey = new PartitionKey(partitionKey)
                    }
                )
               .Where(order => car.ModelName == modelName);

var sql = baseQuery.ToQueryDefinition().QueryText; // "SQL HERE"

Get to underlying SQL for Cosmos DB Query generated via LINQ

1 Answers1