Entity Framework 5 - Implementing SQL Server "Execute As User"

Question

I am writing a database application using Visual Studio 2012 with Entity Framework 5 and SQL Server 2008. I would like Entity Framework to impersonate a SQL Server user (i.e. user without a login). I have created a new constructor for the DB context MyDatabaseEntities which includes an argument for the name of the user to impersonate. Here is the code that I've written:

public partial class MyDatabaseEntities
{
    private String _impersonateUser = null;

    public MyDatabaseEntities(String impersonateUser)
        : base("MyConnectionString")
    {
        _impersonateUser = impersonateUser;

        this.Database.Connection.StateChange += Connection_StateChange;

    }

    void Connection_StateChange(object sender, StateChangeEventArgs e)
    {
        if (e.CurrentState == ConnectionState.Open && e.OriginalState != ConnectionState.Open)
        {
            using (var cmd = this.Database.Connection.CreateCommand())
            {
                cmd.CommandType = CommandType.Text;

                cmd.Parameters.Add(new SqlParameter("user", _impersonateUser));

                cmd.CommandText = "EXECUTE AS USER = @user";

                cmd.ExecuteNonQuery();

            }

        }

    }

I had to add the check...

if (e.CurrentState == ConnectionState.Open && e.OriginalState != ConnectionState.Open)

...because the method Connection_StateChange method seems to execute even when the state hasn't changed. Then problem is that when I run the code twice,

public void RunSimpleQuery()
{
    using (MyDatabaseEntities context = new MyDatabaseEntities("UserName"))
    {
        var result = context.TableName.ToList();

    }

}

...Entity Framework throws a SqlException:

A severe error occurred on the current command. The results, if any, should be discarded.\r\nA severe error occurred on the current command. The results, if any, should be discarded.

Any ideas?

Update 1

I in my code above, I changed...

cmd.CommandText = "EXECUTE AS USER = @user;";

...to...

cmd.CommandText = "REVERT; EXECUTE AS USER = @user;";

...and I still get the same SqlException error.

Answer 1

The problem is that EF closes connection when it doesn't need it and returns it back to the pool. So when it executes some SQL again it request new connection from the pool where your event may not be initialized. But again I believe that you should try to solve this with manually controlling connection lifetime to have both benefit of connection pooling and be able to meet your requirements.

Answer 2

I know is an old question, but maybe will be useful for someone.

I did in a different way, using your code...

Instead of

Connection_StateChanged event

I create two methods in the same class:

    public void ChangeUser(String sUser)
    {
        if(Database.Connection.State != ConnectionState.Open)
            Database.Connection.Open();

        using (var cmd = Database.Connection.CreateCommand())
        {
            cmd.CommandType = CommandType.Text;
            cmd.Parameters.Add(new SqlParameter("user", sUser));
            cmd.CommandText = "EXECUTE AS USER = @user;";
            cmd.ExecuteNonQuery();
        }
    }

    public void Revert()
    {
        if (Database.Connection.State != ConnectionState.Open)
            Database.Connection.Open();

        using (var cmd = Database.Connection.CreateCommand())
        {
            cmd.CommandType = CommandType.Text;
            cmd.CommandText = "REVERT;";
            cmd.ExecuteNonQuery();
        }
    }

I use it before and after execute stored procedure,

using (var db = new MyDatabaseEntities())
        {
            db.ChangeUser(model.Username);
            var result = db.Something();
            db.Revert();
            return result;
        }

It works fine with SPs and it doesn't throw an exception even after many executions. If I could catch an event after command execute, maybe all be encapsulated on MyDatabaseEntities.