Query nested jsonb with LINQ

Question

I have an entity Advisor with a column of type jsonb.

[Column(TypeName = "jsonb")] 
public ExpertiseArea ExpertiseArea { get; set; } = new();

public class ExpertiseArea
{
    public List<TripCountry> TripCountries { get; set; } = new List<TripCountry>();
}

public class TripCountry
{
    public int Id { get; set; }
    public string Name { get; set; }
}

Assume the following LINQ query:

var query = query.Where(a 
    => a.ExpertiseArea.TripCountries.Any(tc 
        => queryParams.Directions.Contains(tc.Id)))

queryParams.Directions is an int[].

I want to select Advisor entities where ExpertiseArea.TripCountries contain each of queryParams.Directions int values.

Here is the JSON example:

{   
    "TripCountries": [
        { "Id": 3, "Name": "Austria" }, 
        { "Id": 83, "Name": "France" }, 
        { "Id": 6, "Name": "Poland" }
    ]
}

For example, queryParams.Directions is [3,6].

I want all the advisors with TripCountries having both Poland and Austria.

I tried to write it with EF.Functions but I'm not sure which exact function to use.

I tried to resolve it with ChatGPT, but it gives wrong answers only.

Any help appreciated.

Maybe you can pull the useful information from my answer here: stackoverflow.com/questions/68439395/… — Mitko Keckaroski
– Mitko Keckaroski, Commented Jul 25, 2023 at 6:36
Thanks for reply. Well, JsonContains is not solution for me, because I need check for a list of values, not a single value — Akhmed
– Akhmed, Commented Jul 25, 2023 at 6:51
Added an answer, based on JsonContains. It works, but I still believe there is more elegant solution. Thank you — Akhmed
– Akhmed, Commented Jul 25, 2023 at 7:06

Akhmed · Accepted Answer · 2023-07-25 07:05:34Z

2

Didn't found an elegant solution but using EF.Functions.JsonContains we can compare to a single value. And with foreach we can add multiple comparations.

This is what worked for me:

        string contained;
        foreach (var direction in queryParams.Directions)
        {
            contained = $"[{{\"Id\": {direction}}}]";
            query = query.Where(a => EF.Functions.JsonContains(a.ExpertiseArea.TripCountries, contained));
        }

answered Jul 25, 2023 at 7:05

Akhmed

1,2194 gold badges24 silver badges52 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Query nested jsonb with LINQ

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related