title: How to use SQL session-based tables with Entity Framework 5
body: <p>I have a WCF Data Service and I intend to use some <strong>session-based table functions</strong> (that creates temporary tables that are usable in the current session) upon <em>insert</em> or <em>update</em>.</p>
  
  <p>I tried to use the <code>SaveChanges</code> method like this:</p>
  
  <pre><code>public partial class MyContext: DbContext
  {
      public override int SaveChanges()
      {          
          var res = SetValues(true);
          var s = Database.SqlQuery&lt;string&gt;("SELECT [Key] FROM  TempContextView").ToList();
          System.IO.File.AppendAllText(@"c:\Temp\session.txt", $"SIZE S: {s.Count}, script res: {res}");
          foreach (var element in s)
          {            
              System.IO.File.AppendAllText(@"c:\Temp\session.txt", $"RES: {element}"); //never reached
          }
          return base.SaveChanges();
      }
  
      public int SetValues(bool insert)
      {
          System.IO.File.AppendAllText(@"c:\Temp\session.txt", "SetV: " + insert);
          return Database.ExecuteSqlCommand(insert ? "INSERT INTO TempContextView ([Key],[Value]) VALUES('Flag', '1')" : "DELETE FROM TempContextView WHERE[Key] = 'Flag'");
      }
  }
  </code></pre>
  
  <p>The <em>TempContextView</em> is a <em>view</em> that provides a <em>temporary table</em> created by a function:</p>
  
  <pre><code>SELECT  TOP (32) [Key], Value
  FROM    Schema1.getContextTable()
  ORDER BY [Key]
  
  function [Schema1].[getContextTable]()
    RETURNS @Context TABLE([Key] varchar(126), [Value] varchar(126))
    WITH SCHEMABINDING
  as...
  </code></pre>
  
  <p>However, when I select the values from the table that is created by the function, it returns nothing (the query size is 0, yet the insert returns 1).</p>
  
  <p>Does it mean, I can't use EF with sessions? Or every EF function uses <em>its own context</em>?
  As the session table is used by other triggers, I need to have the proper key value.</p>
  
  <p>What should I do about this? Any hint if EF is able to use these type of functionality?</p>
  
  <p><strong>UPDATE:</strong></p>
  
  <p>I have learned that EF uses <code>exec sp_reset_connection</code> before each executed command, and it <em>resets all the temporary variables and tables</em>.</p>
  
  <p>So I tried to create a transaction to force EF to execute the commands in one session:</p>
  
  <pre><code>  using (var scope = new TransactionScope(TransactionScopeOption.RequiresNew))
    {
      Database.ExecuteSqlCommand("INSERT INTO TempContextView ([Key],[Value]) VALUES('Flag', '1')"); //session #1?
      base.SaveChanges(); //session #2? :(
      scope.Complete();
    }
  </code></pre>
  
  <p>It still creates new sessions, so I can't really merge the two commands. </p>
  
  <p><a href="https://i.stack.imgur.com/7P7rv.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/7P7rv.png" alt="enter image description here"></a></p>
  
  <p>Any suggestions?</p>