thoughts on coding

February 28, 2011

Entity Framework: logging

Filed under: .NET, Entity Framework, Uncategorized — Tags: , , — Frantisek @ 11:25 am

I use EF4 CTP5 and I’d like to log ANY!!! SQL commands sent to the DB. You can use the method ToTraceString() to display the SQL commands of any ObjectQuery, but … you are not able to see the queries generated i.e. while saving changes OR lazy loading. You need to use i.e. EFProfiler to log it. EFProfiler is very cool tool but … I need to log it by my application.

So I found out the Tracing and Caching implementation by Jaroslaw Kowalski here which extends EF with tracing/logging and caching capabilities. The main disadvantages I found out are:
a) you need to you both! features otherwise logging/tracing will not work. I wanted to use only logging! After doing so I started to get strange exception (or I had to do something wrong). After enabling also tracing, it started to work. Shame because I needed to use ONLY tracing.
b) your Context classes HAS to be derived from his special classes.

Honestly, I don’t use this solution. It’s too much work.

MS guys, I’m sorry but I must say: IT’S BIG SHAME EF WAS NOT DEVELOPED IN EASY EXTENSIBLE MANNER with i.e. ServiceLocator, interceptors, etc. in place.

As far as I know, and correct me if I’m wrong, adding logging capabilities fits to AOP interceptors.

I.e. EFProfiler has quite hardly injects itself into creating new DB connection logic and then it has a capability to log everything. Their solution is more transparent than a by Mr. Kowalski.

My solution
I used the ideas from NHProfiler and I generalized it little bit. I made base AOP filter called IAopFilter.

public interface IAopFilter

    {

        void CommandDurationAndRowCount(Guid connectionId, long milliseconds, int? rowCount);

        void ConnectionDisposed(Guid connectionId);

        void ConnectionStarted(Guid connectionId);

        void DtcTransactionCompleted(Guid connectionId, TransactionStatus status);

        void DtcTransactionEnlisted(Guid connectionId, IsolationLevel isolationLevel);

        void StatementError(Guid connectionId, Exception exception);

        void StatementExecuted(Guid connectionId, Guid statementId, string statement);

        void StatementRowCount(Guid connectionId, Guid statementId, int rowCount);

        void TransactionBegan(Guid connectionId, System.Data.IsolationLevel isolationLevel);

        void TransactionCommit(Guid connectionId);

        void TransactionDisposed(Guid connectionId);

        void TransactionRolledBack(Guid connectionId);

    }

Due to the short of the time, I made only 1 its implementation and it was using Log4Net capabilities. Then I had to create AOP capable DbProviderFactory class called DbProviderFactoryEx which uses IAopFilters. In addition I had to create whole family of the classes following abstract factory pattern implemented in DbProviderFactory.

The main class is called EntityFrameworkExtender. This class wires-up AOP capabilities into EF. You just need to call:
EntityFrameworkExtender.Initialize();

The class by default uses Log4NetFilter as only the one filter but you can freely configure it.

EntityFrameworkExtender.Filters = () => new[] {new Log4NetFilter(“MyClass”)};

            EntityFrameworkExtender.Initialize();

Using it you are able transparently trace/log every SQL command sent from EF to DB.

I think, we, EF users would profit very much IF the EF team would develope the framework on enterprise level – easily extensible, configurable and if we could get also the unit tests! together with the framework.

[UPDATED!]
I bundled the whole source code into 1 project. The whole source code can be found here – save it as a file to the local disk  and delete .doc extension. It’s  normal zip file.
To use it you will need: log4net 1.2.10.0, EF4 CTP5, .NET 4.0

Blog at WordPress.com.