SqlMetal wrongly generates the return type of my stored proc (LINQ)

Question

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

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

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

Any ideas?

Answer 1

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 Designer (drag & drop in the VS IDE): ISingleResult

This is supported by Matt Warren at Microsoft:

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

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.

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):

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.

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.

---

Rewrite the SPROC

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

Rewrite #1 - Storing the result in a variable:

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

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.

Rewrite #2 - Use a case statement:

This is identical to Mark's suggestion.

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

---

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

---

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) Change your SPROC to return a single SELECT statement (comment out your actual code), such as SELECT TOP 1 OrderId FROM OrderDetails

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) 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) 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 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;
            }
        }
    }
}

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.