Exception when trying to execute a stored procedure in ASP.NET MVC with a Sql-Query

Question

I'm working with Entity Framework but have a hierarchical datasource. It takes too long time to make db-queries and convert to json in the controller.

For that reason, I have a stored procedure.

Executing the stored procedure directly in the Microsoft SQL Server Management Studio, I get the expected output.

But trying to do the following in my Controller throws the following exception.

  `IEnumerable<MyItem> itemsJson=Context.Database.SqlQuery<MyItem>("Get_JSON_MyItems").ToList();` 

System.Data.Entity.Core.EntityCommandExecutionException: 'The data reader is incompatible with the specified 'MyDataModel.MyItem'. A member of the type, 'Id', does not have a corresponding column in the data reader with the same name.'

I guess the data reader splits the string into many fields because it is too long? But I'm not sure and don't know how I'd fix if so.

--

(Also tried to write the command in the Controller instead executing the stored procedure)

Answer 1

FOR JSON results in the query returning a single row and single column, with a non-meaningful column name (essentially a single cell containing the full JSON payload). As an example - it doesn't have a Id column in it - so EF doesn't know how to populate the model.

I'd suggest you need to:

1. Remove the use of FOR JSON.
2. Ensure that the columns returned in the query are exactly the same as the properties in your class. Only try this if (1) doesn't work.

Alternatively, if you really want to keep FOR JSON, then you need to read the single JSON element and then use JSON.NET (or similar) to map it to your List<MyItem>.

One way you could do this, as an example, is to change your stored proc to have an nvarchar(MAX) OUTPUT parameter - which you set to the result of your query. Your calling code can then read that output parameter.

Another way would be to bypass Entity Framework altogether and use ADO.NET directly. Call the stored proc and then use SqlDataReader.GetString(0) to get the raw JSON.