SELECT from temp table in Stored Procedure with LINQ to SQL

I started with LINQ and MVC recently, and found the following restriction in LINQ to SQL:

Thus the LINQ to SQL code generator does not support the following:

Stored procedures that produce results based on temporary table.

While this statement is true for generated code, you can still manually work around the problem.

When you create a LINQ to SQL item, VS creates a .dbml and a .designer.cs file for the database connection specified. The .cs file contains the DataContext, which is declared as a public partial class, which means we can easily extend it.

Suppose we have a database called MyDB and a stored procedure MyProc which collects data in a temporary table and returns this data using a SELECT * FROM #Temp.

To declare the #Temp record in C#, we need to create a separate .cs file (e.g. MyDBExt.cs).

Start with a C# class mapping to the #Temp table (taken from one of my projects):

[Table]
public partial class MyProc_Result
{
    public MyProc_Result()    {   }

    [Column]
    public int User_OID;
    [Column]
    public string User_ID;
    [Column]
    public int ItemCount;
    [Column]
    public string Description;
}

What’s important here is that the class has the [Table] attribute, and each field has a [Column] attribute from the System.Data.Linq.Mapping namespace. No getters and setters necessary, as in the generated class file.

Next, extend the partial class from the original .designer.cs file and create an additional method for your SP:

[Function(Name = "dbo.MyProc")]
public IEnumerable<MyProc_Result> MyProcResultset(
    .. copy parameters from original declaration .. )
{
    IExecuteResult result = this.ExecuteMethodCall(this,
        ((MethodInfo)(MethodInfo.GetCurrentMethod())), ..params..);
    return result.ReturnValue as IEnumerable<MyProc_Result>;
}

Since overloaded methods are distinguished by the types of their parameters, and not by their return type, you have to change the method name for your extension. (Optionally, you can derive your own class and override the original method)