3

For one of our recent projects, we created a stored procedure which generated SQL and executed it in the end. The purpose of the stored procedure was to create pivots based on dynamic columns.

When trying to access it using Entity Framework using the usual function import when I tried to access the stored procedure, it would return anything as it requires a dynamic type to store the retrieved data.

Which in our case was a dynamic query and linq was unable to get the returned columns. So to work around what I did was call the stored procedure in the traditional way i.e. creating a DataAdapter and SqlCommand object and SqlConnection object.

But what is the proper way of calling this kind of stored procedure using Entity Framework?

Thanks in advance.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Wajeeh
  • 111
  • 1
  • 7
  • and i need to add here that i cant create a complex type by myself as the columns being returned can increase or decrease in future. – Wajeeh Dec 29 '11 at 12:38

2 Answers2

5

Entity framework doesn't support dynamic result sets from stored procedures. It also doesn't support stored procedures using dynamic SQL because it cannot get static result set declaration from the procedure. So you must either ensure that your procedure will always return static type (same number of columns with same names) or you must use traditional ADO.NET to execute that procedure.

Ladislav Mrnka
  • 360,892
  • 59
  • 660
  • 670
  • k thanks will remeber this next time i get into something like this :) – Wajeeh Dec 30 '11 at 05:01
  • @Ladislav Mrnka Is this possible now with the dynamic types in EF 4 ? can I use a `DbContext.Database.SqlQuery()` with a dynamic type to retrieve a dynamic object with dynamic result-set ? the thing is I'm trying to achieve something else here: [dynamic-user-extensible-entities-using-entity-framework] http://stackoverflow.com/questions/15019705/dynamic-user-extensible-entities-using-entity-framework – Mortalus Feb 22 '13 at 09:12
  • @Mortalus: The answer is no. EF doesn't work with dynamic types. Moreover it is not good technology for user extensible entities (in terms of adding columns at runtime). – Ladislav Mrnka Feb 22 '13 at 09:36
  • @Ladislav Mrnka: Thanks ... so what would be the approach to take when one wants to implement something like a user extensible object in a system like described in the question at my comment above ? – Mortalus Feb 22 '13 at 09:42
0

Following steps can be followed:

  1. Store the dynamic part of SP inside a variable and the print that variable at end of the SP.

  2. execute the SP and execute it with some data.

  3. open the Messages tab in Result window.

  4. copy the code that is written after (x row(s) affected);

  5. paste that code in the SP and comment out everything else until variables declaration.

  6. execute the new modified SP and add it to the entity framework. This time, entity framework will make a complex type which you want.

  7. uncomment the previous commented code and delete the data that you copied from Messages tab and execute it again.

Follow the same process every time you add or remove columns from the SP.

Sanket Shah
  • 9
  • 3