Databases
StackOverflow2013
Postsdbo
POTemporary tables in Linq -- Anyone see a problem with this?
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POTemporary tables in Linq -- Anyone see a problem with this?
    primarykey
    data
    text
    <p>In trying to solve:</p> <p><A href="http://stackoverflow.com/questions/1007893">Linq .Contains with large set causes TDS error</a></p> <p>I think I've stumbled across a solution, and I'd like to see if it's a kosher way of approaching the problem.</p> <p>(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. </p> <p>So I did the same in Linq.</p> <p>In my MyDB.dbml file I added:</p> <pre><code>&lt;Table Name="#temptab" Member="TempTabs"&gt; &lt;Type Name="TempTab"&gt; &lt;Column Name="recno" Type="System.Int32" DbType="Int NOT NULL" IsPrimaryKey="true" CanBeNull="false" /&gt; &lt;/Type&gt; &lt;/Table&gt; </code></pre> <p>Opening the designer and closing it added the necessary entries there, although for completeness, I will quote from the MyDB.desginer.cs file:</p> <pre><code> [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)); } } } </code></pre> <p>Then it simply became a matter of juggling around some things in the code. Where I'd normally have had:</p> <pre><code>MyDBDataContext mydb = new MyDBDataContext(); </code></pre> <p>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.</p> <pre><code>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(); </code></pre> <p>And using it:</p> <pre><code>// 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; </code></pre> <p>Does anyone see a problem with this approach as a general-purpose solution for using temporary tables in joins in Linq?</p> <p>It solved my problem wonderfully, as now I can do a straightforward join in Linq instead of having to use .Contains().</p> <p><strong>Postscript</strong>: 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.</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USJoel Coehoorn
    1. USClinton Pierce
    plurals
    1. PL
      singulars
      1. LTLinked
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    3. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POTemporary tables in Linq -- Anyone see a problem with this?
      1. USRobert Harvey
      1. VTFavorite
    2. VO
      singulars
      1. POTemporary tables in Linq -- Anyone see a problem with this?
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. POTemporary tables in Linq -- Anyone see a problem with this?
      1. This table or related slice is empty.
      1. VTUpMod
    1. This table or related slice is empty.
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload