If you use EF 6, it is possible to add logging functionality that will reveal why an exception in the data layer of your app or system occured and in addition creating a runnable SQL
that you might try out in your testing/production environment inside a transaction that is rollbacked for quicker diagnosis-cause-fix cycle!
First off, create a class that implements the interface
IDbCommandInterceptor in the System.Data.Entity.Infrastructure.Interception namespace.
This class is then added using in your ObjectContext / DbContext class (this is a usually a partial class that you can extend) using the
DbInterception.Add method.
I add this class in the static constructor of my factory class inside a try-catch block. The important part is that you call the DbInterception.Add method and instantiate the class you create.
Let's consider a code example of this. I am only focusing on logging exceptions, other kind of interceptions can of course be performed.
Here is the sample class for logging exceptions, I have replaced the namespaces of the system of mine with the more generic "Acme":
using System;
using System.Data;
using System.Data.Common;
using System.Data.Entity.Infrastructure.Interception;
using System.Diagnostics;
using System.Linq;
using System.Text;
using System.Text.RegularExpressions;
using MySoftware.Common.Log;
namespace Acme.Data.EntityFramework
{
/// <summary>
/// Intercepts exceptions that is raised by the database running an operation and propagated to the eventlog for logging
/// </summary>
public class AcmeDbCommandInterceptor : IDbCommandInterceptor
{
public void NonQueryExecuted(DbCommand command, DbCommandInterceptionContext<int> interceptionContext)
{
LogIfError(command, interceptionContext);
}
public void NonQueryExecuting(DbCommand command, DbCommandInterceptionContext<int> interceptionContext)
{
LogIfError(command, interceptionContext);
}
public void ReaderExecuted(DbCommand command, DbCommandInterceptionContext<DbDataReader> interceptionContext)
{
LogIfError(command, interceptionContext);
}
public void ReaderExecuting(DbCommand command, DbCommandInterceptionContext<DbDataReader> interceptionContext)
{
LogIfError(command, interceptionContext);
}
public void ScalarExecuted(DbCommand command, DbCommandInterceptionContext<object> interceptionContext)
{
LogIfError(command, interceptionContext);
}
public void ScalarExecuting(DbCommand command, DbCommandInterceptionContext<object> interceptionContext)
{
LogIfError(command, interceptionContext);
}
private void LogIfError<TResult>(DbCommand command, DbCommandInterceptionContext<TResult> interceptionContext)
{
try
{
if (interceptionContext != null && interceptionContext.Exception != null)
{
bool isLogged = false;
try
{
LogInterpolatedEfQueryString(command, interceptionContext);
isLogged = true;
}
catch (Exception err)
{
LogRawEfQueryString(command, interceptionContext, err);
}
if (!isLogged)
LogRawEfQueryString(command, interceptionContext, null);
}
}
catch (Exception err)
{
Debug.WriteLine(err.Message);
}
}
/// <summary>
/// Logs the raw EF query string
/// </summary>
/// <typeparam name="TResult"></typeparam>
/// <param name="command"></param>
/// <param name="interceptionContext"></param>
/// <param name="err"></param>
private static void LogRawEfQueryString<TResult>(DbCommand command, DbCommandInterceptionContext<TResult> interceptionContext,
Exception err)
{
if (err != null)
Debug.WriteLine(err.Message);
string queryParameters = LogEfQueryParameters(command);
EventLogProvider.Log(
string.Format(
"Acme serverside DB operation failed: Exception: {0}. Parameters involved in EF query: {1}. SQL involved in EF Query: {2}",
Environment.NewLine + interceptionContext.Exception.Message,
Environment.NewLine + queryParameters,
Environment.NewLine + command.CommandText
), EventLogProviderEnum.Warning);
}
/// <summary>
/// Return a string with the list of EF query parameters
/// </summary>
/// <param name="command"></param>
/// <returns></returns>
private static string LogEfQueryParameters(DbCommand command)
{
var sb = new StringBuilder();
for (int i = 0; i < command.Parameters.Count; i++)
{
if (command.Parameters[i].Value != null)
sb.AppendLine(string.Format(@"Query param {0}: {1}", i, command.Parameters[i].Value));
}
return sb.ToString();
}
private static DbType[] QuoteRequiringTypes
{
get
{
return new[]
{
DbType.AnsiString, DbType.AnsiStringFixedLength, DbType.String,
DbType.StringFixedLength, DbType.Date, DbType.Date, DbType.DateTime,
DbType.DateTime2, DbType.Guid
};
}
}
private static void LogInterpolatedEfQueryString<TResult>(DbCommand command,
DbCommandInterceptionContext<TResult> interceptionContext)
{
var paramRegex = new Regex("@\\d+");
string interpolatedSqlString = paramRegex.Replace(command.CommandText,
m => GetInterpolatedString(command, m));
EventLogProvider.Log(string.Format(
"Acme serverside DB operation failed: Exception: {0}. SQL involved in EF Query: {1}",
Environment.NewLine + interceptionContext.Exception.Message,
Environment.NewLine + interpolatedSqlString),
EventLogProviderEnum.Warning);
}
private static string GetInterpolatedString(DbCommand command, Match m)
{
try
{
int matchIndex;
if (string.IsNullOrEmpty(m.Value))
return m.Value;
int.TryParse(m.Value.Replace("@", ""), out matchIndex);
//Entity framework will usually build parametrized queries with @1, @2 and so on ..
if (matchIndex < 0 || matchIndex >= command.Parameters.Count)
return m.Value;
//Ok matchIndex from here
DbParameter dbParameter = command.Parameters[matchIndex];
var dbParameterValue = dbParameter.Value;
if (dbParameterValue == null)
return m.Value;
try
{
return GetAdjustedDbParameterValue(dbParameter, dbParameterValue);
}
catch (Exception err)
{
Debug.WriteLine(err.Message);
}
}
catch (Exception err)
{
Debug.WriteLine(err.Message);
}
return m.Value;
}
/// <summary>
/// There are some cases where one have to adjust the Db Parametre value in case it is a boolean
/// </summary>
/// <param name="dbParameter"></param>
/// <param name="dbParameterValue"></param>
/// <returns></returns>
private static string GetAdjustedDbParameterValue(DbParameter dbParameter, object dbParameterValue)
{
if (QuoteRequiringTypes.Contains(dbParameter.DbType))
return string.Format("'{0}'", dbParameterValue); //Remember to put quotes on parameter value
if (dbParameter.DbType == DbType.Boolean)
{
bool dbParameterBitValue;
bool.TryParse(dbParameterValue.ToString(), out dbParameterBitValue);
return dbParameterBitValue ? "1" : "0"; //BIT
}
return dbParameterValue.ToString(); //Default case (not a quoted value and not a bit value)
}
}
}
The code above uses a class
EventLogProvider that will record to the Event Log of the system running the Entity Framework code in the data layer, usually the application server of your system.
Here is the relevant code for logging to the Eventlog:
using System.Diagnostics;
using System;
using Acme.Common.Security;
namespace Acme.Common.Log
{
public enum EventLogProviderEnum
{
Warning, Error
}
public static class EventLogProvider
{
private static string _applicationSource = "Acme";
private static string _applicationEventLogName = "Application";
public static void Log(Exception exception, EventLogProviderEnum logEvent)
{
Log(ErrorUtil.ConstructErrorMessage(exception), logEvent);
}
public static void Log(string logMessage, EventLogProviderEnum logEvent)
{
try
{
if (!EventLog.SourceExists(_applicationSource))
EventLog.CreateEventSource(_applicationSource, _applicationEventLogName);
EventLog.WriteEntry(_applicationSource, logMessage, GetEventLogEntryType(logEvent));
}
catch { } // If the event log is unavailable, don't crash.
}
private static EventLogEntryType GetEventLogEntryType(EventLogProviderEnum logEvent)
{
switch(logEvent)
{
case EventLogProviderEnum.Error:
return EventLogEntryType.Error;
case EventLogProviderEnum.Warning:
default:
return EventLogEntryType.Warning;
}
}
}
}
It is also necessary to add an instance of the db interception class in your ObjectContext or DbContext class as noted. Example:
try {
DbInterception.Add(new AcmeDbCommandInterceptor());
}
catch (Exception err){
//Log error here (consider using the EventLogProvider above for example)
}
The interpolated string will often be the one that is interesting when EF queries fail. Entity Framework (EF) uses stored procedures and parameters to prevent SQL injection attacks. To get a SQL
you can actually run, you will usually interpolate the EF query CommandText and look at the parameters, that is named as @0, @1, @2 and so on.. I use a Regex here to search after this.
Note that my code uses a lot of try-catch in case something goes wrong. You also do NOT want to run any heavy code here, as the DbInterception will run on ANY query. I only do further processing IF
an exception has occured, to avoid bogging down the system with performance drain. I also first try to get the interpolated EF query string that I can run in my Production or Test environment, usually
inside a BEGIN TRAN.. and ROLLBACK statement just to see why the database call failed. In addition, the code will try to log the Raw EF Query in form of logging the EF query CommandText and the command parameters,
but without the interpolation technique. I have also done some adjustment, by adding single quotes around strings and considering booleans as the value 0 or 1 (BIT). This code is new and there might be some additional adjustments here.
The bottom line to note here is that it is important to LOG the EF query SQL to INFER the real REASON why the SQL query FAILED, i.e. a quicker DIAGNOSE-INFER-FIX cycle leading to more success on your projects, if you use .NET and Entity Framework (version 6 or newer)!
Share this article on LinkedIn.
No comments:
Post a Comment