Temporary tables in Linq -- Anyone see a problem with this?

Question

In trying to solve:

Linq .Contains with large set causes TDS error

I think I've stumbled across a solution, and I'd like to see if it's a kosher way of approaching the problem.

(short summary) I'd like to linq-join against a list of record id's that aren't (wholly or at least easily) generated in SQL. It's a big list and frequently blows past the 2100 item limit for the TDS RPC call. So what I'd have done in SQL is thrown them in a temp table, and then joined against that when I needed them.

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 designer and closing it added the necessary entries there, although for completeness, I will quote from the MyDB.desginer.cs file:

   [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 simply became a matter of juggling around some things in the code. Where I'd normally have had:

MyDBDataContext mydb = new MyDBDataContext();

I had to get it to share its connection with a normal SqlConnection so that I could use the connection to create the 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 general-purpose solution for using temporary tables in joins in Linq?

It solved my problem wonderfully, as now I can do a straightforward join in Linq instead of having to use .Contains().

Postscript: The one problem I do have is that mixing Linq and regular SqlCommands on the table (where one is reading/writing and so is the other) can be hazardous. Always using SqlCommands to insert on the table, and then Linq commands to read it works out fine. Apparently, Linq caches results -- there's probably a way around it, but it wasn't obviousl.

Answer 1

I don't see a problem with using temporary tables to solve your problem. As far as mixing SqlCommands and LINQ, you are absolutely correct about the hazard factor. It's so easy to execute your SQL statements using a DataContext, I wouldn't even worry about the 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 while this works, the issue becomes that you aren't really dealing with Queryables, so you cannot easily use this "with" LINQ. This isn't a solution that works with method chains.

Our final solution was just to throw what we want in a stored procedure, and write selects in that procedure against the temp tables when we want those values. It is a compromise, but both are workarounds. At least with the stored proc the designer will generate the calling code for you, and you have a black boxed implementation so if you need to do further tuning you can do so strictly within the procedure, without a recompile.

In a perfect world, there will be some future support for writing Linq2Sql statements that allow you to dicate the use of temp tables within your queries, avoid the nasty sql IN statement for complex scenarios like this one.

Answer 3

As a "general-purpose solution", what if you code is run in more than one threads/apps? I think big-list solution is always related to the problem domain. It's better to use a regular table for the problem you are working on.

I once created a "generic" list table in database. The table was created with three columns: int, uniqueidentifier and varchar, along with other columns to manage each list. I was thinking: "it ought to be enough to handle many cases". But soon I received a task that requires a join be performed with a list on three integers. After that, I never tried to create "generic" list table again.

Also, it's better to create a SP to insert multiple items into the list table in each database call. You can easily insert ~2000 items in less than 2 db round trips. Of cause, depending on what you are doing, performance may do not matter.

EDIT: forgot it is a temporary table and temporary table is per connection, so my previous argument on multi-threads was not proper. But still, it is not a general solution, for enforcing the fixed schema.

Answer 4

Would the solution offered by Neil actually work? If its a temporary table, and each of the methods is creating and disposing its own data context, I dont think the temporary table would still be there after the connection was dropped.

Even if it was there, I think this would be an area where you are assuming some functionality of how queries and connections end up being rendered, and thats ome of the big issues with linq to sql - you just dont know what might happen downt he track as the engineers come up with better ways of doing things.

I'd do it in a stored proc. You can always return the result set into a pre-defined table if you wish.