LINQ to SQL with dynamic sql and temporary tables

Recently I was fiddling with .NET Framework 3.5 and LINQ. LINQ is a very interesting concept. When working with Framework 2.0, I used to create classes manually to store the data retrieved from Database. Now, we have a drag-and-drop, automatic way of generating classes.

Over the weekend, I was using LINQ to SQL to execute an existing stored procedure which returned results by using dynamic sql. However, when I dropped the procedure into the dbml file, it generated a function with the return type as integer.

After a quick round of googling, it turns out that LINQ to SQL does not work as expected with dynamic sql, as well as temporary tables.

Not wanting to do any modification on the shared stored procedure, a workaround is required.

The solution is surprisingly simple.

1. Replace the affected stored procedure with a dummy one, that returns all the fields the normal one should return (name and type must be same).
2. Drop this dummy stored procedure into the dbml file. This will generate the correct return type for that procedure.
3. Modify the dummy stored procedure back to the correct one.

That's all!! A very quick and dirty solution.