-
-
Save jinweijie/8d2ef2e30207712e806b to your computer and use it in GitHub Desktop.
EF6.x | Correlating SQL to code
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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, | |
}); | |
} | |
} | |
} | |
} |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment