SqlMetal错误产生我的存储过程的返回类型（LINQ） [英] SqlMetal wrongly generates the return type of my stored proc (LINQ)

问题描述

嗨有一个存储过程，它总是返回根据参数单排：

Hi have a stored proc that always returns a single row depending of a parameter:

IF @bleh = 1
  SELECT TOP 1 Xyz FROM Abc
ELSE
  SELECT TOP 1 Def FROM Abc

我必须使用SqlMetal生成的DataContext但此存储过程返回 IMultipleResults ，这是一个错误。相反，它应该返回一个 ISingleResult ...

I must use SqlMetal to generate the DataContext but this stored procedure returns a IMultipleResults, which is an error. Instead it should return a ISingleResult...

如果我删除的，如果（把一个单一的 SELECT 调用），会生成一个 ISingleResult 返回类型。

If I remove the if (putting a single SELECT call), an ISingleResult return type is generated.

任何想法？

推荐答案

你所描述的情况是由设计。我测试既.NET 3.5和.NET 4.0 Beta 2中，得到了相同的结果。鉴于使用和你做一个IF / ELSE结构的存储过程，使用的生成的结果和工具：

The scenario you're describing is by design. I've tested with both .NET 3.5 and .NET 4.0 Beta 2 and got the same results. Given a SPROC using an IF/ELSE structure as yours does, the generated results and tools used are:

• SqlMetal ： IMultipleResults 的
• LINQ to SQL设计（拖动和放大器;下降的VS IDE）的 ISingleResult 的

• SqlMetal: IMultipleResults
• LINQ To SQL Designer (drag & drop in the VS IDE): ISingleResult

这是<一个href="http://social.msdn.microsoft.com/Forums/en-US/linqprojectgeneral/thread/ce90169d-70e7-41e7-9828-aca0eceab769/">supported由马特·沃伦的微软：

设计者无法识别存储   特效多返回值和   将它们映射到返回   单一的整数。

The designer does not recognize stored procs with multiple return values and will map them all to returning a single integer.

SQLMetal命令行工具的功能   识别多种结果，   将输入方法的返回   正确的IMultipleResults。您   既可以使用SQLMetal或修改   DBML用手或添加方法   签名这个存储过程到您的   对于自己的部分类的   DataContext的。

SQLMetal command line tool does recognize the multiple results and will type the return of the method correctly as IMultipleResults. You can either use SQLMetal or modify the DBML by hand or add the method signature for this stored proc to your own partial class for your DataContext.

在这篇博客在另一场景设计中不添加IMultipleResults并使用ISingleResult而是迪内希库尔卡尼评论。他指出（强调）：

In this blog post Dinesh Kulkarni comments on the opposite scenario where the designer doesn't add IMultipleResults and uses ISingleResult instead. He states (emphasis added):

不，设计师不支持   此功能。所以，你必须添加   方法在部分类。 SqlMetal   然而，是否提取的存储过程。 的   原因是一个实现   细节：两个使用相同的code   发电机，但不同的数据库   模式提取。

And no, the designer does not support this feature. So you have to add the method in your partial class. SqlMetal does however extract the sproc. The reason for that is an implementation detail: the two use the same code generator but different database schema extractors.

此外，标题为来自的存储过程处理多个结果形状在<一个href="http://weblogs.asp.net/scottgu/archive/2007/08/16/linq-to-sql-part-6-retrieving-data-using-stored-procedures.aspx">Scott顾的帖子和这个MSDN文章都显示IMultipleResults正在与使用的存储过程中使用相同的结构。

In addition, the section titled "Handling Multiple Result Shapes from SPROCs" in Scott Gu's post and this MSDN article both show IMultipleResults being used with SPROCs that use the same structure.

大，现在该怎么办？有几个解决方法，有些人比其他人更好。

Great, now what? There are a few workarounds, some are nicer than others.

您可以重写存储过程，使SqlMetal生成使用ISingleResult功能。这可以通过以下方式实现

You can rewrite the SPROC so that SqlMetal generates the function using ISingleResult. This can be achieved by

重写＃1 - 结果存储到一个变量：

DECLARE @Result INT
IF @Input = 1
    SET @Result = (SELECT TOP 1 OrderId FROM OrderDetails)
ELSE
    SET @Result = (SELECT TOP 1 ProductId FROM OrderDetails ORDER BY ProductId DESC)

SELECT @Result As Result

显然，类型将需要类似或东西，可以转换为其他的。例如，如果一个人是一个 INT 键，另一个是 DECIMAL（8,2）你会使用十进制保留precision。

Obviously the types will need to be similar or something that can be cast to the other. For example, if one was an INT and the other was a DECIMAL(8, 2) you would use the decimal to retain precision.

重写＃2 - 使用一个case语句：

这是相同的<一个href="http://stackoverflow.com/questions/2037280/sqlmetal-wrongly-generates-the-return-type-of-my-stored-proc-linq/2150955#2150955">Mark's建议。

