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

elizondo82

New member
Joined
Sep 16, 2022
Messages
4
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!!
 

Skydiver

Staff member
Joined
Apr 6, 2019
Messages
5,698
Location
Chesapeake, VA
Programming Experience
10+
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?
 

elizondo82

New member
Joined
Sep 16, 2022
Messages
4
Programming Experience
10+
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!!
 

Skydiver

Staff member
Joined
Apr 6, 2019
Messages
5,698
Location
Chesapeake, VA
Programming Experience
10+
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.
 

Skydiver

Staff member
Joined
Apr 6, 2019
Messages
5,698
Location
Chesapeake, VA
Programming Experience
10+
If you are willing to write SQL instead of LINQ, and your database is MSSQL 2016 or higher:

 

Skydiver

Staff member
Joined
Apr 6, 2019
Messages
5,698
Location
Chesapeake, VA
Programming Experience
10+
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.
 
Top Bottom