Azure SQL trigger item contains null values after conversion to isolated model

Question

I am trying to convert an Azure function from .Net6.0 and In-process model, to .Net8.0 and Isolated model.

The function uses Azure SQL triggers on an Azure SQL server database with change tracking enabled. When the triggers fire updates are made to a container in a Cosmos DB with the changed data.

This is working fine using .Net6.0 and in-process model of the function. Using .net8 and isolated model, the trigger fires when changes are made to the tables, but the item contains null or default values for all of the fields. The same model and Dbcontext are being used in both the in-process and isolated versions.

public async Task RunGroupTransformAsync([SqlTrigger("[FACETS].[CMC_GRGR_GROUP]", "CONNECTION-AZURE-SQL-RAW")] IReadOnlyList<SqlChange<CmcGrgrGroup>> changes,
 FunctionContext context)
{
   foreach (SqlChange<CmcGrgrGroup> change in changes)
   {  
     var logger = context.GetLogger("CmcGrgrGroup"); 
     logger.LogInformation($"Group Change operation: {change.Operation}");
     logger.LogInformation($"GrgrCk: {rawGroup.GrgrCk}");
     CmcGrgrGroup rawGroup = change.Item; 
     //Update Cosmos Container with rawGroup
    }
}

Running locally and debugging:

Many of the fields in the Items should have values but none do.

Thank you Suresh, this solution did work for me, however I did have to change my code to use System.text.Json rather than the standard Newtonsoft.json that my company has adapted. Once I got it working I tried to revert back to Newtonsoft.Json and try to implement similar global settings, but it did not work. Code is below if anyone is interested in trying to get it to work with Newtonsoft.

  // Configure global JSON serialization settings using Newtonsoft.Json
    JsonConvert.DefaultSettings = () => new JsonSerializerSettings
    {
        NullValueHandling = NullValueHandling.Ignore,
        DefaultValueHandling = DefaultValueHandling.IgnoreAndPopulate,
        Formatting = Formatting.None,
        ContractResolver = new DefaultContractResolver
        {
            NamingStrategy = new SnakeCaseNamingStrategy()
            {
                ProcessDictionaryKeys = true,
                OverrideSpecifiedNames = true
            }
        },
        MissingMemberHandling = MissingMemberHandling.Ignore

I tried to upvote your answer, but I am too new and do not have permission.

Answer 1

Marcelo, Anand Thanks for your input.

• In the isolated worker model, you can specify JsonSerializerOptions globally for your function. Configure the options to handle null values, property naming, and other serialization-related settings.

var host = new HostBuilder()
    .ConfigureFunctionsWorkerDefaults()
    .ConfigureServices(services =>
    {
        services.AddHttpClient();
        // Define your global custom JSON serializer options
        services.Configure<JsonSerializerOptions>(options =>
        {
            options.AllowTrailingCommas = true;
            options.DefaultIgnoreCondition = JsonIgnoreCondition.WhenWritingNull;
            options.PropertyNamingPolicy = JsonNamingPolicy.CamelCase;
            options.PropertyNameCaseInsensitive = true;
        });
    });

I have modified the logging configuation that the logs are capturing the expected information.

public async Task RunGroupTransformAsync([SqlTrigger("[FACETS].[CMC_GRGR_GROUP]", "CONNECTION-AZURE-SQL-RAW")] IReadOnlyList<SqlChange<CmcGrgrGroup>> changes,
    FunctionContext context)
{
    var logger = context.GetLogger("CmcGrgrGroup");

    foreach (SqlChange<CmcGrgrGroup> change in changes)
    {
        CmcGrgrGroup rawGroup = change.Item;

        logger.LogInformation($"Group Change operation: {change.Operation}");
        logger.LogInformation($"GrgrCk: {rawGroup?.GrgrCk}");

        // Update Cosmos Container with rawGroup
        await UpdateCosmosContainerAsync(rawGroup, logger);
    }
}

private async Task UpdateCosmosContainerAsync(CmcGrgrGroup rawGroup, ILogger logger)
{
    try
    {
        // Your Cosmos DB update logic here
        // Make sure to use rawGroup and logger as needed
        // ...
    }
    catch (Exception ex)
    {
        logger.LogError($"Error updating Cosmos Container: {ex.Message}");
        // Handle the exception accordingly
    }
}

Sample data taken for Azure SQL server Database:

Here I have used the Dotnet 8 version including Azure Functions and related libraries, are updated to versions compatible with .NET 8.0.

Reference:

• JsonSerializerOptions in the Isolated model.

• Azure Functions supports .NET 8 in the isolated worker model.