Devart Forums

MaxFerretti

Hello to all,
I have a Json column on a MySql database and I'm using dotConnect with EntityFramework 6.4.4 in a .NET application (4.6.2).
Inside the json column I have some products like this {name: string, weight: decimal}
I can easily filter on strings using
MySqlJsonFunctions.Extract(e.custom_props, "$.name") == "foo"
but I can't find a way to filter and aggregate on integer values.
For exampe I want to have something like that:

Code: Select all

var query = db.test_events
                    .Where(e =>
                    	e.Year = 2021 &&
                        MySqlJsonFunctions.Extract(e.custom_props, "$.name") == "foo" && 
                        MySqlJsonFunctions.Extract(e.custom_props, "$.weight") > 0 &&                         
                    .GroupBy(e => new { e.Year })
                    .Select(e =>
                        new
                        {
                            Year = e.Key.Year,
                            TotalWeight = e.Sum(p => MySqlJsonFunctions.ExtractUnquotedText(p.custom_props, "$.weight"))
                        }
                    ).ToArray();

But obviously I can't because MySqlJsonFunctions only have methods that returns strings.
Adding Convert.ToInt32 also cannot work since the Sql generator cannot understand it.

Is there a way?
O am I supposed to threat it as strings, convert in memory and filter on the materialized objects?
Wouldn't it degrade performance very much?

Thank you!

Devart Forums

Search found 1 match

Filter and aggregation on JSON columns