How can I trace the query produced by the documentdb Linq provider?

Question

How can I see the document DB sql query (in a string) generated by a linq statement before it gets sent to the server?

_documentClient.CreateDocumentQuery<MyType>(
                        UriFactory.CreateDocumentCollectionUri(DatabaseName,
                            CollectionName)).Where(....).SelectMany(...)

I want to use this for tracing purposes.

Lunn

The following link shows you how to do so: #2026212 – Disoperation

My question is about azure documentdb, do you recon anything on the link you posted is related? – Lunn

Answer 1

You can call ToString() on the DocumentDB query to get the SQL translation of the LINQ expression that's sent over the wire.

string sql = client.CreateDocumentQuery<MyType>(collectionUri).Where(t => t.Name = "x").ToString();
// sql is somthing like SELECT * FROM c WHERE c["Name"] = "x"

Fleeman

This doesn't work if you use Count() I have found. – Tropho

Answer 2

You could just use Fiddler and view the JSON of the request after it is sent.

View an example here:

CosmosDB\DocumentDB Generated SQL Query

Riarial

Answer 3

The accepted answer is kind of weird : It means that you have to log each and every piece of Linq code you made.

(Using AspNetCore DI). Knowing that the Microsoft.Azure.Cosmos.CosmosClient contains an HttpClient, the first thing we can do is search if we can pass our own HttpClient.

And we can. For example, via CosmosClientBuilder and using the injected IHttpClientFactory :

.WithHttpClientFactory(() => httpClientFactory.CreateClient("Client"))

If your log filters are well configured, you'll see the Request / Response in the console.

Now, we could either add a DelegatingHandler to our HttpClient this way in ConfigureServices or builder.Services in .NET 6 :

services.AddTransient<NoSqlLoggingDelegatingHandler>();
services.AddHttpClient("Client")
    .AddHttpMessageHandler<NoSqlLoggingDelegatingHandler>();

or find out if CosmosClient has the option to add our own, and of course it does (Example via CosmosClientBuilder).

.AddCustomHandlers(
    new NoSqlLoggingDelegatingHandler(loggerFactory.CreateLogger<NoSqlLoggingDelegatingHandler>()))

With this, we can intercept the Request and Response sent :

public override async Task<ResponseMessage> SendAsync(RequestMessage request, CancellationToken cancellationToken)
{
    _logger.LogInformation("Requesting {uri}.\n{query}",
            request.RequestUri, await GetQueryAsync(request.Content));

    ResponseMessage response = await base.SendAsync(request, cancellationToken);

    _logger.LogInformation("Requested {uri} - {status}",
    response.RequestMessage.RequestUri, response.StatusCode);

    return response;
}

And GetQueryAsync reads the Request's body Stream using System.Text.Json :

private static async ValueTask<string?> GetQueryAsync(Stream content)
{
    string? stringContents;

    // Create a StreamReader with leaveOpen = true so it doesn't close the Stream when disposed
    using (StreamReader sr = new StreamReader(content, Encoding.UTF8, true, 1024, true))
    {
        stringContents = await sr.ReadToEndAsync();
    }

    content.Position = 0;

    if (string.IsNullOrEmpty(stringContents))
    {
        return null;
    }

    try
    {
        using JsonDocument parsedJObject = JsonDocument.Parse(stringContents);

        return parsedJObject.RootElement.GetProperty("query").GetString();
    }
    catch (KeyNotFoundException)
    {
        return stringContents;
    }
    // Not a JSON.
    catch (JsonException)
    {
        return stringContents;
    }
}

And there you have it. I've tried to shortened the code, for example, I didn't add a condition to check if the logging level is enabled or not, to avoid doing useless work.

Papyrology