Linq to SQL，基于If / Else的不同返回类型的存储过程

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

我有一个现有的存储过程,我现在尝试使用LINQ to SQL调用,这是存储过程:

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:

我的问题是intellsense只能看到IF语句中的第一组返回值,而且我无法从存储过程的“else”部分访问任何内容。所以当我尝试这样做时:

 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.

在应用程序上。部分我唯一可用的值是上面第一个Select distinct的结果。

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

是否可以在这种类型的存储过程中使用LINQ?

3 个解决方案

#1

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

Scott Guthrie在一篇博文中介绍了这个案例。向下滚动到“处理来自SPROC的多个结果形状”。

#2

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.

问题不在于Intellisense。 dataContext.sp_SELECT_Security_ALL()返回固定数据类型。您可能隐藏在“var”后面,但它仍然是具有固定数量属性的具体类型。还有C#记住,一个函数只能返回一种类型的对象。查看dataContext.designer.cs文件以查看它是如何实际定义的。

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

您可能必须观察/(手动更改)映射类型中属性的可空性,但这将使您获得所需的位置。

#1