-2

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)

thestruggleisreal
  • 940
  • 3
  • 10
  • 26
  • 2
    not a data/content problem. the physical column schema does not match the model. – Cee McSharpface Nov 16 '18 at 09:33
  • 2
    Please check your `SELECT` query inside `Get_JSON_MyItems` and provide `MyItem` class contents, probably the `Id` column is not included there. – Tetsuya Yamamoto Nov 16 '18 at 09: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 itemsJson = TabWebContext.Database.SqlQuery("Get_JSON_MyItems").ToList();` instead. – Tetsuya Yamamoto Nov 16 '18 at 09: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 Nov 16 '18 at 10:33
  • The both MyItem and MyItemModel classes are have same property? – Ranjith.V Nov 16 '18 at 10:50
  • I am sorry, I need to know how to get the json from that column generated by the query. – thestruggleisreal Nov 16 '18 at 11:07
  • `but have a hierarchical datasource.` Just to be clear, you aren't expecting `AS JSON PATH` will create the hierarchy for you? You know you'll still need to do that manually? – mjwills Nov 16 '18 at 11:26

1 Answers1

0

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.

mjwills
  • 23,389
  • 6
  • 40
  • 63