The scenario you are describing is by design. I tested with .NET 3.5 and .NET 4.0 Beta 2 and got the same results. Given SPROC, using the IF / ELSE structure, like your, generated results and tools used:
- SqlMetal : IMultipleResults
- LINQ To SQL Designer (drag and drop in VS IDE): ISingleResult
This is supported by Matt Warren at Microsoft:
The designer does not recognize stored procs with multiple return values and will display them all to return a single unit.
The SQLMetal command-line tool will recognize multiple results and will inject the return method correctly, like IMultipleResults. You can use SQLMetal or modify DBML manually or add a signature method for this stored procedure for your own partial class for your DataContext.
On this Dinesh blog post, Kulkarni comments on the opposite scenario when the designer does not add IMultipleResults and instead uses ISingleResult. He states (emphasis added):
And no, the designer does not support this feature. Therefore, you need to add a method in your partial class. SqlMetal however extracts sproc. The reason for this is the implementation in detail: the two use the same code generator, but different database extractors schemes.
In addition, the “Handling Multiple Result Forms from SPROC” section in Scott Gu's post and this MSDN article show how IMultipleResults are used with SPROC that use the same structure.
Great, what now? There are several workarounds, some of them are better than others.
Rewrite SPROC
You can rewrite SPROC so that SqlMetal generates a function using ISingleResult. This can be achieved using
Rewrite # 1 - Saving 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 must be similar or something that can be passed on to another. For example, if one of them was INT and the other was DECIMAL(8, 2) , you would use a decimal digit to maintain accuracy.
Rewrite # 2 - Use the case statement:
This is in line with Mark's suggestion.
SELECT TOP 1 CASE WHEN @Input = 1 THEN OrderId ELSE ProductId END FROM OrderDetails
Use UDF instead of SPROC
You can use a scalar-valued UDF and configure your request to use the UDF format (similar to the variable option 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 SPROC and disable it
It works, but more tiring than the previous ones. In addition, future use of SqlMetal will replace these changes and require a repetition of the process. Using a partial class and relocating relative code would help prevent this.
1) Modify your SPROC to return a single SELECT (comment out your actual code), e.g. 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 to its original form, but use the same alias for the return 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
Note that I am not using a variable here, but using the format you started directly with.
4) Since we use the alias FakeId, we need to customize the generated code. If you go to the mapped class that was generated for you in step 2 ( FakeODIdsResult in my case). The class will use the original column name from step 1 in the code, OrderId in my case. In fact, this entire step could have been avoided if smoothing had been smoothed in the first step, i.e. SELECT TOP 1 OrderId As FakeId FROM OrderDetails . If you haven’t done this, you need to log in and configure everything.
FakeODIdsResult will use OrderId , which will not return anything, since these are aliases of FakeId . It will look something like 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 . After that, you can use ISingleResult above, as usual, for example:
int fakeId = dc.FakeODIds(i).Single().FakeId;
This completes what I used and could find on this topic.