1.看似针对同样一段查询表ef达式,重复执行却没有被记录下来。其实这是正常情况，因为ef并没有重复去执行 相同sql查询。
2.MiniProfiler结合MVC过滤器进行 拦截记录Sql,示例代码：
using Mobile360.Core;
using Mobile360.Core.Interfaces;
using Mobile360.Core.Models;
using Mobile360.Web.Common;
using Newtonsoft.Json.Linq;
using StackExchange.Profiling;
using StackExchange.Profiling.Storage;
using System;
using System.Collections.Generic;
using System.Collections.Specialized;
using System.Configuration;
using System.Linq;
using System.Text;
using System.Web;
using System.Web.Helpers;
using System.Web.Mvc; namespace Mobile360.Web
{
[AttributeUsage(AttributeTargets.Method, AllowMultiple = true, Inherited = false)]
public class OperationHandlerAttribute : FilterAttribute,IActionFilter, IExceptionFilter
{
private IRepository repo; /// <summary>
/// 模块描述
/// </summary>
public string ModuleName { get; set; } /// <summary>
/// 方法名称
/// </summary>
public string ActionName { get; set; } /// <summary>
/// 方法描述
/// </summary>
public string ActionDescription { get; set; } /// <summary>
/// 控制器名称
/// </summary>
public string ControllerName { get; set; } /// <summary>
/// 方法参数
/// </summary>
public string ActionParameters { get; set; } /// <summary>
/// 访问时间
/// </summary>
public DateTime AccessDate { get; set; } /// <summary>
/// 操作备注
/// </summary>
public string OperationRemark { get; set; } /// <summary>
/// 是否记录入库
/// </summary>
public bool IsLog { get; set; } /// <summary>
/// 操作人id
/// </summary>
public int OperatorId { get; set; } /// <summary>
/// 操作人名
/// </summary>
public string OperatorName { get; set; } public OperationHandlerAttribute()
{
this.AccessDate = DateTime.Now;
this.IsLog = true;
this.repo = DependencyResolver.Current.GetService<IRepository>();
} /// <summary>
/// 操作日志记录
/// </summary>
/// <param name="option">操作动作描述</param>
/// <param name="remark">其他备注</param>
public OperationHandlerAttribute(string actionDescription , string remark = "")
{
this.AccessDate = DateTime.Now;
this.IsLog = true;
//this.ModuleName = moduleName;
this.OperationRemark = remark;
this.ActionDescription = actionDescription;
this.repo = DependencyResolver.Current.GetService<IRepository>();
}
void IActionFilter.OnActionExecuting(ActionExecutingContext filterContext)
{
if (this.IsLog)
{
MiniProfiler.Start(); this.OperatorName = filterContext.HttpContext.User.Identity.Name; this.ActionName = filterContext.ActionDescriptor.ActionName;
this.ControllerName = filterContext.ActionDescriptor.ControllerDescriptor.ControllerName;
IDictionary<string, object> dic = filterContext.ActionParameters;
var parameters = new System.Text.StringBuilder();
foreach (var item in dic)
{
parameters.Append(item.Key + "=" + Json.Encode(item.Value) + "|");
}
this.ActionParameters = parameters.ToString(); }
} void IActionFilter.OnActionExecuted(ActionExecutedContext context)
{
if (this.IsLog)
{
MiniProfiler.Stop();
string efSqlStr2Json = MiniProfiler.Current.Root.CustomTimingsJson; AuditLog log = new AuditLog();
log.AuditAccount = string.IsNullOrEmpty(this.OperatorName)?"(未登录用户)": this.OperatorName;
log.Action = this.ActionName;
log.ActionDescription = this.ActionDescription;
log.Controller = this.ControllerName;
log.Parameters = this.ActionParameters;
log.StartTime = this.AccessDate;
log.SqlQuery = efSqlStr2Json;
log.EndTime = DateTime.Now;
log.Result = true;
log.IP = IPHelper.GetRealIP(); repo.Insert<AuditLog>(log);
repo.SaveChanges();
}
} #region IExceptionFilter 成员
void IExceptionFilter.OnException(ExceptionContext context)
{
if (ConfigurationManager.AppSettings["IsDev"] == "true")
{
throw new Exception(context.Exception.Message, context.Exception);
} SystemLog slog = new SystemLog();
slog.Action = this.ActionName;
slog.Level = (int)SystemLogType.ERROR;
slog.LoginAccount = this.OperatorName;
slog.Message = BuildExceptionInfo(context);
slog.OccurTime = DateTime.Now; repo.Insert<SystemLog>(slog);
repo.SaveChanges(); JObject jsonResult = new JObject(); //返回的json数据
jsonResult.Add(new JProperty("Code", -));
jsonResult.Add(new JProperty("Msg", "系统发生异常，请查看内部日志"));
ContentResult cr = new ContentResult();
cr.Content = jsonResult.ToString();
cr.ContentType = "application/json";
context.Result = cr;
context.ExceptionHandled = true;
} private string BuildExceptionInfo(ExceptionContext context)
{
var sb = new StringBuilder();
var req = context.HttpContext.Request;
sb.AppendLine(String.Format("处理对“{0}”的“{1}”请求时发生了异常", req.RawUrl, req.HttpMethod));
sb.AppendLine("以下是参数的信息：");
this.AppendRequestLine(sb, req.QueryString);
this.AppendRequestLine(sb, req.Form);
sb.AppendLine("以下是异常的信息：");
sb.AppendLine(context.Exception.ToString());
//sb.AppendLine(context.Exception.StackTrace.ToString()); return sb.ToString();
} private void AppendRequestLine(StringBuilder sb, NameValueCollection coll)
{
for (int i = ; i < coll.Count; i++)
{
sb.AppendFormat("{0}: {1}", coll.Keys[i], coll[i]);
sb.AppendLine();
}
} #endregion }
}
3.以上的miniprofiler并不能 拦截到 sql语句查询，需要使用 minprofiler 封装的ado.net对象。
/// <summary>
/// 执行自定义SQL(创建、更新、删除操作)
/// </summary>
/// <typeparam name="TEntity"></typeparam>
/// <param name="commandText"></param>
/// <param name="parameters"></param>
/// <returns></returns>
public async Task<int> ExecuteSqlCommandAsync(string commandText, params object[] parameters)
{
var connection1 = this.Database.Connection;//使用EF的sql连接对象。统一管理。
if (connection1 != null)
{
DbCommand command = new SqlCommand();
ProfiledDbCommand prcommand = new ProfiledDbCommand(command, connection1, MiniProfiler.Current);
prcommand.CommandType = CommandType.Text;
prcommand.CommandText = commandText;
prcommand.Parameters.AddRange(parameters);
prcommand.Connection = connection1; if (connection1.State == ConnectionState.Closed)
connection1.Open(); return await prcommand.ExecuteNonQueryAsync();
}
return ;
}
ProfiledDbCommand，
ProfiledDbConnection等对象都是MiniProfiler的对象。这样才能抓到 Sql语句。 4.由于miniprofiler是用来性能调优的，用来做审计日志记录（包括哪个用户最终生成的sql查询）看似并不合适，非常耗性能。
所以，我们并没有准备去使用它来获取Sql语句。 运用在
Application_BeginRequest和
Application_EndRequest
期间用EF6.0版本以上才有的 拦截器接口
DbCommandInterceptor
拦截的所有sql语句作为一次请求的sql查询语句 来作为尝试，不知道这样有啥劣势不？ 希望有尝试过的 前辈 指点。