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)
devioblog 
This was very helpful. Thank you.
Instead Of create Temporary Table, Create Variable Table.
ex:
create table #table_name(…..)
change the above Table like the Following
Declare @table_name table(…..).
It will work.
You mean to say that, no need to write any partial class for the temp table..? and it require only
[Function(Name = “dbo.MyProc”)]
public IEnumerable MyProcResultset(
.. copy parameters from original declaration .. )
{
IExecuteResult result = this.ExecuteMethodCall(this,
((MethodInfo)(MethodInfo.GetCurrentMethod())), ..params..);
return result.ReturnValue as IEnumerable;
}
?
We are returning 2 temp tables from stored procedures.
We have created 2 class in dbml file which have same schema as temporary tables.
We have associcated these tables. The problem is data is getting loaded in first class collection but while loading data in second class collection it gives error as Invalid Object Name “Class name”.
Can you help on this
Worked using a table variable as suggested by Er Suresh Ganesan. Thanks.
Using table variable for return table from stored procedure is works fine. Thanks for this idea
Very helpfull thanks!
Just a caveat, using table type instead of temporary inmemory table is only applicable in sql server >=2005 in sql server 2000 we still have to use the temp table method.
Greeting,
Teixeira
Hi all!
Just want to give my input again, because i found that i couldn’t accomplish the goal just using the class with public members inside without gets and sets, didn’t work for me….when trying to bind it directly to the gridview control.
So, I had to use gets and sets to get it done!
JIC someone steps in the same, here’s another experience report that might help.
Greetings,
Teixeira
Accessors are a good point.
I did not use my classes in GridViews and other controls yet, so the problem didn’t occur for me.
I found an easy workaround for this problem. If you create a stored proc say : SELECT * FROM Products then drag it into the .dbml designer as usual.
You can then go back to the sproc and modify it any way you want including adding temp tables and using ‘exec’ which I also found would cause problems. As long as your final select columns match the object, then your OK.
usint table variable instead of temp table working fine ,thank you so much
Another way to fix this issue with Linq to SQL and #temptables that also supports SQL 2000 is to add the following statement to the top of your stored procedure:
SET FMTONLY OFF;
Now the designer will be able to inspect your return type just fine without the need for any strange workarounds.
thanks its work for me
Thanks it work
there is a trick you can use to create [return] classes for sprocs that either use temp tables or use a sp_executesql statement….
simply copy the select statement with all the fields to a new procedure and import it into the LINQ designer… this creates a class containing all these fields… now you just have to change the return type for your #temp or sp_executesql from INT to the object type created from the dummy sproc and presto
unfortunately you have to make the changes each time you modify your context, however i just use smaller contexts nowadays to avoid constantly having to re-do it
Thanks Luke Wrobel .. your suggestion of ‘including all fields in Select’ worked for me
Salam All,
We face this problem also in our applications but instead of drag an SP or these workarrounds you can just add the mapping in the .dbml file by yourself (i.e add the xml like another sps) and it depends only on the column names if they are compatible then it will work fine so your data dudes will not laugh on the limitation of your “limted tool”
I hope it’s helpful
@Teixeira – No, u dont need getters and setters as originally mentioned.
@Musab Ahmed Ali and all others pointing out other approaches – Firstly, thanks for that. My scenario is – i m using .NET 3.5 and the SP is provided to me by a third party – which is not viewable / changeable. Only, i can provide parameters and see the output and it does used #temp tables, hence only the above solution works for me. The gist is – the above solution will def be helpful for all kinds of requirements and can be considered an “one another approach”. I followed the below steps in my program:
1. Just create a new partial class extending from the org designer.cs class
2. Define output field names under [Column]as mentioned
3. Pass the parameters and results are returned as IEnumerable.
This was very helpful. Thank you.
Thanks for the great info to my problem! The table variable worked easiest for me!