Issues with Entity Framework and stored procedures that return temp tables

entity-framework sql-server temp-tables

Question

(Disclaimer - I'm not the database designer. I'm just the poor developer what has to make this work.)

There are 17 (at the moment) tables with identical structure - name, address, phone number.

Given a phone number, I have to check to see if there's a matching entry in any of the tables, then return that address.

So, I created a view to get the list of tables (there's a ref table that holds that info), then I created a stored procedure to

  1. create a temp table,
  2. using cursors, check each table in the view for the phone number, using sql concatenation. If a record is found, insert it into the temp table.
  3. return the rows from the temp table.

This all works in straight T-SQL.

Now, I'm trying to use Entity Framework 4+ to call the stored procedure. But the function import interface won't generate columns. It says return type = none, and the LINQ code expects an int and won't compile.

Any ideas on how to make this work?

I know I can move the check tables part to code, if I absolutely have to, but I'd rather have the above method work.

1
3
10/19/2012 4:47:44 AM

Accepted Answer

I don't know the solution to the EF part, but in the database I'd just create the following view:

select * from Table1
union all select * from Table2
union all select * from Table3
union all select * from Table4
...

Then you can use EF to query the view however you like. No need for cursors and such.

0
3/18/2012 5:17:18 PM

Popular Answer

EF default way to get information about stored procedures asks only for "metadata" - it doesn't execute queries or data modification commands. Because of that EF is not able to receive information about stored procedures using temp tables, dynaimc SQL, etc. because these information are unknown until commands are really executed.

As a workaround you can modify your procedure and at the beginning put

SET FMTONLY OFF

Use this only when you try to import stored procedure to your database and make sure that stored procedure doesn't make any modifications in the database because these modifications will be executed each time you try to import or update stored procedure in your entity model.



Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow