JSON Column support

[statler]

Hi Simon

I note that v11 includes support for JSON columns. Could you provide some documentation on how to configure JSON columns so that we can get configs like

modelBuilder.Entity().ComplexProperty(b => b.Details, b => b.ToJson());

(from https://learn.microsoft.com/en-us/ef/core/what-is-new/ef-core-10.0/whatsnew)

Cant find anything in the docs, and cant figure it out from the code sorry

[sjh37]

Hi @statler
In SqlServerToCSharp I convert the SQL json type to a string

{ "json", "string" }, // SQL Server 2025 / Azure SQL native json type (string works for all EF versions)

The C# class type for a json column will be a string, so you can deserialize it to your internal type afterwards.
The main reason I can't map the json column directly to an object is because there is no way to know this by looking at the database. This is the first release to support json fields, and I would need to enhance the generator further in order for you to declare which json types map to which poco classes.

I will leave this open and use it to enhance the generator for proper JSON support.

[sjh37]

This is how its going to work, I've still got some work to do to it and will finish it this week / next,

JSON Column to POCO Class Mapping

Overview

This feature allows you to map JSON database columns directly to C# POCO classes instead of the default string type. This eliminates the need for manual deserialization in your application code and provides strong typing for your JSON data.

Supported Databases

• SQL Server: json type (SQL Server 2016+ and Azure SQL)
• PostgreSQL: json and jsonb types

Quick Start

1. Define Your POCO Classes

First, create the POCO classes that match your JSON structure:

namespace MyApp.Models
{
    public class Address
    {
        public string Street { get; set; }
        public string City { get; set; }
        public string PostalCode { get; set; }
        public string Country { get; set; }
    }
}

2. Configure the Mapping

In your settings (typically in your .tt file or Settings.cs), configure the JSON column mappings:

Settings.AddJsonColumnMappings = delegate (List<JsonColumnMapping> jsonColumnMappings)
{
    jsonColumnMappings.Add(new JsonColumnMapping
    {
        Schema = "dbo",
        Table = "Orders",
        Column = "ShippingAddress",
        PropertyType = "Address",
        AdditionalNamespace = "MyApp.Models"
    });
};

3. Generate Code

Run the generator as usual. The generated Order class will now have:

public Address ShippingAddress { get; set; }

instead of:

public string ShippingAddress { get; set; }

Configuration Options

JsonColumnMapping Properties

Property	Type	Description	Required
Schema	string	Database schema name. Use "*" to match any schema.	Yes
Table	string	Table name. Use "*" to match any table.	Yes
Column	string	Column name to map.	Yes
PropertyType	string	C# type to use (e.g., "Address", "List<string>", "Dictionary<string, object>").	Yes
AdditionalNamespace	string	Namespace(s) required for the type. Separate multiple namespaces with semicolons.	No

Wildcard Matching

You can use wildcards to apply mappings broadly:

// Map all "Metadata" columns across all tables and schemas
jsonColumnMappings.Add(new JsonColumnMapping
{
    Schema = "*",
    Table = "*",
    Column = "Metadata",
    PropertyType = "Dictionary<string, object>",
    AdditionalNamespace = "System.Collections.Generic"
});

Common Patterns

Pattern 1: Simple POCO

jsonColumnMappings.Add(new JsonColumnMapping
{
    Schema = "dbo",
    Table = "Users",
    Column = "Profile",
    PropertyType = "UserProfile",
    AdditionalNamespace = "MyApp.Models"
});

Pattern 2: Generic Collections

jsonColumnMappings.Add(new JsonColumnMapping
{
    Schema = "dbo",
    Table = "Products",
    Column = "Tags",
    PropertyType = "List<string>",
    AdditionalNamespace = "System.Collections.Generic"
});

Pattern 3: Dictionaries

jsonColumnMappings.Add(new JsonColumnMapping
{
    Schema = "dbo",
    Table = "Settings",
    Column = "Configuration",
    PropertyType = "Dictionary<string, object>",
    AdditionalNamespace = "System.Collections.Generic"
});

Pattern 4: Complex Nested Types

jsonColumnMappings.Add(new JsonColumnMapping
{
    Schema = "dbo",
    Table = "Reports",
    Column = "Data",
    PropertyType = "Dictionary<string, List<ReportItem>>",
    AdditionalNamespace = "System.Collections.Generic;MyApp.Models"
});

Pattern 5: Fully Qualified Type Names

jsonColumnMappings.Add(new JsonColumnMapping
{
    Schema = "dbo",
    Table = "Documents",
    Column = "Content",
    PropertyType = "MyCompany.Domain.Models.DocumentContent"
    // AdditionalNamespace is optional when using fully qualified names
});

Entity Framework Configuration

After generating your code, you need to configure Entity Framework to handle JSON serialization/deserialization.

EF Core

In your DbContext.OnModelCreating method:

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    // For simple types
    modelBuilder.Entity<Order>()
        .Property(e => e.ShippingAddress)
        .HasConversion(
            v => JsonSerializer.Serialize(v, (JsonSerializerOptions)null),
            v => JsonSerializer.Deserialize<Address>(v, (JsonSerializerOptions)null));

    // For nullable types
    modelBuilder.Entity<Order>()
        .Property(e => e.Metadata)
        .HasConversion(
            v => v == null ? null : JsonSerializer.Serialize(v, (JsonSerializerOptions)null),
            v => v == null ? null : JsonSerializer.Deserialize<Dictionary<string, object>>(v, (JsonSerializerOptions)null));
}

