Parameterized OPENJSON in SQL Server query using Entity Framework Core

Question

context.Set<BlogKeyValuePair>()
   .FromSql("SELECT [key], value FROM OPENJSON((SELECT JsonData FROM dbo.Blogs WHERE BlogId=1), '$.@path')", 
        new SqlParameter("@path", "path.to.data"));

On first sighting this should work correctly and @path should be replace by path.to.data but it doesn't, an SqlException is thrown with the following error:

System.Data.SqlClient.SqlException: Incorrect syntax near '@path'.

Seems like SQL server does not replace the parameter because it is a parameter inside the OPENJSON function.

Looking for secure workarounds.

Share the full stack trace!

marsze
– marsze

2018-09-05 10:18:51 +00:00
Commented Sep 5, 2018 at 10:18 — marsze
– marsze, Commented Sep 5, 2018 at 10:18

marsze · Accepted Answer · 2018-09-04 10:46:27Z

3

SQL does not recognize the variable because you put it inside a string:

-- Wrong:
OPENJSON((SELECT JsonData FROM dbo.Blogs WHERE BlogId = 1), '$.@path')

-- Correct:
OPENJSON((SELECT JsonData FROM dbo.Blogs WHERE BlogId = 1), '$.' + @path)

Code:

context
    .Set<BlogKeyValuePair>()
    .FromSql(@"
        SELECT [key], value
        FROM OPENJSON((SELECT JsonData FROM dbo.Blogs WHERE BlogId = 1), '$.' + @path)",
        new SqlParameter("@path", "path.to.data"));

edited Sep 4, 2018 at 10:46

answered Sep 4, 2018 at 10:34

marsze

17.5k6 gold badges59 silver badges67 bronze badges

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

5 Comments

J. Doe Over a year ago

This does not work for me. Have you checked it yourself? Perhaps I am missing something. I am getting "Incorrect syntax near '+'."

J. Doe Over a year ago

Try concatenate two strings into OPENJSON's path parameter inside SSMS or any other tool you use to manage your DB.

marsze Over a year ago

@J.Doe This should work fine and David Browne's SQL worked for me too. Not a clue why it gives you syntax errors. Have you tried it inside SMSS? Seems like not the code but something else is broken. Have you tried it using a plain string for the path without a parameter? Have you tried putting the full path in the parameter, incl. the $.?

J. Doe Over a year ago

Yes I have tried inside SMSS. Yes I have tried using a plain string for the path without a parameter and it does work. And yes for the $. inside my parameter

marsze Over a year ago

@J.Doe If tried David Browne's SQL using the .NET functionality under System.Data.SqlClient and it worked fine too, even using the parameter. I wonder if EF Core performs some internal syntax checks? I don't believe that though.

David Browne - Microsoft · Accepted Answer · 2018-09-04 22:09:13Z

0

This works for me in SSMS, so @marsze's answer should work.

declare @path nvarchar(2000) = 'ArrayValue';

DECLARE @json NVARCHAR(4000) = N'{  
   "StringValue":"John",  
   "IntValue":45,  
   "TrueValue":true,  
   "FalseValue":false,  
   "NullValue":null,  
   "ArrayValue":["a","r","r","a","y"],  
   "ObjectValue":{"obj":"ect"}  
}'

SELECT *
FROM OPENJSON(@json, '$.' + @path)

answered Sep 4, 2018 at 22:09

David Browne - Microsoft

90.7k7 gold badges52 silver badges87 bronze badges

2 Comments

J. Doe Over a year ago

I'm copying exactly that and I'm still getting "Incorrect syntax near '+'." Any idea why?

marsze Over a year ago

@J.Doe If you changed your code, then add your new code in your question!

Collectives™ on Stack Overflow

Parameterized OPENJSON in SQL Server query using Entity Framework Core

2 Answers 2

5 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related