Issue with serialized Json Array/Lists

[Felipe379]

Recently I've been trying to upgrade to the lastest EF 7, since its said to support serialized Json columns, however, I didn't find any info about what are the current limitations.

My question is, does it currently supports serialized json Arrays or Lists? I'm going to try to write a simple example

Model:

	public class User
	{
		public Guid Id { get; set; }
		public string Name { get; set; }
		//Other properties
		public List<Comments> Comments { get; set; }
	}

	public class Comments
	{
		public Guid UserId { get; set; }
		public string UserName { get; set; }
		public string Message { get; set; }
		public DateTimeOffset Date { get; set; }
		public CommentType CommentType { get; set; }
	}

Configuration:

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

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

Query:

	var usersWithComments = await _dbContext.Users
		.Where(u => u.Comments.Any())
		.Select(u => u.Id)
		.ToListAsync(cancellationToken);

Often I ended up getting an exception similar of those:

The LINQ expression 'DbSet<Users>()
	.Where(d => EF.Property<List<Comments>>(d, "Comments")
		.AsQueryable()
		.Any())' could not be translated. Either rewrite the query in a form that can be translated, or switch to client evaluation explicitly by inserting a call to 'AsEnumerable', 'AsAsyncEnumerable', 'ToList', or 'ToListAsync'. See https://go.microsoft.com/fwlink/?linkid=2101038 for more information.

In this case, List<Comments> Comments should be stored in the database as a serialized Json column in the User's table
Here is an example of the json:

[
  {
    "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'm not quite sure if it's currently a limitation or if I'm just missing some configuration.

[maumar]

Currently you can store data as List/Array of types mapped to JSON, like in your configuration. Problem is in the query - we don't support Any or any other queryable operation on JSON arrays. Support for these is tracked here: #28616

At the moment you can:

• traverse to the scalar property in JSON aggregate and use it anywhere in the query,
• project entity (and all it's related navigations) mapped to JSON,
• project collection mapped to JSON (and all the related navigations of the elements).

[Felipe379]

Hmm, I see. Most of the problem right now comes from the migration from previous versions since it was possible to just apply some filters in the JSON and then do a projection.

Often configuration would be similar to this:

	protected override void OnModelCreating(ModelBuilder modelBuilder)
	{
		modelBuilder.Entity<User>(u =>
		{
			u.ToTable("User");
			u.HasKey(x => x.Id);
			u.Property(c => c.Comments)
				.HasConversion(new JsonPropertyConversion<ImmutableList<Comments>>());
		});
	}

Anyway, looking foward for this feature to be implemented.

In meantime, assuming that Owned such as OwnsMany will be required in the future for JSON arrays, would be possible to implement its configration for SQL views?

	protected override void OnModelCreating(ModelBuilder modelBuilder)
	{
		modelBuilder.Entity<VwUserQuery>(u =>
		{
			u.ToView("vwUserQuery");
			u.HasNoKey();
			u.OwnsMany(c => c.Comments, ownedType =>
			{
				ownedType.Property(d => d.CommentType)
					.HasConversion(new EnumToNumberConverter<CommentType, int>());

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

Asking because as far I know, Owned types need a primary key.

[maumar]

In EF 6 JSON support was done through a workaround using converter (presumably to a string), so from EF6 perspective JSON object was just a scalar and some things would translate. In EF7 we decided to represent them as owned types so translation is much more complicated (and missing for now).

In the meantime, you can still use the old approach if that's blocking your migration to EF7 - it should work just as well.

wrt question about views - you can already have JSON entities where the owner is mapped to a view, but all the JSON entities must also map to the same view.

[roji]

Duplicate of #28616