Entity Framework and Stored Procedure Returning Temporary Table
(Disclaimer - I'm not a database developer. I'm just a poor developer to get the job done.)
There are 17 (at the moment) tables with the same structure - name, address, phone number.
Given the phone number, I have to check if there is a matching entry in any of the tables, and then return that address.
So, I created a view to get a list of tables (there is a link table that contains this information), then I created a stored procedure for
- create a temporary table,
- using cursors check each table in the view for a phone number using sql concatenation. If a record is found, insert it into the temp table.
- returns rows from temp table.
It all works in straightforward T-SQL.
I am now trying to use Entity Framework 4+ to call a stored procedure. But the function import interface won't generate columns. It says type return = none and LINQ code expects int and won't compile.
Any ideas on how to make this work?
I know I can move some of the checklists into the code if I absolutely need to, but I would prefer this method to work.
source to share
I don't know the solution to the EF part, but in the database, I 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 request the view however you like. No need for cursors, etc.
source to share
EF by default only queries "metadata" for information about stored procedures — it does not execute queries or commands to modify data. Because of this, EF cannot get information about stored procedures using temporary tables, dynaimc SQL, etc., because this information is unknown until the commands are executed.
As a workaround, you can change your procedure and put
SET FMTONLY OFF
Use this only when trying to import a stored procedure into your database, and make sure that the stored procedure does not make any changes to the database as these changes will be made every time you try to import or update a stored procedure in your organization's model.
source to share
Adding this illogical block of code solved the problem. Even if it never hits
IF 1=0 BEGIN
SET FMTONLY OFF
END
source to share
This is a common hack, but I've used it in the past to leverage entity framework with complex stored procedures. It takes advantage of the fact that the entity framework uses NULL parameter values when creating the type to mirror the returned dataset. Make a simple IF (NULL parameter) and a fake return dataset that matches what you return.
ALTER proc [dbo].[cust_auto_doc_list_invoice]
@interval_ref integer
AS
SET NOCOUNT ON;
IF @interval_ref IS NULL
BEGIN
-- This is to fool the edmx generator.
SELECT CONVERT(integer,0) as group_ref,
CONVERT(varchar(50),'') as group_name,
CONVERT(integer,0) as wloc_ref,
CONVERT(decimal(18,0),0) as invoice_ref,
CONVERT(decimal(10,0),0) as cust_ref,
CONVERT(varchar(50),'') as cust_name,
CONVERT(decimal(10,0),0) as csnee_ref,
CONVERT(varchar(50),'') as csnee_name
END
ELSE
BEGIN
-- Do real work here
END
-Tom
source to share