SELECT TOP 1 CASE WHEN @Input = 1 THEN OrderId ELSE ProductId END FROM OrderDetails

---

使用，而不是一个存储过程一个UDF

您可以使用标量值UDF 并调整您的查询使用UDF格式（以上相同的变量的方法提）。 SqlMetal将产生一个ISingleResult对于它，因为只有一个值被返回。

---

Use a UDF instead of a SPROC

You could use a scalar-valued UDF and adjust your query to use the UDF format (identical to the variable approach mentioned above). SqlMetal will generate an ISingleResult for it since only one value is returned.

CREATE FUNCTION [dbo].[fnODIds] 
(
    @Input INT
)
RETURNS INT
AS
BEGIN
    DECLARE @Result INT

    IF @Input = 1
        SET @Result = (SELECT TOP 1 UnitPrice FROM OrderDetails)
    ELSE
        SET @Result = (SELECT TOP 1 Quantity FROM OrderDetails ORDER BY Quantity DESC)

    RETURN @Result

END

---

假的存储过程和切换它

这工作，但低于previous选项比较繁琐。此外，要反复将来使用SqlMetal的将覆盖这些变化和所需要的过程。使用部分类和移动相对code也有助于prevent这一点。

---

Fake the SPROC & switch it out

This works but is more tedious than the previous options. Also, future use of SqlMetal would overwrite these changes and require the process to be repeated. Using a partial class and moving the relative code there would help prevent this.

1）的改变你的存储过程返回一个 SELECT 语句（注释掉您的实际code），如 SELECT TOP 1的OrderID FROM订单明细

1) Change your SPROC to return a single SELECT statement (comment out your actual code), such as SELECT TOP 1 OrderId FROM OrderDetails

2）的使用SqlMetal。它会生成一个ISingleResult：

2) Use SqlMetal. It will generate an ISingleResult:

[Function(Name = "dbo.FakeODIds")]
public ISingleResult<FakeODIdsResult> FakeODIds([Parameter(Name = "Input", DbType = "Int")] System.Nullable<int> input)
{
    IExecuteResult result = this.ExecuteMethodCall(this, ((MethodInfo)(MethodInfo.GetCurrentMethod())), input);
    return ((ISingleResult<FakeODIdsResult>)(result.ReturnValue));
}

3）的改变你的存储过程返回到其原来的形式，但使用相同的别名返回的结果。例如，我将返回的两个的 ORDERID 和的ProductId 为 FakeId 。

3) Change your SPROC back to its original form but use the same alias for the returned result. For example, I will return both OrderId and ProductId as FakeId.

IF @Input = 1
    SELECT TOP 1 OrderId As FakeId FROM OrderDetails
ELSE
    SELECT TOP 1 Quantity As FakeId FROM OrderDetails ORDER BY Quantity DESC

请注意，我没有使用一个变量，但在这里用你最初开始直接与格式。

Notice I am not using a variable here but using the format you originally started with directly.

4）的由于我们使用，我们需要调整产生的code中的FakeId别名。如果您导航到您在步骤2（ FakeODIdsResult 在我的情况），生成的映射类。该类将使用原来的列名从code， ORDERID 在我的情况下，第1步。事实上，就可以避免这整个步骤，如果在步骤1中声明别名入手，即。 SELECT TOP 1的OrderID作为FakeId从订单明细。如果你没有，虽然，你需要去和调整的东西。

4) Since we're using the FakeId alias we need to tweak the generated code. If you navigate to the mapped class that was generated for you in step 2 (FakeODIdsResult in my case). The class will be using the original column name from step 1 in the code, OrderId in my case. In fact, this whole step could be avoided if the statement in step 1 was aliased to start with, ie. SELECT TOP 1 OrderId As FakeId FROM OrderDetails. If you didn't though, you need to go in and tweak things.

FakeODIdsResult将使用 ORDERID ，因为它的别名，它们将返回任​​何 FakeId 。这将类似于此：

FakeODIdsResult will be using OrderId, which will return nothing since it aliases FakeId. It will look similar to this:

public partial class FakeODIdsResult
{
    private System.Nullable<int> _OrderId;

    public FakeODIdsResult()
    {
    }

    [Column(Storage = "_OrderId", DbType = "Int")]
    public System.Nullable<int> OrderId
    {
        get
        {
            return this._OrderId;
        }
        set
        {
            if ((this._OrderId != value))
            {
                this._OrderId = value;
            }
        }
    }
}

您需要做的是重新命名 ORDERID 到 FakeId 和 _OrderId 到 _FakeId 。一旦这样做了，你可以使用上面的ISingleResult像往常一样，例如：

What you need to do is rename OrderId to FakeId and _OrderId to _FakeId. Once that's done, you can use the ISingleResult above as you normally would, for example:

int fakeId = dc.FakeODIds(i).Single().FakeId;

---

这个结论我所用，并能找到的话题。

---

This concludes what I've used and was able to find on the topic.

这篇关于SqlMetal错误产生我的存储过程的返回类型（LINQ）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！