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