ICollection.Contains(...) on Json Column defined as a ICollection doesn't produce JSON_CONTAINS

[idavidmarshali]

Consider this entite:

public class House{
     [Key]
     public int Id {get;set;}
}

public class Humans {
      [Key]
     public int Id {get;set;}
     [ColumnType(TypeName="json")]
     public List<int> HouseIds {get;set;} = [];
}

When we try to project this into another object like so:

context.Houses.Select(h => new {
    Count = context.Humans.Count(e => e.HouseIds.Contains(h.Id))
    Id = h.Id
} );

I Get the following error:

[System.InvalidOperationException: The LINQ expression 'DbSet<Leads>()
    .Count(l => EF.Property<List<int>>(l, "HouseIds")
        .AsQueryable()
        .Contains(id))' 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'.

Am i doing something wrong or the provider doesn't create a JSON_CONTAINS() query for List.Contains()?

because if i rewrite the query to:

context.Houses.Select(h => new {
    Count = context.Humans.Count(e => EF.Functions.JsonContains(e.HouseIds, h.Id))
    Id = h.Id
} );

it works fine and generates a correct SQL query.

Further Information:

• Using Mariadb 10
• Pomelo.EntityFrameworkCore.MySql Version="8.0.2"
• Pomelo.EntityFrameworkCore.MySql.Json.Microsoft Version="8.0.2"
• Microsoft.EntityFrameworkCore Version="8.0.2"

[lauxjpn]

Am i doing something wrong or the provider doesn't create a JSON_CONTAINS() query for List.Contains()?

There is indeed currently no query translation for the Contains() method when using our own JSON support. Using EF.Functions.JsonContains() instead is the way to go.

[idavidmarshali]

Thankyou for the response.
Is there a plan to add this to the translation?

[lauxjpn]

I think our next JSON endeavors will be to implement ToJson() support (issue #1752).
Once we have done that, we will take another look at or own JSON support again.

You can leave this open, so we don't forget to take another look at it.

[idavidmarshali]

can you suggest a starting point that i could start reading from and get myself familiar with this project and how it works so maybe i could become a contributor and help in the process of json support? (i don't exactly know where in the code i should start)

[lauxjpn]

@idavidmarshali Sure, just open an issue about it, so we have a central place for others to discover the information in the future.