Execute a SQL stored procedure before every query generated by EntityFramework

Question

I need to execute a SQL stored procedure every time before I query my ObjectContext. What I want to achieve is setting the CONTEXT_INFO to a value which will be later on used with most of my queries.

Has anyone done that? Is that possible?

[EDIT]

Currently I'm achieving this by opening the connection and executing the stored procedure in my ObjectContext constructor like this:

public partial class MyEntitiesContext
{       
    public MyEntitiesContext(int contextInfo) : this()
    {
        if (Connection.State != ConnectionState.Open)
        {
            Connection.Open(); // open connection if not already open
        }

        var connection = ((EntityConnection)Connection).StoreConnection;

        using (var cmd = connection.CreateCommand())
        {
            // run stored procedure to set ContextInfo to contextInfo
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = "[dbo].[SetContextInfo]";
            cmd.Parameters.Add(new SqlParameter("@ci", _contextInfo));
            cmd.ExecuteNonQuery();
        }
        // leave the connection open to reuse later
    }
}

Then in my integration test:

[TestMethod]
public void TestMethod1()
{
    using (var ctx = new MyEntitiesContext(1))
    {               
        Assert.AreEqual(2, ctx.Roles.ToList().Count);
        Assert.AreEqual(2, ctx.Users.ToList().Count);
    }
}

But this requires me to leave the connection open - this is error prone since I will always need CONTEXT_INFO, and another developer might easily do:

[TestMethod]
public void TestMethod2()
{
    using (var ctx = new MyEntitiesContext(1))
    {               
        // do something here
        // ... more here :)
        ctx.Connection.Close(); // then out of the blue comes Close();
        // do something here
        Assert.AreEqual(2, ctx.Roles.ToList().Count);
        Assert.AreEqual(2, ctx.Users.ToList().Count); // this fails since the where
        // clause will be:
        // WHERE ColumnX = CAST(CAST(CONTEXT_INFO() AS BINARY(4)) AS INT)
        // and CONTEXT_INFO is empty - there are no users with ColumnX set to 0
        // while there are 2 users with it set to 1 so this test should pass
    }
}

The above means that I can write the code like in my test and everthing is green (YAY!) but then my colleague uses the code from TestMethod2 somewhere in his business logic and it's all f'd up - and nobody knows where and why since all tests are green :/

[EDIT2]

This blog post certainly does not answer my question but actually solves my problem. Maybe going with NHibernate will be better suited for my purpose :)

Answer 1

We have used this pattern.

But the way we did it was to call the stored procedure as the first opperation inside each db context.