Linq Temporary Tables - Does anyone see a problem with this?

Question

When trying to solve:

A large set of Linq.Contains causes a TDS error

I think I came across a solution, and I would like to see if this is a kosher way of approaching the problem.

(brief summary) I would like to linq-join against a list of record identifiers that (completely or at least easily) are not generated in SQL. This is a long list and often breaks the limit of 2100 points for calling TDS RPC. So what I would do in SQL, drop them into the temp table, and then join this when I needed it.

So, I did the same in Linq.

In my MyDB.dbml file, I added:

<Table Name="#temptab" Member="TempTabs"> <Type Name="TempTab"> <Column Name="recno" Type="System.Int32" DbType="Int NOT NULL" IsPrimaryKey="true" CanBeNull="false" /> </Type> </Table> 

Opening the constructor and closing it added the necessary entries there, although for completeness I will quote from the file MyDB.desginer.cs:

  [Table(Name="#temptab")] public partial class TempTab : INotifyPropertyChanging, INotifyPropertyChanged { private static PropertyChangingEventArgs emptyChangingEventArgs = new PropertyChangingEventArgs(String.Empty); private int _recno; #region Extensibility Method Definitions partial void OnLoaded(); partial void OnValidate(System.Data.Linq.ChangeAction action); partial void OnCreated(); partial void OnrecnoChanging(int value); partial void OnrecnoChanged(); #endregion public TempTab() { OnCreated(); } [Column(Storage="_recno", DbType="Int NOT NULL", IsPrimaryKey=true)] public int recno { get { return this._recno; } set { if ((this._recno != value)) { this.OnrecnoChanging(value); this.SendPropertyChanging(); this._recno = value; this.SendPropertyChanged("recno"); this.OnrecnoChanged(); } } } public event PropertyChangingEventHandler PropertyChanging; public event PropertyChangedEventHandler PropertyChanged; protected virtual void SendPropertyChanging() { if ((this.PropertyChanging != null)) { this.PropertyChanging(this, emptyChangingEventArgs); } } protected virtual void SendPropertyChanged(String propertyName) { if ((this.PropertyChanged != null)) { this.PropertyChanged(this, new PropertyChangedEventArgs(propertyName)); } } } 

Then it just became the subject of juggling with some things in the code. Where I usually had:

 MyDBDataContext mydb = new MyDBDataContext(); 

I had to get him to share his connection with a regular SqlConnection so that I could use the connection to create a temporary table. After that, it seems quite usable.

 string connstring = "Data Source.... etc.."; SqlConnection conn = new SqlConnection(connstring); conn.Open(); SqlCommand cmd = new SqlCommand("create table #temptab " + "(recno int primary key not null)", conn); cmd.ExecuteNonQuery(); MyDBDataContext mydb = new MyDBDataContext(conn); // Now insert some records (1 shown for example) TempTab tt = new TempTab(); tt.recno = 1; mydb.TempTabs.InsertOnSubmit(tt); mydb.SubmitChanges(); 

And using it:

 // Through normal SqlCommands, etc... cmd = new SqlCommand("select top 1 * from #temptab", conn); Object o = cmd.ExecuteScalar(); // Or through Linq var t = from tx in mydb.TempTabs from v in mydb.v_BigTables where tx.recno == v.recno select tx; 

Does anyone see a problem with this approach as a one-stop solution for using temporary tables in joins in Linq?

He solved my problem wonderfully, since now I can make a direct connection in Linq instead of using .Contains ().

Postscript : The only problem I ran into is that mixing Linq and regular SqlCommands in a table (where you read / write, as well as others) can be dangerous. Always using SqlCommands to insert into a table and then Linq commands to read, this works great. Linq seems to cache the results - perhaps around, but that was not obvious.

Answer 1

I do not see a problem using temporary tables to solve your problem. Regarding mixing SqlCommands and LINQ, you are absolutely right about the danger factor. It is so easy to execute your SQL statements using a DataContext, I would not even bother with SqlCommand:

 private string _ConnectionString = "<your connection string>"; public void CreateTempTable() { using (MyDBDataContext dc = new MyDBDataContext(_ConnectionString)) { dc.ExecuteCommand("create table #temptab (recno int primary key not null)"); } } public void DropTempTable() { using (MyDBDataContext dc = new MyDBDataContext(_ConnectionString)) { dc.ExecuteCommand("DROP TABLE #TEMPTAB"); } } public void YourMethod() { CreateTempTable(); using (MyDBDataContext dc = new MyDBDataContext(_ConnectionString)) { ... ... do whatever you want (within reason) ... } DropTempTable(); } 

Answer 2

We have a similar situation, and although this works, the problem is that you are not actually dealing with Queryables, so you cannot easily use this “with” LINQ. This is not a solution that works with method chains.

Our final decision was to simply drop what we want in the stored procedure and write a choice in this procedure against temporary tables when we want these values. This is a compromise, but both are workarounds. At least, with the stored procedure, the developer will generate the calling code for you, and you have a black box implementation, so if you need to continue the configuration, you can do this strictly within the procedure without recompiling.

In an ideal world, there will be some support in the future for writing Linq2Sql statements that will allow you to determine the use of temporary tables in your queries, avoiding the unpleasant sql IN operator for complex scripts like this.

Answer 3

As a “one-stop solution”, what if the code runs on multiple threads / applications? I think that solving a large list is always related to a problem area. It is better to use a regular table for the problem you are working on.

I once created a “shared table” in a database. The table was created with three columns: int, uniqueidentifier and varchar, as well as other columns to manage each list. I thought: "this should be enough to handle many cases." But I soon got a job requiring the connection to be done with a list of three integers. After that, I no longer tried to create a "generic" list table.

In addition, it is best to create an SP to insert multiple items into the list table in each database call. You can easily insert ~ 2000 items in less than 2 dB. Depending on what you are doing, performance may be unimportant.

EDIT: forgot that this is a temporary table and a temporary table for each connection, so my previous argument for multiple threads was wrong. But still, this is not a general solution for providing a fixed circuit.

Answer 4

Will Neal's solution really work? If its temporary table, and each of the methods creates and deletes its own data context, I do not think that the temporary table would still be there after the connection was deleted.

Even if he were there, I think that this will be an area in which you assume some functionality of how queries and connections are rendered, and this is due to big problems with linq for sql - you just don’t know what may fail as engineers come up with better ways to do things.

I would do this in a saved proc. You can always return a result set to a predefined table if you want.