Search code examples
sql-serverentity-frameworktemp-tables

Entity Framework and stored procedure returning temp table Issues

(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.

Solution

  • 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.