C# query JSON column in a SQL Server table to get the matching rows

Question

Json string is updated to one of the columns of SQL Server, I want the rows that matches my condition and store it to a collection. In this case, I am applying the condition to the json key/ name in order to fetch the data. I achieved it in entity framework with the help of sqlquery function. But I trying to find other options that we have for same circumstance maybe linq or lambda.

Example it is a movie table that has 3 columns among which Movie_info column is a NVarChar that contains Json string.

 id: 1
 Storyline: "Blah Blah!!"
 Movie_info [{"title":"The Shawshank Redemption","year":1994,"cast":["Morgan 
 Freeman", "Tim Robbins"]}]`

 id: 2
 Storyline: "free from corruption."
 Movie_info [{"title":"Batman Begins","year":2005,"cast":[],"Director": 
 "Christopher Nolan"}]
 .
 ..
 ...

I am trying to filter the movies that were released in the year 2005. Below is the code I tried and it worked successfully.

using (MoviesDbContext ctx = new MoviesDbContext())
{
    var movieList = ctx.Movies.SqlQuery("SELECT * FROM [Movie].[dbo].[Movies] 
                                         WHERE JSON_VALUE(Movie_info, '$.year') = @value", 
                                        new SqlParameter("@value", 2005)).ToList();
}

With this code I got list of Movies that were released in the year 2005. How can I achieve the same using Lambda or using Linq queries because I don't want to hardcode SQL statements to the controller..

I am expecting the same result. suggestion on any other approach is much appreciated..

Nabi Sobhi · Accepted Answer · 2019-07-30 04:55:56Z

1

from visualstudiomagazine.com There is, as yet, no support in Entity Framework or LINQ to leverage the TSQL JSON functionality.

answered Jul 30, 2019 at 4:55

Nabi Sobhi

3694 silver badges19 bronze badges

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

4 Comments

Kannan KG Over a year ago

Thank you Nabi, do you have suggestion how to avoid hardcoding the sql queries to the code?

Nabi Sobhi Over a year ago

You can take advantage of nameof() operator or reflection, but I personally prefer move raw queries to resource files, so that I can manipulate them easily. Having the queries in a separate library could be of great help too.

Kannan KG Over a year ago

After some research I come to know that stored procedure is one way out of this situation as Reflections in C# are not that efficient. What is your opinion about this?

Nabi Sobhi Over a year ago

It depends on your situation. For example, are the store procedures under source controller? This link may be useful. stackoverflow.com/questions/15142/…

Sami · Accepted Answer · 2019-07-30 09:43:47Z

0

You could use Reflectionsto get the Propertyname of the requested JSON item to put it in the Query, but be aware that reflections are slow. for further information see https://www.tutorialspoint.com/csharp/csharp_reflection.htm

answered Jul 30, 2019 at 9:43

Sami

363 bronze badges

Collectives™ on Stack Overflow

C# query JSON column in a SQL Server table to get the matching rows

2 Answers 2

4 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related