I’ve been struggling for awhile to figure out how to get Entity Framework to set and unset application roles in Sql Server when opening and before closing a connection. The problem is that ConnectionState does not provide a Closing state that fires before a connection is closed.
It was suggested to me to turn of connection pooling. Errrr, no. We want connection pooling for our applications. I also don’t want to have to manually open and close the connection every time I create a DbContext. That’s just messy and irritating.
The next obvious thing to do would be to create a DbConnectionDecorator to wrap the existing database connection and expose the Closing event. This proved to be very difficult because Entity Framework does not expose when and how it opens connections.
What’s that you say? I can implement my own EntityFramework Provider? There’s a provider wrapper toolkit I can plug into to do this? Awesome!
Oh wait—that looks really, really, REALLY complicated? You mean I can’t just decorate a single object? I have to decorate a whole family of objects?
Alright, tell you what I’ll do. I’ll implement the provider wrapper using the toolkit as best I can—but then I’m going to strip away everything I don’t actually need. Besides, if I just make the various data related events observable, it’s nothing to Trace the output. And Cacheing can easily be added as a IDbContext Decorator anyway. I don’t really get why that should be done at the Provider level.
Configuring Your Application to use the Provider
To use the new provider, first install the package. At application startup, you’ll need to register the provider and tell it which other provider you are wrapping. The registration process will set the ObservableConnectionFactory as the default connection factory used by EF unless you pass the optional setAsDefault:false.
Consuming the Provider Events
ObservableConnection exposes several new events, including Opening, Opened, Failed, Closing, and Closed. However, to subscribe to those events directly requires that you cast the DbConnection exposed by EF to ObservableDbConnection, which strikes me as a little cumbersome (not to mention a violation of the LSP). My first use case demands that I handle the Opening and Closing events the same way for every DbConnection application-wide. To that end, ObservableDbConnection (and ObservableDbCommand) pushes its event messages onto a static class called Hub.
This code is brand-spanking new and it hasn’t had time to bake yet. I’m using it, but it’s entirely possible that there are unforeseen problems. Feel free to report issues to and/or contribute to the open-source project on BitBucket. Until then, know that it has been rigorously test and that it works on my machine.