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)

not a data/content problem. the physical column schema does not match the model. — Cee McSharpface, Commented Nov 16, 2018 at 9:33
Please check your SELECT query inside Get_JSON_MyItems and provide MyItem class contents, probably the Id column is not included there. — Tetsuya Yamamoto, Commented Nov 16, 2018 at 9:33
Wait... string.Concat returns new string with concatenated values, you cannot use ToList() against a string (it must be a collection). You should use List<MyItem> itemsJson = TabWebContext.Database.SqlQuery<MyItem>("Get_JSON_MyItems").ToList(); instead. — Tetsuya Yamamoto, Commented Nov 16, 2018 at 9:56
Do you mean that the MyItemModel.cs attributes has to be absolute consistent with the database columns @dlatikay ? But I need additional attributes in the Model where no database column exists for (in order to initialize them in the Controller in some case). But when executing the query in my question, that attributes could be null. — thestruggleisreal, Commented Nov 16, 2018 at 10:33
The both MyItem and MyItemModel classes are have same property? — Ranjith.V, Commented Nov 16, 2018 at 10:50

mjwills · Accepted Answer · 2018-11-16 11:08:59Z

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:

Remove the use of FOR JSON.
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.

Collectives™ on Stack Overflow

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

1 Answer 1

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Linked

Related