Skip to content

Instantly share code, notes, and snippets.

@rowanmiller
Last active November 5, 2019 09:12
Show Gist options
  • Save rowanmiller/c117c118ceb5e882c53b to your computer and use it in GitHub Desktop.
Save rowanmiller/c117c118ceb5e882c53b to your computer and use it in GitHub Desktop.
EF6.x | Correlating SQL to code
using System;
using System.Collections.Generic;
using System.Data.Common;
using System.Data.Entity;
using System.Data.Entity.Infrastructure.Interception;
using System.Diagnostics;
using System.IO;
using System.Linq;
namespace Demo
{
class Program
{
static void Main(string[] args)
{
GetMsdnBlogs();
GetBlogs();
}
public static Blog[] GetMsdnBlogs()
{
using (var db = new BloggingContext())
{
return db.Blogs
.Where(b => b.Url.Contains("msdn"))
.OrderBy(b => b.Url)
.ToArray();
}
}
public static Blog[] GetBlogs()
{
using (var db = new BloggingContext())
{
return db.Blogs
.SqlQuery("SELECT * FROM dbo.wrong_table")
.ToArray();
}
}
}
public class BloggingContext : DbContext
{
public DbSet<Blog> Blogs { get; set; }
public DbSet<Post> Posts { get; set; }
}
public class Blog
{
public int BlogId { get; set; }
public string Url { get; set; }
public List<Post> Posts { get; set; }
}
public class Post
{
public int PostId { get; set; }
public string Title { get; set; }
public string Content { get; set; }
public int BlogId { get; set; }
public Blog Blog { get; set; }
}
public class MyConfig : DbConfiguration
{
public MyConfig()
{
this.AddInterceptor(new PoorSqlLogger(@"C:\temp\log.txt", 1));
}
}
public class PoorSqlLogger : DbCommandInterceptor
{
private readonly string _logFile;
private readonly int _executionMillisecondThreshold;
public PoorSqlLogger(string logFile, int executionMillisecondThreshold)
{
_logFile = logFile;
_executionMillisecondThreshold = executionMillisecondThreshold;
}
public override void ReaderExecuting(DbCommand command, DbCommandInterceptionContext<DbDataReader> interceptionContext)
{
Executing(interceptionContext);
base.ReaderExecuting(command, interceptionContext);
}
public override void ReaderExecuted(DbCommand command, DbCommandInterceptionContext<DbDataReader> interceptionContext)
{
Executed(command, interceptionContext);
base.ReaderExecuted(command, interceptionContext);
}
public override void NonQueryExecuting(DbCommand command, DbCommandInterceptionContext<int> interceptionContext)
{
Executing(interceptionContext);
base.NonQueryExecuting(command, interceptionContext);
}
public override void NonQueryExecuted(DbCommand command, DbCommandInterceptionContext<int> interceptionContext)
{
Executed(command, interceptionContext);
base.NonQueryExecuted(command, interceptionContext);
}
public override void ScalarExecuting(DbCommand command, DbCommandInterceptionContext<object> interceptionContext)
{
Executing(interceptionContext);
base.ScalarExecuting(command, interceptionContext);
}
public override void ScalarExecuted(DbCommand command, DbCommandInterceptionContext<object> interceptionContext)
{
Executed(command, interceptionContext);
base.ScalarExecuted(command, interceptionContext);
}
private void Executing<T>(DbCommandInterceptionContext<T> interceptionContext)
{
var timer = new Stopwatch();
interceptionContext.UserState = timer;
timer.Start();
}
private void Executed<T>(DbCommand command, DbCommandInterceptionContext<T> interceptionContext)
{
var timer = (Stopwatch)interceptionContext.UserState;
timer.Stop();
if (interceptionContext.Exception != null)
{
File.AppendAllLines(
_logFile,
new string[]
{
"FAILED COMMAND",
interceptionContext.Exception.Message,
command.CommandText,
Environment.StackTrace,
string.Empty,
string.Empty,
});
}
else if (timer.ElapsedMilliseconds >= _executionMillisecondThreshold)
{
File.AppendAllLines(
_logFile,
new string[]
{
$"SLOW COMMAND ({timer.ElapsedMilliseconds}ms)",
command.CommandText,
Environment.StackTrace,
string.Empty,
string.Empty,
});
}
}
}
}
@MagicAndre1981
Copy link

with EF6.2 I get a warning that interceptionContext.UserState is deprecated .

So I changed it to interceptionContext.SetUserState("timer", timer); and var timer = (Stopwatch)interceptionContext.FindUserState("timer");. Is this the correct "fix"?

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment