[Question] Configuring json columns

[Felipe379]

I've been reading the documentation for EF8, since we're upgrading to NET8 and its necessary to make some changes, mostly regarding json structures in our database.

There are several different fields that are serialized. Here are some very rough examples of their data:

[
	"DATA_1",
	"DATA_2",
	"DATA_3"
]

[
	{
		"Email": "test@test.com",
		"IsValid": false,
	},
	{
		"Email": "customer1@gmail.com",
		"IsValid": true,
		"Username": "Customer",
		"CategoryEnum": "Enum_GoodCustomer",
	}
]

{
	"Id": 0,
	"Name": "Customer",
	"Address": {
		"Country": "US"
	},
	"Vehicles": [
		{
			"TypeEnum": 15,
			"ClassEnum": "Enum_Imported",
			"Brand": "Ford",
			"Color": "White"
		},
		{
			"TypeEnum": 7,
			"Brand": "Ford",
			"Year": 2020
		}
	],
	"Phone": [
		"0000000",
		"1111111",
		"2222222"
	]
}

Json columns are configurated like:

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
	modelBuilder.Entity<User>(u =>
	{
		u.ToTable("User");
		u.HasKey(x => x.Id);
		u.Property(c => c.Emails)
			.HasColumnName("Emails")
			.HasConversion(new JsonPropertyConversion<ImmutableList<Email>>());
		u.Property(c => c.RandomObject)
			.HasColumnName("RandomObject")
			.HasConversion(new JsonPropertyConversion<RandomObject>());
		u.Property(c => c.TypeEnum)
			.HasColumnName("Type")
			.HasConversion(new EnumToStringConverter<TypeEnum>());
	});
}

I'm almost certanly not all of those cenarios are supported to be queryable (such as .Contains(), .Any()), so I have some questions.

• From all of the json shown, which one are supported to be queryable?
• Are those supported only in tables or are they also supported in keyless entities such as views?
• How should I change my model so I can migrate to NET8 with EF8 and have those columns to be queryable?

[DEAN-Cherry]

You can follow the doc here using toJson() method
https://learn.microsoft.com/en-us/ef/core/what-is-new/ef-core-7.0/whatsnew#json-columns
Everything works fine Except when you try to use a List of entity types(or dictionary) as the top-level JSON property, which is what i am confused until now.

Especially something like this #28688 (comment)

[Felipe379]

You can follow the doc here using toJson() method https://learn.microsoft.com/en-us/ef/core/what-is-new/ef-core-7.0/whatsnew#json-columns Everything works fine Except when you try to use a List of entity types(or dictionary) as the top-level JSON property, which is what i am confused until now.

Especially something like this #28688 (comment)

Thanks for your reply. I'll take a look.

I was reading about primitive collections here: https://learn.microsoft.com/en-us/ef/core/what-is-new/ef-core-8.0/whatsnew#primitive-collections

From what I could understand, those should be queryable without the need of any additional converters, right?
Because I've stumbled in this issue:

//returns nothing
var data1 = _dbContext.Data
             .Where(d => d.DataObject.Contains("MY_TEST"))
             .ToList();

//returns results
var data2 = _dbContext.Data
             .Where(d => d.DataObject.Any(x => x == "MY_TEST_STRING"))
             .ToList();

Only the .Any() returns some data, .Contains() doesn't despite we having such string in the database.

Something else I did notice, is that, it doesn't seems to be compatible with ImmutableLists? We used those back in the previous version so EF could detect changes made in certain json columns.

[Felipe379]

There's also an structure similar to:

[
  {
    "UserId": "00000000-0000-0000-0000-000000000000",
    "UserName": "Test1",
    "Message": "User comment test",
    "Date": "2022-11-24T10:00:00.0000000-00:00",
    "CommentType": 0
  },
  {
    "UserId": "11111111-1111-1111-1111-111111111111",
    "UserName": "Test2",
    "Message": "Adm comment test.",
    "Date": "2022-11-25T08:00:00.00000000-00:00",
    "CommentType": 1
  }
]

I assumed the configuration would be such as:

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
	modelBuilder.Entity<User>(u =>
	{
		//More properties configuration
		u.OwnsMany(c => c.Comments, ownedType =>
		{
			ownedType.Property(d => d.CommentType)
				.HasConversion(new EnumToNumberConverter<CommentType, int>());

				ownedType.ToJson();
		});
	});
}

However, querying over it gives an error:
'This SqlTransaction has completed; it is no longer usable.'

Yet, querying over the same entity when its not an array in another entity:

{
  "UserId": "11111111-1111-1111-1111-111111111111",
  "UserName": "Test2",
  "Message": "Adm comment test.",
  "Date": "2022-11-25T08:00:00.00000000-00:00",
  "CommentType": 1
}

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
	modelBuilder.Entity<User2>(u =>
	{
		//More properties configuration
		u.OwnsOne(c => c.Comments, ownedType =>
		{
			ownedType.Property(d => d.CommentType)
				.HasConversion(new EnumToNumberConverter<CommentType, int>());

				ownedType.ToJson();
		});
	});
}

Work just as intended. I thought querying over such types would be possible given the issue #28616 was completed, unless I missed some configuration.

[ajcvickers]

Because I've stumbled in this issue

Please file a new issue and attach a small, runnable project or post a small, runnable code listing that reproduces what you are seeing so that we can investigate.

However, querying over it gives an error: 'This SqlTransaction has completed; it is no longer usable.'

Please file a new issue and attach a small, runnable project or post a small, runnable code listing that reproduces what you are seeing so that we can investigate.

...to NET8 and its necessary to make some changes,

Can you explain a bit more why it is necessary to make some changes? What was it that broke, specifically?

From all of the json shown, which one are supported to be queryable?
Are those supported only in tables or are they also supported in keyless entities such as views?
How should I change my model so I can migrate to NET8 with EF8 and have those columns to be queryable?

This is impossible to answer. It depends also on the shape of the model, the specific mapping, and the database provider.

[Felipe379]

Please file a new issue and attach a small, runnable project or post a small, runnable code listing that reproduces what you are seeing so that we can investigate.

Please file a new issue and attach a small, runnable project or post a small, runnable code listing that reproduces what you are seeing so that we can investigate.

Not sure how that would help, as I just provided some examples of how the model is configurated.

Can you explain a bit more why it is necessary to make some changes? What was it that broke, specifically?

Since the removal of EF Core client evaluation, we had to stay in previous version of EF due to the json columns. I've seen some issues regarding jsons getting resolved for the EF8 and that made me try to actually migrate to the lastest version of EF8 and NET8. Unless you are saying that I can still keep the old configuration, which currently is just a serializer.

This is impossible to answer. It depends also on the shape of the model, the specific mapping, and the database provider.

Please can you provide what other details do you need in order to actually be possible to answer this?

[ajcvickers]

Unless you are saying that I can still keep the old configuration, which currently is just a serializer.

Absolutely.

Please can you provide what other details do you need in order to actually be possible to answer this?

If you provide a model, a query, and a provider, then I could tell you. But it would be much quicker for you to just test it.

[ajcvickers]

EF Team Triage: Closing this issue as the requested additional details have not been provided and we have been unable to reproduce it.

BTW this is a canned response and may have info or details that do not directly apply to this particular issue. While we'd like to spend the time to uniquely address every incoming issue, we get a lot traffic on the EF projects and that is not practical. To ensure we maximize the time we have to work on fixing bugs, implementing new features, etc. we use canned responses for common triage decisions.

[Felipe379]

EF Team Triage: Closing this issue as the requested additional details have not been provided and we have been unable to reproduce it.

BTW this is a canned response and may have info or details that do not directly apply to this particular issue. While we'd like to spend the time to uniquely address every incoming issue, we get a lot traffic on the EF projects and that is not practical. To ensure we maximize the time we have to work on fixing bugs, implementing new features, etc. we use canned responses for common triage decisions.

I'm so sorry. I did set up a project some weeks ago but due to a lot unforeseen events, I wasn't able to keep up with this issue.

In the end, with except the one that gave me the error: This SqlTransaction has completed; it is no longer usable.; I was able to reproduce every issue in this new test api: https://github.com/Felipe379/JsonTestAPI

You can find every experiment that I did in this test project in JsonTestAPI/JsonTestAPI/Controllers/

I'm still unsure if I should create a new issue as I may have messed up the configuration files, but if it's confirmed it's an issue with EF, I agree and it makes sense to create new individual issues.