CODEWITHSUNDEEP
c#.netjsonsql-serverentity-framework
Kannan KG
Kannan KG

Reputation: 11

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

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..

Upvotes: 0

Views: 1498

Answers (2)

Sami
Sami

Reputation: 36

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

Upvotes: 0

Nabi Sobhi
Nabi Sobhi

Reputation: 369

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

Upvotes: 1

Related Questions