c# - Linq - How to query json inside Linq - Stack Overflow

admin管理员组

文章数量:1122832

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 /?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 /?linkid=2101038 for more information.

How do i resolve this?

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?

• c#
• entity-framework
• linq

Share Improve this question Follow edited Nov 21, 2024 at 18:01 Dai 155k3030 gold badges295295 silver badges419419 bronze badges asked Nov 21, 2024 at 17:35 redoc01redoc01 2,30355 gold badges3535 silver badges6868 bronze badges 7

• 1 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 Commented Nov 21, 2024 at 17:42
• 1 ...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 Commented Nov 21, 2024 at 17:44
• 1 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 Commented Nov 21, 2024 at 18:08
• 1 @vc74: a cat is the category of an item to sell? Sounds absolutely plausible... – Thomas Weller Commented Nov 21, 2024 at 19:25
• 1 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 Commented Nov 21, 2024 at 21:50

 |  Show 2 more comments

1 Answer 1

Sorted by: Reset to default 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.

本文标签： cLinqHow to query json inside LinqStack Overflow