Sql trigger cannot deserialize types with properties stored as json

[bcrispcvna]

The SQL trigger fails when trying to deserialize types that have columns stored as Json that map to object types (for example an owned type that was stored as Json in EFCore). I tried adding a custom converter for handling this in the function startup but can't seem to override the default serialization behavior.

For now, I have two options for working around the issue. One method that worked was adding a class that specified the properties as strings, using that as the SqlChange<> type, then converting it to the target type in the function. I was also able to pass in JObject as the type for SqlChange<> and then use the custom converter inside of the JObject.ToObject method to convert.

https://github.com/Azure/azure-functions-sql-extension/blob/release/trigger/src/TriggerBinding/SqlTableChangeMonitor.cs#L705

[chlafreniere]

Hi @bcrispcvna, just to make sure that we're understanding correctly, would it be possible to provide a sample code snippet of what you're trying to do in the ideal case here?

[bcrispcvna]

@chlafreniere In our case we're using EFCore on some classes that have owned types stored as JSON and want to use sql trigger when records are inserted. Here's a simple example without using efcore that stores data in the same fashion. Let me know if you have any questions.

CREATE TABLE Contacts
(
    Id int PRIMARY KEY,
    Name nvarchar(255),
    Address nvarchar(MAX)
)

ALTER DATABASE MyDatabase
SET CHANGE_TRACKING = ON
(CHANGE_RETENTION = 2 DAYS, AUTO_CLEANUP = ON)

ALTER TABLE Contacts
ENABLE CHANGE_TRACKING
WITH (TRACK_COLUMNS_UPDATED = ON)

public class Contact
{
    public int Id { get; set; 
    public string Name { get; set; }
    public Address Address { get; set; }
}

public class Address
{
    public string Street { get; set; }
    public string City { get; set; }
    public string PostalCode { get; set; }
    public string Country { get; set; }
}

public class MyFunction
{
    [FunctionName(nameof(MyFunction))]
    public async Task Run(
    [SqlTrigger("[dbo].[Contacts]", "MySqlConnection")]
    IReadOnlyList<SqlChange<Contact>> changes)
    {
        // Currently this will fail during deserialization

        // Expectation is that a custom converter can be applied to the function, or the default converter
        // will perform a type comparison during deserialization, where if the property is a class and 
        // the sql value is a valid json string it deserializes it as the target property type
    }
}

INSERT INTO Contacts (Id, Name, Address)  
VALUES (1, 'John Doe', '{"Street":"123 Street","City":"City","PostalCode":"12345","Country":"Country"}')