Using context.Database.Log

In my recent project there was a requirement to develop a logging system each query performed by Entity framework should be logged with time stamp. It was all in the air when we started addressing performance tuning.
A good and common way to find out what’s going on behind the scenes is to use SQL Server profiler to trace all calls to the database. That way, you can see what SQL statements are being executed, how often, and how much time they take.
Logging all database operations can be achieved by assigning the DbContext.Database.Log property with an action that takes a string as a parameter.
When the Log property is set the following activities are logged −

  1. SQL for all different kinds of commands e.g. Queries, including inserts, updates, and 2. deletes generated as part of Save Changes
    3. Parameters
    4. Whether or not the command is being executed asynchronously
    5.. A time stamp indicating when the command started executing
    6. The command completed successfully or failed
    7. Some indication of the result value
    8. The approximate amount of time it took to execute the command

The DbContext.Database.Log property can be set to a delegate for any method that takes a string. Most commonly it is used with any TextWriter by setting it to the “Write” method of that TextWriter. All SQL generated by the current context will be logged to that writer. For example, the following code will log SQL to the console as well as log file:

public partial class MyDBCoreContext : DbContext
{
    static MyDBCoreContext()
    {
        Database.SetInitializer<MyDBCoreContext>(null);
    }

    public MyDBCoreContext(String connectionString)
        : base(connectionString)
    {
        this.Database.CommandTimeout = 240;
        this.Database.Log = DatabaseEfLogger;
    }

    public void DatabaseEfLogger(string sql)
    {
        Debug.Write(sql);
        var logger = LogManager.GetLogger(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType);
        logger.Debug(sql);
    }
}

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s