Store Jsonstring using Entity Framework Core ExecuteSqlRawAsync

Question

I have an entity in which for one of the string field I want to store jsonstring (json equivalent of List of MyObject) using EF Core ExecuteSqlRawAsync extension.

However, I am getting this error :

Input string was not in a correct format.

from EF at

Microsoft.EntityFrameworkCore.Storage.Internal.RawSqlCommandBuilder.Build.

I am using EF Core version 3.1.

Please find below an overview of sample similar to what I am trying:

string query = "update mytable set column1 = 100, column2= '[{\"property1\":\"value1\",\"property2\":null}]' where condition;update mytable set column1= 200, column2= '[{\"property1\":\"value2\",\"property\":null}]' where condition;"

await this.Context.Database.ExecuteSqlRawAsync(query);

If I run the same raw SQL statement directly against the SQL Server database, it works just fine.

Is there some limitation in the RawSql extension that string field in an entity cannot have jsonstring?

Any help how to run raw query with one of the string entity field accommodating jsonstring?

Answer 1

The problem is caused by the '{' and '}' symbols inside SQL string, because in ExecuteSqlRaw{Async} they are used to specify parameter placeholders, so the string should be a valid input for string.Format function. In fact EF Core implementation uses string.Format (even though you don't pass parameters) at some point, which in turn generates the exception in question. Which can be seen if you do

string.Format(query);

The solution is to make it valid format string by doubling '{' and '}' symbols inside. For instance, in your sample:

string query = "update mytable set column1 = 100, column2= '[{{\"property1\":\"value1\",\"property2\":null}}]' where condition;update mytable set column1= 200, column2= '[{{\"property1\":\"value2\",\"property\":null}}]' where condition;"

Also it would be good if you try actually parameterizing your SQL.

Answer 2

The best way to solve this problem is to use SqlParameter

object[] sqlParams = new object[] {new SqlParameter("@column2"," [{"property1":"value1","property2":null}]") };

string query = "update mytable set column1 = 100, column2= @column2

await this.Context.Database.ExecuteSqlRawAsync(query,sqlParams);