Linq to SQL, Stored Procedure with different return types based on an If/Else

Question

I have an existing Stored Procedure which I am trying to now call with LINQ to SQL, here is the stored procedure:

ALTER procedure [dbo].[sp_SELECT_Security_ALL] (
@UID       Varchar(15)
)
as
DECLARE @A_ID int

If ISNULL(@UID,'') = ''
    SELECT  DISTINCT
       App_ID,
       App_Name,
       App_Description,
       DB,
       DBNameApp_ID,
       For_One_EVA_List_Ind
    From      v_Security_ALL
ELSE
   BEGIN
        Select @A_ID = (Select Assignee_ID From NEO.dbo.v_Assignees Where USER_ID = @UID and Inactive_Ind = 0)

    SELECT  DISTINCT
       Security_User_ID,
       Security_Company,
       Security_MailCode,
       Security_Last_Name,
       Security_First_Name,
       Security_User_Name,
       Security_User_Info,
       Security_User_CO_MC,
       Security_Email_Addr, 
       Security_Phone,
       Security_Security_Level, 
       Security_Security_Desc, 
       Security_Security_Comment,
       Security_Security_Inactive_Ind,
       App_ID,
       App_Name,
       App_Description,
       DB,
       DBNameApp_ID,
       For_One_EVA_List_Ind,
       @A_ID as Assignee_ID
    From      v_Security_ALL
    Where     Security_User_ID   = @UID
   END

My problem is that the intellsense only sees the first set of return values in the IF statement and I can not access anything from the "else" part of my stored procedure. so when I try to do this:

 var apps = dataContext.sp_SELECT_Security_ALL(userId);

        foreach (var app in apps)
        {
            string i = app.
        }

On the app. part the only available values I have there is the results of the the first Select distinct above.

Is it possible to use LINQ with this type of stored procedure?

Answer 1

The problem isn't with Intellisense. dataContext.sp_SELECT_Security_ALL() is returning a fixed data type. You may be hiding that behind a "var", but it's nevertheless a concrete type with a fixed number of properties. There is still C# remember, and a function can only return one type of object. Look in your dataContext.designer.cs file to see how it's actually defined.

Answer 2

Scott Guthrie has covered this case in a blog post. Scroll down to "Handling Multiple Result Shapes from SPROCs."

Answer 3

The quick and dirty way to fix this is to coerce every returning statement to return the same thing:

IF @theSkyIsBlue
SELECT CustomerNumber, CustomerName, null as OrderNumber, null as OrderName
FROM Customers
ELSE
SELECT null as CustomerNumber, null as CustomerName, OrderNumber, OrderName
FROM Orders

You may have to watch/(manually change) the nullability of properties in the mapped type, but this will get you where you're going.