Snake Case & JSON Errors In EF Core: A Bug?

Hey everyone! Let's dive into a tricky issue some of us might encounter while using Entity Framework Core (EF Core) with Npgsql and JSON complex properties. Specifically, we're going to break down an error that pops up when you combine UseSnakeCaseNamingConvention() with JSON mapping for complex properties. If you're scratching your head over this, you're in the right place! We'll explore the problem, understand why it happens, and discuss potential solutions.

The Problem: A Clash of Conventions

So, here's the scenario: You're rocking .NET and EF Core (version 10 RC 2, in this case), using Npgsql (version 10-rc.2) to connect to your PostgreSQL database. You're a fan of the UseSnakeCaseNamingConvention(), aiming for that clean, snake_case style in your database columns. You've also got some snazzy complex properties in your entities that you want to map as JSON columns in your database. You define a class like AddressInfo, with properties like GooglePlaceId, Street, CityState, and so on. You then tell EF Core to treat this AddressInfo as a JSON property using .ToJson(). Seems straightforward, right?

But then, disaster strikes! When you try to generate a migration, you're greeted with this cryptic error message:

Unable to create a 'DbContext' of type 'ApplicationDbContext'. The exception 'Property 'Event.AddressInfo#AddressInfo.CityState' cannot have both a column name ('address_info_city_state') and a JSON property name ('CityState') configured. Properties in JSON-mapped types should use JSON property names, not column names.' was thrown while attempting to create an instance. For the different patterns supported at design time, see [https://go.microsoft.com/fwlink/?linkid=851728](https://go.microsoft.com/fwlink/?linkid=851728)

Ouch! It sounds like EF Core is getting confused about whether to use the snake_case column name (address_info_city_state) or the original property name (CityState) within the JSON structure. The error message suggests that properties within JSON-mapped types should stick to JSON property names, not column names. But you haven't explicitly configured any column names using HasColumnName(). What's going on?

Diving Deeper: Why This Happens

The root of the problem lies in how UseSnakeCaseNamingConvention() interacts with JSON property mapping. When you use UseSnakeCaseNamingConvention(), you're instructing EF Core to automatically convert your PascalCase property names (like CityState) to snake_case column names (like city_state) in the database. This is a global setting that affects all entities and properties in your context.

However, when you map a complex property to JSON using .ToJson(), you're essentially telling EF Core to serialize the object into a JSON string and store it in a single column. Inside that JSON string, you typically want the property names to remain as they are in your C# class (e.g., CityState), not transformed into snake_case.

The conflict arises because UseSnakeCaseNamingConvention() tries to apply its naming transformation to all properties, including those within the JSON-mapped complex property. EF Core then detects this conflict – it sees that a property within a JSON column is trying to have both a column name (from the snake_case conversion) and a JSON property name (the original PascalCase name). Hence, the error!

Illustrating with an Example

Let's break it down with our AddressInfo class example:

public class AddressInfo {
 public string GooglePlaceId { get; set; } = null!;
 public string Street { get; set; } = null!;
 public string CityState { get; set; } = null!;
 public string CountryCode { get; set; } = null!;
 public string? Description { get; set; }
}

Without UseSnakeCaseNamingConvention(), EF Core would map this to a JSON column, and the JSON would look something like this:

{
 "GooglePlaceId": "some_place_id",
 "Street": "123 Main St",
 "CityState": "Anytown, CA",
 "CountryCode": "US",
 "Description": "A nice place"
}

But with UseSnakeCaseNamingConvention(), EF Core also tries to apply snake_case to the properties inside the JSON. This is where the problem lies. It's trying to reconcile CityState (the JSON property name) with a potential address_info_city_state (the snake_cased column name), which leads to the error. The core issue is that we want snake_case for the database column itself, but not for the properties serialized within the JSON.

Identifying the Culprit: The Migration Builder

The error message itself gives us a clue. It mentions that the issue occurs while trying to create a DbContext instance, which usually happens during migration generation. This tells us that the problem isn't just at runtime; it's at design time when EF Core is figuring out how to map your entities to the database schema. The migration builder is getting tripped up by the conflicting naming conventions.

Solutions: Taming the Snake Case

Okay, so we understand the problem. Now, how do we fix it? There are a few strategies we can use to resolve this conflict. The best approach will depend on your specific needs and preferences, but let's explore the most common options.

1. Selective Snake Case: Fluent API to the Rescue

The most elegant solution is often to be more selective about where you apply the snake_case convention. Instead of using UseSnakeCaseNamingConvention() globally, we can use the Fluent API to configure snake_case naming for specific table and column names. This gives us fine-grained control and avoids the conflict with JSON property names.

Here's how you can do it:

Instead of:

optionsBuilder.UseNpgsql(connectionString, o =>
 o.UseSnakeCaseNamingConvention());

Do this in your OnModelCreating method:

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
 modelBuilder.Entity<YourEntity>(entity =>
 {
 entity.ToTable(