CODEWITHSUNDEEP
c#asp.netentity-frameworkstored-procedures
Wajeeh
Wajeeh

Reputation: 111

Dynamic SQL based stored procedure call in Entity Framework

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.

Upvotes: 3

Views: 7721

Answers (2)

Sanket Shah
Sanket Shah

Reputation: 9

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.

Upvotes: 0

Ladislav Mrnka
Ladislav Mrnka

Reputation: 364259

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.

Upvotes: 5

Related Questions