4

I am using entity framework with a stored procedure, in which I am generating query dynamically and executing that query. The stored proc query looks like:

Begin
  DECLARE @Query nvarchar(MAX)
  SET @Query = 'SELECT e.id, e.name, e.add, e.phno from employee'
  EXEC sp_executesql @Query
End

In above sql code you can see that i am executing '@Query' variable, and that variable value can be changed dynamically.

I am able to add my stored proc in my edmx file. and then I go to model browser and say Add function import and try to Get column information it does not show anything. but when I execute my stored proc at server it returns all columns with values. Why i am not getting column information at model browser?

Erik Philips
  • 53,428
  • 11
  • 128
  • 150
sawan
  • 2,341
  • 3
  • 25
  • 51
  • Possible duplicate of [Why can't Entity Framework see my Stored Procedure's column information?](https://stackoverflow.com/questions/5996887/why-cant-entity-framework-see-my-stored-procedures-column-information) – Matt Jul 17 '17 at 14:59

3 Answers3

1

The model browser isn't running the stored procedure to then gather the column information from its result - it's trying to grab the column information from the underlying procedure definition using the sys tables.

This procedure, because it's dynamic, will not have an underlying definition and therefore won't be importable into the EDMX like this.

Mike Perrenoud
  • 66,820
  • 29
  • 157
  • 232
1

Temporarily change your stored proc to

SELECT TOP 1 e.id, e.name, e.add, e.phno from employee /* ... rest of your proc commented out */

Then add it to the EF and create the function import (it will see the columns).

Then change the proc back to how you had it above.

SteveCav
  • 6,649
  • 1
  • 50
  • 52
0

Try adding SET NOCOUNT ON after BEGIN.... that supresses messages that might cause it to be "confused"

Lasse Edsvik
  • 9,070
  • 16
  • 73
  • 109