在CosmosDB中避免使用UpsertAsync：执行SQL更新命令

Absolutely! You can perform partial updates on Cosmos DB documents to modify just the companies array without replacing the entire document. This is exactly what the Cosmos DB Patch API is designed for, and it’ll drastically improve your performance compared to full upserts.

How to Implement Partial Updates for the companies Array #

Instead of using UpsertItemAsync (which serializes and sends the entire document), you’ll use PatchItemAsync with a Replace operation targeting the /companies path. This only sends the updated companies array over the wire, cutting down on payload size and serialization time.

Here’s how to modify your code:

public async Task ChangeCompaniesAsync()
{
    await ConnectToDatabaseAsync();
    await GetItemsFromCosmosAsync().ConfigureAwait(false);
    RandomizeCompanies();
    try
    {
        Console.WriteLine($"Starting...");
        var stopwatch = Stopwatch.StartNew();
        var tasks = new List<Task>(_items.Count);
        
        foreach (var item in _items)
        {
            // Define the patch operation to replace the companies array
            var patchOperations = new List<PatchOperation>
            {
                PatchOperation.Replace("/companies", item.companies)
            };
            
            // Optional: Enable upsert if you want to create the document if it doesn't exist
            var requestOptions = new PatchItemRequestOptions { EnableUpsert = true };
            
            // Add the patch task to the list
            tasks.Add(_cosmosConnection.Container.PatchItemAsync<YourDocumentType>(
                item.id, 
                new PartitionKey(item.Id), 
                patchOperations,
                requestOptions));
        }
        
        await Task.WhenAll(tasks);
        stopwatch.Stop();
        Console.WriteLine($"Finished writing {_items.Count} items in {stopwatch.Elapsed}.");
    }
    catch (Exception ex)
    {
        Console.WriteLine(ex);
    }
    finally
    {
        Console.WriteLine("Cleaning up resources...");
    }
}

Key Benefits of This Approach #

• Reduced Payload: Only the companies array is sent, not the entire document. This is a huge win if your documents have large, unchanged fields.
• Faster Serialization: Less data means less time spent converting between objects and JSON.
• Lower Latency: Smaller payloads reduce network transfer time, cutting down your overall operation duration from minutes to seconds (depending on document size).

Important Notes #

• Case Sensitivity: The path /companies must match the exact case of the property in your document (Cosmos DB is case-sensitive for JSON paths).
• SDK Version: Ensure you’re using the latest version of the Microsoft.Azure.Cosmos NuGet package to access the Patch API.
• Error Handling: If a document doesn’t exist and you haven’t enabled EnableUpsert, PatchItemAsync will throw a NotFoundException. Adjust the request options based on your needs.

内容的提问来源于stack exchange，提问作者Paul