How to use SQL session-based tables with Entity Framework 5

Question

I have a WCF Data Service and I intend to use some session-based table functions (that creates temporary tables that are usable in the current session) upon insert or update.

I tried to use the SaveChanges method like this:

public partial class MyContext: DbContext
{
    public override int SaveChanges()
    {          
        var res = SetValues(true);
        var s = Database.SqlQuery<string>("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'");
    }
}

The TempContextView is a view that provides a temporary table created by a function:

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...

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).

Does it mean, I can't use EF with sessions? Or every EF function uses its own context? As the session table is used by other triggers, I need to have the proper key value.

What should I do about this? Any hint if EF is able to use these type of functionality?

UPDATE:

I have learned that EF uses exec sp_reset_connection before each executed command, and it resets all the temporary variables and tables.

So I tried to create a transaction to force EF to execute the commands in one session:

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

It still creates new sessions, so I can't really merge the two commands.

Any suggestions?

Answer 1

EF will open and close the SqlConnection for every command (causing connection resets), unless

1. Explicitly open the connection and pass it to the DbContext constructor,
2. Call DbContext.Database.Connection.Open(), or
3. Use a Transaction, which will cause the connection pool to return you the same connection every time.

Edit:
Looks like TransactionScope does not suppress the connection reset when the connection is checked out of the segregated connection pool. So with TransactionScope you would still have to explicitly open the DbContext.Database.Connection to use session state between commands.

But DbContext.Database.BeginTransaction() works (probably by preventing connection pooling for the life of the DbContext).

Here's a complete working example using sp_set_sesson_context:

using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;
using System.Data.Entity;
using System.Data.Entity.Core.Metadata.Edm;
using System.Data.Entity.Infrastructure;
using System.Data.Entity.ModelConfiguration.Conventions;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Transactions;

namespace ConsoleApp6
{
    [Table("Customers")]
    public class Customer
    {
        public int CustomerID { get; set; }
        public string Name { get; set; }

        [DatabaseGenerated(DatabaseGeneratedOption.Computed)]
        public string UpdatedBy { get; set; }
    }

    class Db : DbContext
    {
        public DbSet<Customer> Customers { get; set; }
    }
    
    class Program
    {
        static void Main(string[] args)
        {
            Database.SetInitializer(new DropCreateDatabaseAlways<Db>());

            using (var db = new Db())
            {
                db.Database.Initialize(false);
                db.Database.ExecuteSqlCommand(@"
create trigger tg_customer on customers after insert, update 
as
begin
   update customers set UpdatedBy = cast(SESSION_CONTEXT(N'user') as varchar(200))
   where CustomerId in (select CustomerId from inserted);
end");
            }

            using (var db = new Db())
            {
                using (var tran = db.Database.BeginTransaction())
                {
                    db.Database.Log = m => Console.WriteLine(m);

                    db.Database.ExecuteSqlCommand(
                        "EXEC sp_set_session_context 'user', 'joe'"); //set session context

                    var c = db.Customers.Create();

                    c.Name = "Fred";

                    db.Customers.Add(c);

                    db.SaveChanges();
                    Console.WriteLine(c.UpdatedBy); //joe
                    tran.Commit();
                }
            }

            using (var db = new Db())
            {
                using (var scope = new TransactionScope(TransactionScopeOption.RequiresNew))
                {
                    db.Database.Connection.Open();
                    db.Database.ExecuteSqlCommand(
                        "EXEC sp_set_session_context 'user', 'alice'"); //set session context

                    var fred = db.Customers.Where(c => c.Name == "Fred").Single();

                    fred.Name = "Fred Jones";

                    db.SaveChanges();

                    Console.WriteLine(fred.UpdatedBy); //alice
                    scope.Complete();
                }
            }

            Console.ReadKey();
        }
    }
}

Answer 2

Alternatively to the solutions offered by David, here's another option in case you're still using Entity Framework 6.2.

You can create an event that will be called each time the "ConnectionState" changes to "Open".

    private void OnConnectionStateChange(object sender, StateChangeEventArgs args)
    {
        if (args.OriginalState == ConnectionState.Closed && args.CurrentState == ConnectionState.Open)
        {
            using (var command = Database.Connection.CreateCommand())
            {
                command.CommandText = $"EXEC sp_set_session_context 'AppUser', '{PrincipalProvider.User}'";
                command.ExecuteNonQuery();                     
            }
        }
    }

Just before calling the SaveChangesAsync() you can set this event and remove it through the finally-block.

Database.Connection.StateChange += OnConnectionStateChange;
var result = await base.SaveChangesAsync();
[...]
finally
{
   Database.Connection.StateChange -= OnConnectionStateChange;
}

The reason why you want to add and remove the event, instead of calling it through the constructor, is to avoid that every SELECT statement would call the sp_set_session_context stored procedure.