EF Core with Owned Types (Recommended for EF Core 8+)

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<Order>()
        .OwnsOne(o => o.ShippingAddress, sa =>
        {
            sa.ToJson();
        });
}

Important Notes

1. Type Compatibility: Ensure your POCO classes can be serialized/deserialized to/from JSON and match the structure in your database.

2. Namespace Requirements: If your type requires additional namespaces, specify them in AdditionalNamespace. The generator will automatically add them to the using statements.

3. Nullable Columns:

   • Reference types (classes) are nullable by default
   • Value types will be made nullable (T?) if the database column allows NULL

4. Default Values: Default values are cleared for JSON columns mapped to custom types, as they're typically not meaningful for complex types.

5. Entity Framework Configuration: You must configure EF to handle serialization/deserialization. The generator creates the property with the correct type but doesn't add EF configuration automatically.

6. Performance: Be aware that deserializing JSON on every query may have performance implications. Consider using projections or lazy loading patterns when appropriate.

Implementation Details

Files Modified

• JsonColumnMapping.cs - New class defining the mapping structure
• Settings.cs - Added AddJsonColumnMappings callback and mapping logic
• SingleContextFilter.cs - Initialize and pass JSON mappings
• MultiContextFilter.cs - Updated to support new signature

How It Works

1. During code generation, the generator reads database schema including column types
2. For each column with SQL type json or jsonb, it checks for a matching JsonColumnMapping
3. If a match is found:
   • The column's PropertyType is changed from string to the specified type
   • Additional namespaces are added to the file's using statements
   • Default values are cleared
4. The generated code includes the strongly-typed property

Examples

See JsonColumnMappingExamples.cs for comprehensive examples of various usage scenarios.

Troubleshooting

Q: My custom type isn't recognized

• Ensure the namespace is correct in AdditionalNamespace
• Verify the type is accessible in your project
• Check that you're using the correct casing

Q: EF throws serialization errors

• Verify you've configured EF to handle JSON serialization (see "Entity Framework Configuration" above)
• Ensure your POCO structure matches the JSON in the database

Q: The mapping isn't being applied

• Verify the schema, table, and column names match exactly (case-insensitive comparison is used)
• Check that the column is actually a json or jsonb type in the database
• Ensure you're calling the generator after configuring the mappings

[statler]

Will this work now and it is just the documentation waiting to catch up? Or will this be a new release?

Also;

1. Can you comment on .OwnsOne vs the MS documentation .ComplexProperty
2. In your example Shouldn't this be PropertyType = "List<type_of_tag_column>"
   Pattern 2: Generic Collections

jsonColumnMappings.Add(new JsonColumnMapping
{
    Schema = "dbo",
    Table = "Products",
    Column = "Tags",
    ---> PropertyType = "List<string>", <----
    AdditionalNamespace = "System.Collections.Generic"
});

[sjh37]

This will be a new release, as shown in the changeset above. Not tested yet either so there may still be some more changes.

[statler]

I just thought I would mention (as I am currently dealing with it), that the JSON typing can apply to stored procedures and functions as well. My current hack until your update is to update the property type on the table

        if (table.NameHumanCase.Equals("SpatialModelElementOverride"))
        {
            if (column.NameHumanCase == "InteractionEffects")
            {
                column.PropertyType = "List<cpModel.Models.JSON.SpatialElementInteractionEffect>";
            }
        }

But I cant get this to work for where it is returned in my tablevaluefunction.

I don't know if the difference between object types is an issue with what you are working on - but thought I would bring it up.

[sjh37]

Stored procedure mapping is very particular. columns have to match the C# class exactly, matching case 1:1 etc. So I am not sure if the column types would map to JSON tbh.

[statler]

Stored procedure mapping is very particular. columns have to match the C# class exactly, matching case 1:1 etc. So I am not sure if the column types would map to JSON tbh.

Thanks Simon - at first I didn't get what you mean. Then I was 'surely not'. But for anyone reading this thread, as of now, apparently with EF Core 10’s native JSON column support, the big improvement is that EF can map complex properties directly to a single JSON column (including SQL Server 2025’s native json type).

But that improvement is primarily about entity types mapped to tables (and, generally, other “store objects” where EF has a proper relational mapping). A TVF result is still a read-only, keyless projection in EF terms, so you still don’t get the same “map complex type to JSON column” experience you get on a table entity. Keyless/query mappings remain query-only