How can find a param by json string from Linq and context with sql

elizondo82

Member
Joined
Sep 16, 2022
Messages
12
Programming Experience
10+
Hello, I try find a param by json string from Linq.

Example:
I have a db sql there show me this columns
Id | name | price | code | countryCode

The column countryCode show a Json String example , [{siglas: "NY", Name:"New York"},{siglas: "NX", Name:"New Mexico"},...]
I want find the siglas == "NX"
= from o in Datacolumns where x=>x.code == 5 && x.countryCode["sliglas"] == "NX"

any idea

Thanks!!
 
Why do you have stringly-typed data in your relational database? Wouldn't the first step be to normalize the database data so that you have all relational data?
 
Hello, I add a json a tablet, because I had create a form where the show a list options, there I select the options, later save the info.

but now i want find the register.

Thanks!!
 
So the amount of work you saved while writing into that column ONE time must now be paid for by parsing that column EVERY time there is a database query.

Unless SQL server (or whatever database engine you are currently using) has native support for parsing JSON within a column, you will have to retrieve every candidate row, parse the JSON and see if it satisfied the condition that you need.
 
If you are willing to write SQL instead of LINQ, and your database is MSSQL 2016 or higher:

 
Yes, but with that function, the candidates values need to be brought back from the SQL to your PC (or the web server) for the function to run. If there are one million x.code == 5, but only three x.countryCode["sliglas"] == "NX", all one million needs to be sent over the network to your PC for your PC to filter down to the just those three items. Imagine the time it will take to send those items and to parse each one. The point of using a database is to leverage it's power to perform fast searches. If you could get the database to just send you back the three items instead of the one million items.
 
Hello, I try find a param by json string from Linq.

Example:
I have a db sql there show me this columns
Id | name | price | code | countryCode

The column countryCode show a Json String example , [{siglas: "NY", Name:"New York"},{siglas: "NX", Name:"New Mexico"},...]
I want find the siglas == "NX"
= from o in Datacolumns where x=>x.code == 5 && x.countryCode["sliglas"] == "NX"

any idea

Thanks!!
C#:
var json = "Your JSON string";

dynamic stuff = JsonConvert.DeserializeObject(json);

string name = stuff.status;
var arr = stuff.articles;

foreach (var a in arr)
{
   var authorName = a.author;
}
Given problam have fixed by this solution.
 
Last edited by a moderator:
Unfortunately, your solution doesn't solve the key part of the problem: the JSON string lives in the countryCode column of each row of the OP's table. Your solution only handles a single row on the database client side assuming that the row data has been retrieved from the database. The problem is how to let the datase server do the work so that all the rows don't have to be sent to the database client side.
 
Back
Top Bottom