Linq - How to query json inside Linq

Question

I have this code:

    List<int> cats = new List<int>();

    foreach (var cat in ctx.TblCategories)
    {
        int catCount = ctx.TblProducts
            .Where(x => int.Parse( JsonConvert.DeserializeObject<SellItem>( x.ItemJson ).category ) == cat.Id )
            .ToList()
            .Count; <-- errors here
        
        cats.Add(catCount);
    }

//

    public class SellItem
    {
        public string title { get; set; }
        public string description { get; set; }
        public string specification { get; set; }
        public string additional { get; set; }
        public string colour { get; set; }
        public string condition { get; set; }

        public string gender { get; set; }
        public string size { get; set; }
        public string category { get; set; }
        public string postage { get; set; }
        public string price { get; set; }
        public string quantity { get; set; }
        public bool active { get; set; }
        public List<string> images { get; set; }
        public List<string> imagenames { get; set; }

    }

But i get an exception:

The LINQ expression DbSet<TblProduct>().Where(t => int.Parse(JsonConvert.DeserializeObject<SellItem>(t.ItemJson).category) == __cat_Id_0) could not be translated.

Additional information: Translation of method Newtonsoft.Json.JsonConvert.DeserializeObject failed. If this method can be mapped to your custom function, see https://go.microsoft.com/fwlink/?linkid=2132413 for more information. 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.

How do i resolve this?

Your code is terse to the point of being confusing - you can make it much easier to read and follow by introducing intermediate variables instead of packing complex nested call-sites into argument expressions. — Dai
– Dai, Commented Nov 21, 2024 at 17:42
...and that complexity is actually the cause of the problem: EF can only translate a handful of expression-patterns to SQL - but certainly not JSON deserialization of external data like that... — Dai
– Dai, Commented Nov 21, 2024 at 17:44
List<int> cats = new List<int>(); is where I stopped reading. A list of numbers is not cats. Maybe catIds. If you can't name things correctly, it's likely that you haven't understood them. — Thomas Weller
– Thomas Weller, Commented Nov 21, 2024 at 18:08
@vc74: a cat is the category of an item to sell? Sounds absolutely plausible... — Thomas Weller
– Thomas Weller, Commented Nov 21, 2024 at 19:25
My advice to the OP is to stop, stop stop storing JSON blobs in an RDBMS and properly normalize it first before even attempting to use anything like Entity Framework - the only way to make this work right now is to either use only raw SQL with horrible JSON_QUERY etc functions (so no Linq) - or even worse: loading the entire table of JSON strings into memory so you can then deserialize it all. There's no redemption here: nuke it and start over. — Dai
– Dai, Commented Nov 21, 2024 at 21:50

Hans Egil Vaaga · Accepted Answer · 2024-11-22 11:54:38Z

0

As Neil says you can't deserialize data on the server side. And the solution is in your Additional information. You need to add .AsEnumerable to switch it to client-side evalutation.

You can try this:

List<int> cats = new List<int>();

foreach (var cat in ctx.TblCategories)
{
    int catCount = ctx.TblProducts
        .AsEnumerable() // Switch to client-side evaluation
        .Where(x => int.Parse( JsonConvert.DeserializeObject<SellItem>( x.ItemJson ).category ) == cat.Id )
        .Count;            

    cats.Add(catCount);
}

Hope it will help.

answered Nov 22, 2024 at 11:54

Hans Egil Vaaga

751 silver badge7 bronze badges

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

1 Comment

Neil Over a year ago

This will work, but bear in mind EVERY record in TblProducts will be pulled from the server to the client to do this. If you only have a few records (or this not called very often), it's a reasonable, if inefficient way to do it.

Collectives™ on Stack Overflow

Linq - How to query json inside Linq

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related