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();
}