LINQ and Stored Procedures Not Always Magic

I had an interesting experience while trying to import a stored procedure into a LINQ to SQL Classes Designer surface today.  Normally this is a pretty straightforward process.  First you open Server Explorer, then go to one of your connections, find the stored procedure, and then drag and drop it onto the designer surface.  Boom, it's suddenly available from your DataContext as a method. 

That's exactly what I did today, except when I created a var for the stored procedure result set, and then added a foreach to loop through the rows in the result set... nothing showed in my Intellisense.  Huh?  A little hover magic and I saw that my generated method was returning an int.

After some investigation, I found that the designer has a hard time handling stored procedures that use temporary tables in them, as it throws off the procedure meta data.  As it turns out, there are two possible solutions.  First, you can use a table variable instead:

DECLARE @tempTable TABLE ( ... )

Your second option is to continue to use a temporary table, but hand modify your dbml file using an XML editor.  Simply right click on the file in Solution Explorer and choose Open With and then choose your favorite XML editor.  Mine is Notepad2.  Search through the file for your stored procedure, which for a procedure named "storedProcedureName" might look like this:

<Function Name="dbo.storedProcedureName" Method="storedProcedureName">
  <Parameter Name="Parameter1" Parameter="Parameter1" Type="System.Int32" DbType="Int" />
  <Return Type="System.Int32" />
</Function>

Then remove the <Return ... /> element and replace it with an <ElementType> node which may look like this:

<Function Name="dbo.storedProcedureName" Method="storedProcedureName">
  <Parameter Name="Parameter1" Parameter="Parameter1" Type="System.Int32" DbType="Int" />
  <ElementType Name="storedProcedureNameResult">
    <Column Name="Result1" Type="System.Int32" DbType="int NOT NULL" CanBeNull="false" />
    <Column Name="Result2" Type="System.String" DbType="varchar(10) NOT NULL" CanBeNull="false" />
    <!-- ... -->
  </ElementType>
</Function>