LINQ-to-SQL: Stored Procedure that returns a single scalar value?

Question

I am using LINQ-to-SQL for an application that queries a legacy database. I need to call a stored procedure, that selects a single integer value. Changing the stored procedure is not an option.

The designer creates a method with this signature:

private ISingleResult<sp_xal_seqnoResult> NextRowNumber([Parameter(DbType="Int")] System.Nullable<int> increment, [Parameter(DbType="Char(3)")] string dataset)

I would like the return type to be int. How do I do this using LINQ-to-SQL ?

Answer 1

This would be trivial with a scalar function (UDF) rather than an SP. However, it should work easily enough - although if the SP is complex (i.e. FMT_ONLY can't inspect it 100%) then you might need to "help" it...

Here's some dbml that I generated from a simplfied SP that returns an integer; you can edit the dbml via "open with... xml editor):

<Function Name="dbo.foo" Method="foo">
    <Parameter Name="inc" Type="System.Int32" DbType="Int" />
    <Parameter Name="dataset" Type="System.String" DbType="VarChar(20)" />
    <Return Type="System.Int32" />
</Function>

(note you obviously need to tweak the names and data-types).

And here is the generated C#:

[Function(Name="dbo.foo")]
public int foo([Parameter(DbType="Int")] System.Nullable<int> inc, [Parameter(DbType="VarChar(20)")] string dataset)
{
    IExecuteResult result = this.ExecuteMethodCall(this, ((MethodInfo)(MethodInfo.GetCurrentMethod())), inc, dataset);
    return ((int)(result.ReturnValue));
}

If your current SP uses SELECT (instead of RETURN), then the DBML will need to reflect this. You can fix this by hiding the implementation details, and providing a public wrapper in a partial class; for example:

<Function Name="dbo.foo" Method="FooPrivate" AccessModifier="Private">
    <Parameter Name="inc" Type="System.Int32" DbType="Int" />
    <Parameter Name="dataset" Type="System.String" DbType="VarChar(20)" />
    <ElementType Name="fooResult" AccessModifier="Internal">
      <Column Name="value" Type="System.Int32" DbType="Int NOT NULL" CanBeNull="false" />
    </ElementType>
</Function>

The above describes an SP that returns a single table with a single column; but I've made the SP "private" to the data-context, and the result-type "internal" to the assembly (hiding it):

[Function(Name="dbo.foo")]
private ISingleResult<fooResult> FooPrivate(
    [Parameter(DbType="Int")] System.Nullable<int> inc,
    [Parameter(DbType="VarChar(20)")] string dataset)
{
    IExecuteResult result = this.ExecuteMethodCall(this, ((MethodInfo)(MethodInfo.GetCurrentMethod())), inc, dataset);
    return ((ISingleResult<fooResult>)(result.ReturnValue));
}

Now in my own class file I can add a new partial class (a new .cs file) in the correct namespace, that exposes the method more conveniently:

namespace MyNamespace {
    partial class MyDataContext
    {
        public int Foo(int? inc, string dataSet)
        {
            return FooPrivate(inc, dataSet).Single().value;
        }
    }
}

(the namespace and context names need to be the same as the actual data-context). This adds a public method that hides the grungy details from the caller.

Don't edit the designer.cs file directly; your changes will be lost. Only edit either the dbml or partial classes.