Entity Framework JSON Search with NpgSql

21 Views Asked by At

I have an Entity Framework model class like below and as you see it is used with Postgres.

[Table]
public class User
{
    [Key]
    public Guid Id { get; set; }
    public Guid DisplayName { get; set; }
    [Column(TypeName = "jsonb")]
    public List<string> Tags { get; set; }
}

When I try to search an array of string in the column "Tags" I encounter several exceptions like

  • npgsql operator does not exist: jsonb && jsonb
  • query could not be translated (I tried EF.Functions.JsonExistsAny and etc.)

Data structure in the column is like ["apple", "banana", "melon", "grape"] as JSON

I am trying to search rows that are containing ["apple", "melon"]

My query is

var searchItems = new string[] { "apple", "melon" };
var option1 = dbContext.Users.Where(t => searchItem.Any(a => t.Tags.Contains(a))).ToList();
var option2 = dbContext.Users.Where(t => searchItem.Any(a => EF.Functions.JsonExistAny(a.Tags,)).ToList();

I tried to write various queries but stuck with it. Do you see any weird thing?

0

There are 0 best solutions below