In EF Core how can I efficiently check if text column of Json array contains any number from passed integer array using LINQ?
Example of table, where Id is integer type and Name and TypeJson are text
| Id | Name | TypeJson |
| --- | ---------- | -------- |
| 1 | Name One | [1,2] |
| 2 | Name Two | [2,3] |
| 3 | Name Three | [4,7] |
In Postgresql I would have written something like this
SELECT *
FROM "Table"
WHERE translate("TypeJson", '[]','{}')::int[] && ARRAY[1, 7]
where the select would return 1 and 3 rows.
I'd like to achieve same result by using LINQ functions.
I tried using EF.Functions but didn't achieve much. My attempt
await _dbContect.Table
.Where(x => !string.IsNullOrEmpty(x.TypeJson ) &&
EF.Functions.JsonContains(x.TypeJson , "[1]")
.ToListAsync();
But it produces error as column is type of text and not Json
System.InvalidOperationException: The EF JSON methods require a JSON parameter and none was found.
The entity:
public class Table
{
public int Id { get; set; }
public string Name { get; set; }
public string TypeJson { get; set; }
}
Using FromSqlRaw() is not possible because there is already written code and would be preferable if I didn't have to rewrite whole code block.
