0

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

Kannan KG
  • 11
  • 7

2 Answers2

1

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

Nabi Sobhi
  • 369
  • 4
  • 16
  • Thank you Nabi, do you have suggestion how to avoid hardcoding the sql queries to the code? – Kannan KG Jul 30 '19 at 09:35
  • 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. – Nabi Sobhi Jul 30 '19 at 10:41
  • 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? – Kannan KG Jul 30 '19 at 13:02
  • It depends on your situation. For example, are the store procedures under source controller? This link may be useful. https://stackoverflow.com/questions/15142/what-are-the-pros-and-cons-to-keeping-sql-in-stored-procs-versus-code – Nabi Sobhi Jul 31 '19 at 07:12
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

Sami
  • 36
  • 3