Entity Framework and stored procedure returning temp table Issues

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.

Answer 1

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.

Answer 2

This is a total hack but I have used it in the past to use the entity framework with complex stored procedures. It takes advantage of the fact that the entity framework uses NULL parameter values in creating a type to mirror the returned dataset. Do a simple IF (parameter is NULL) and a fake return dataset that matches what you will 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