Profiler for Entity Framework
Greetings
I will continue a series of articles about programming. This time, I want to tell you about the free profiler I wrote for the Entity Framework (hereinafter EF) from version 4 to version 6, as well as some of the solutions I applied, such as decompiling .NET code on the fly, a form for exception handling and so on.
Nuget package for EF6: https://www.nuget.org/packages/EFlogger-For6/
Nuget package for EF4 - 5: https://www.nuget.org/packages/EFlogger-For4/
')
Details under the cut.
Introduction
When I started studying EF, I faced the question of profiling the queries generated by EF. After some searching, I found out with displeasure that EF does not provide standard tools, so I had to use, in my opinion, the extremely inconvenient program to use SQL Server Profiler or buy an expensive enough third-party Entity Framework Profiler 2.0 tool for $ 389.00,
With the advent of EF version 6, the situation with command logging has changed. A mechanism has appeared that allows you to add your interceptor:
// IDbCommandInterceptor DbInterception.Add(new LogFormatter()); public class LogFormatter : IDbCommandInterceptor { private readonly Stopwatch _stopwatch = new Stopwatch(); public void NonQueryExecuting(DbCommand command, DbCommandInterceptionContext<int> interceptionContext) { // _stopwatch.Restart(); } public void NonQueryExecuted(DbCommand command, DbCommandInterceptionContext<int> interceptionContext) { // _stopwatch.Stop(); // Log(command, interceptionContext); } public void ReaderExecuting(DbCommand command, DbCommandInterceptionContext<DbDataReader> interceptionContext) { _stopwatch.Restart(); } public void ReaderExecuted(DbCommand command, DbCommandInterceptionContext<DbDataReader> interceptionContext) { _stopwatch.Stop(); Log(command, interceptionContext); } public void ScalarExecuting(DbCommand command, DbCommandInterceptionContext<object> interceptionContext) { _stopwatch.Restart(); } public void ScalarExecuted(DbCommand command, DbCommandInterceptionContext<object> interceptionContext) { _stopwatch.Stop(); Log(command, interceptionContext); } private void Log<TResult>(DbCommand command, DbCommandInterceptionContext<TResult> interceptionContext) { Logger.Log(command, _stopwatch.ElapsedMilliseconds, interceptionContext.Exception); } } public static class Logger { public static void Log(DbCommand command, long elapsedMilliseconds, Exception exception) { // Trace Trace.WriteLine(string.Format("Command:{0}, Elapsed Milliseconds:{1}", command.CommandText, elapsedMilliseconds)); } } For the 4th version of EF, I did not find a similar logging mechanism.
My decision
The idea to write my own profiler, which repeats the functionality of a paid one, I have been carrying for a long time. The initial version was based on the EF 6th interceptor mechanism, but unfortunately in EF 4th, this mechanism does not work.
After a brief googling, a great library was found for EF profiling in ASP MVC applications called MiniProfiler . Based on it, it was decided to make your application.
What happened
The system was as simple as possible to use and integrate. You can pick up binaries with a project example on the wordpress-e site I created at http://ef-logger.com/
Nuget package for EF6: https://www.nuget.org/packages/EFlogger-For6/
The main application window:
Using
1. Pick up the archive with binaries and project example
2. Connect the EFlogger.EntityFramework4.dll library for EF 4-5 version and EFlogger.EntityFramework6.dll for EF 6th version
3. Initialize the profiler by running a static method.
// EF 6- EFloggerFor6.Initialize(); // EF 4- 6- EFloggerFor4.Initialize(); 4. Run the EFlogger.exe application.
API
An example for the library EFlogger.EntityFramework6.dl, in the case of using EFlogger.EntityFramework4.dl the static class will be called EFloggerFor4
// EFloggerFor6.Initialize(); // IP "127.0.0.1" EFloggerFor6.SetProfilerClientIP(“192.168.10.50″); // EFloggerFor6.WriteMessage(“Text message”); // ( ) EFloggerFor6.StartSendToClient(); // EFloggerFor6.StopSendToClient(); // EFlogger.log EFloggerFor6.StartSaveToLogFile(); // EFlogger.log EFloggerFor6.StopSaveToLogFile(); // , EFloggerFor6.ClearLog(); // EFloggerFor6.DisableDecompiling(); // Stack Trace-a EFloggerFor6.ExcludeAssembly(“MyAssemly”); How it all works
The implementation mechanism in EF version 4 is very different from version 6 EF.
There are no established DbProviderFactory substitution mechanisms in EF 4, so you have to replace the standard factory with your own one through reflection.
The process itself looks like this:
// DbProviderFactories.GetFactoryClasses(); // DbProviderFactories Type type = typeof(DbProviderFactories); DataTable table = null; // DataSet _configTable _providerTable object setOrTable = (type.GetField("_configTable", BindingFlags.NonPublic | BindingFlags.Static) ?? type.GetField("_providerTable", BindingFlags.NonPublic | BindingFlags.Static)).GetValue(null); var set = setOrTable as DataSet; if (set != null) table = set.Tables["DbProviderFactories"]; table = (DataTable)setOrTable; // DataTable foreach (DataRow row in table.Rows.Cast<DataRow>().ToList()) { DbProviderFactory factory; try { factory = DbProviderFactories.GetFactory(row); } catch (Exception) { continue; } var profType = EFProviderUtilities.ResolveFactoryType(factory.GetType()); if (profType != null) { DataRow profiled = table.NewRow(); profiled["Name"] = row["Name"]; profiled["Description"] = row["Description"]; profiled["InvariantName"] = row["InvariantName"]; profiled["AssemblyQualifiedName"] = profType.AssemblyQualifiedName; table.Rows.Remove(row); table.Rows.Add(profiled); } } To integrate into EF 6th version, you can use the regular service substitution mechanism:
DbConfiguration.Loaded += (_, a) => a.ReplaceService<DbProviderServices>((s, k) => new EFProfiledDbProviderServices()); Decompilation
EFlogger on the fly decompiles the code and displays the body of the method that generated this or that request. Libraries from the free ILSpy reflector are used for this. To decompose a method, you must first find it by going up the Stack Trace and eliminate unnecessary calls to third-party libraries:
public static string Get(out StackFrame outStackFrame) { outStackFrame = null; var frames = new StackTrace(0, true).GetFrames(); if (frames == null) { return ""; } var methods = new List<string>(); // foreach (StackFrame t in frames) { // var method = t.GetMethod(); // var assembly = method.Module.Assembly.GetName().Name; if (ShouldExcludeType(method) || AssembliesToExclude.Contains(assembly) || MethodsToExclude.Contains(method.Name)) continue; // , if (outStackFrame == null) { outStackFrame = t; } methods.Add(method.DeclaringType.FullName + ":" + method.Name); } return string.Join("\r\n", methods); } private static string GetMethodBody(StackFrame stackFrame) { MethodBase methodBase = stackFrame.GetMethod(); return Decompiler.GetSourceCode(methodBase.Module.FullyQualifiedName, methodBase.DeclaringType.Name, methodBase.Name); } Decompiler
using System; using System.IO; using System.Linq; using ICSharpCode.Decompiler; using ICSharpCode.Decompiler.Ast; using ICSharpCode.Decompiler.Ast.Transforms; using Mono.Cecil; namespace Common { public static class Decompiler { public static string GetSourceCode(string pathToAssembly, string className, string methodName) { try { var assemblyDefinition = AssemblyDefinition.ReadAssembly(pathToAssembly); TypeDefinition assembleDefenition = assemblyDefinition.MainModule.Types.First(type => type.Name == className); MethodDefinition methodDefinition = assembleDefenition.Methods.First(method => method.Name == methodName); var output = new StringWriter(); var plainTextOutput = new PlainTextOutput(output); DecompileMethod(methodDefinition, plainTextOutput); return output.ToString(); } catch (Exception exception) { return string.Format( "Exception in decompling. \r\n Message:{0}, \r\n Inner Exception:{1}, \r\n StackTrace:{2}",exception.Message, exception.InnerException, exception.StackTrace); } } private static void DecompileMethod(MethodDefinition method, ITextOutput output) { AstBuilder codeDomBuilder = CreateAstBuilder(currentType: method.DeclaringType, isSingleMember: true); if (method.IsConstructor && !method.IsStatic && !method.DeclaringType.IsValueType) { AddFieldsAndCtors(codeDomBuilder, method.DeclaringType, method.IsStatic); RunTransformsAndGenerateCode(codeDomBuilder, output); } else { codeDomBuilder.AddMethod(method); RunTransformsAndGenerateCode(codeDomBuilder, output); } } private static AstBuilder CreateAstBuilder(ModuleDefinition currentModule = null, TypeDefinition currentType = null, bool isSingleMember = false) { if (currentModule == null) currentModule = currentType.Module; var settings = new DecompilerSettings(); if (isSingleMember) { settings = settings.Clone(); settings.UsingDeclarations = false; } return new AstBuilder( new DecompilerContext(currentModule) { CurrentType = currentType, Settings = settings }); } private static void AddFieldsAndCtors(AstBuilder codeDomBuilder, TypeDefinition declaringType, bool isStatic) { foreach (var field in declaringType.Fields) { if (field.IsStatic == isStatic) codeDomBuilder.AddField(field); } foreach (var ctor in declaringType.Methods) { if (ctor.IsConstructor && ctor.IsStatic == isStatic) codeDomBuilder.AddMethod(ctor); } } private static void RunTransformsAndGenerateCode(AstBuilder astBuilder, ITextOutput output, IAstTransform additionalTransform = null) { astBuilder.GenerateCode(output); } } } An example of a working draft decompilation on Yandex files: yadi.sk/d/AzBGet5-Nwns2
Exception Handling Window
It's no secret that applications periodically fall, this can happen for a variety of reasons and the developer’s task is to show some sane window for an error message.
My form option looks like this:
The form prompts the user to add information about the error, steps to reproduce, and your email to respond.
When you click on the Send button, the developer receives an email with all the data.
[STAThread] [STAThread] static void Main() { SetupLogger(); // if (!Debugger.IsAttached) { // Application.ThreadException += (sender, e) => HandleError(e.Exception); AppDomain.CurrentDomain.UnhandledException += (sender, e) => HandleError((Exception)e.ExceptionObject); } Application.EnableVisualStyles(); Application.SetCompatibleTextRenderingDefault(false); Application.Run(new MainForm()); } private static void HandleError(Exception exception) { try { // try/catch new ErrorHandlerController(exception).Run(); } catch (Exception e) { MessageBox.Show("Error processing exception. Please send log file EFlogger.log to developer: " + Settings.Default.ProgrammerEmail + " \r\n Exception:" + e); // Logger.Error(e); if (MessageBox.Show("Attach debugger? \n Only for developer!!!", "Debugging...", MessageBoxButtons.YesNo, MessageBoxIcon.Question) == DialogResult.Yes) { Debugger.Launch(); throw; } } finally { // Windows Environment.Exit(1); } } //new ErrorHandlerController(exception).Run(); public void Run() { // string exceptionInfoText = string.Format( "An unexpected error occurred: {0}" + Environment.NewLine + "Time: {1} " + Environment.NewLine + "{2}" + Environment.NewLine + "InnerException: \n {3}" + Environment.NewLine + "InnerException StackTrace: \n {4}" + Environment.NewLine, _exception.Message, DateTime.Now, _exception, _exception.InnerException, _exception.InnerException != null ? _exception.InnerException.StackTrace : string.Empty ); // Program.Logger.Error(exceptionInfoText); _view.ExceptionInfoText = exceptionInfoText; // , ShowDialog, _view.ShowDialog(); } An example of a working draft with an error handling form can be collected on Yandex files: yadi.sk/d/7y4i_cz7NwtE3
Networking
You can read the details of the network interaction between the connected library and the profiler from my previous article: Networking via TCP in C #
A bit of humor
During the testing of a tool on a friend’s
Request
Command text:
SELECT
[Project238]. [C4] AS [C1],
[Project238]. [Name] AS [Name],
[Project238]. [Name1] AS [Name1],
[Project238]. [C2] AS [C2],
[Project238]. [C3] AS [C3],
[Project238]. [C1] AS [C4]
FROM (SELECT
[GroupBy1]. [A1] AS [C1],
[GroupBy1]. [K1] AS [Name],
[GroupBy1]. [K2] AS [Name1],
[GroupBy1]. [K3] AS [C2],
[GroupBy1]. [K4] AS [C3],
1 AS [C4]
FROM (SELECT
[Project237]. [Name] AS [K1],
[Project237]. [Name1] AS [K2],
[Project237]. [C1] AS [K3],
[Project237]. [C2] AS [K4],
COUNT (1) AS [A1]
FROM (SELECT
[Extent2]. [Name] AS [Name],
[Extent3]. [Name] AS [Name1],
DATEPART (year, [Join3]. [C11]) AS [C1],
DATEPART (month, [Join3]. [C12]) AS [C2]
FROM [OccupationHistory] AS [Extent1]
INNER JOIN [Division] AS [Extent2] ON [Extent1]. [DivisionID] = [Extent2]. [Id]
INNER JOIN [Position] AS [Extent3] ON [Extent1]. [PositionID] = [Extent3]. [Id]
CROSS JOIN (SELECT [UnionAll59]. [C1] AS [C11], [UnionAll118]. [C1] AS [C12]
FROM (SELECT
[UnionAll58]. [C1] AS [C1]
FROM (SELECT
[UnionAll57]. [C1] AS [C1]
FROM (SELECT
[UnionAll56]. [C1] AS [C1]
FROM (SELECT
[UnionAll55]. [C1] AS [C1]
FROM (SELECT
[UnionAll54]. [C1] AS [C1]
FROM (SELECT
[UnionAll53]. [C1] AS [C1]
FROM (SELECT
[UnionAll52]. [C1] AS [C1]
FROM (SELECT
[UnionAll51]. [C1] AS [C1]
FROM (SELECT
[UnionAll50]. [C1] AS [C1]
FROM (SELECT
[UnionAll49]. [C1] AS [C1]
FROM (SELECT
[UnionAll48]. [C1] AS [C1]
FROM (SELECT
[UnionAll47]. [C1] AS [C1]
FROM (SELECT
[UnionAll46]. [C1] AS [C1]
FROM (SELECT
[UnionAll45]. [C1] AS [C1]
FROM (SELECT
[UnionAll44]. [C1] AS [C1]
FROM (SELECT
[UnionAll43]. [C1] AS [C1]
FROM (SELECT
[UnionAll42]. [C1] AS [C1]
FROM (SELECT
[UnionAll41]. [C1] AS [C1]
FROM (SELECT
[UnionAll40]. [C1] AS [C1]
FROM (SELECT
[UnionAll39]. [C1] AS [C1]
FROM (SELECT
[UnionAll38]. [C1] AS [C1]
FROM (SELECT
[UnionAll37]. [C1] AS [C1]
FROM (SELECT
[UnionAll36]. [C1] AS [C1]
FROM (SELECT
[UnionAll35]. [C1] AS [C1]
FROM (SELECT
[UnionAll34]. [C1] AS [C1]
FROM (SELECT
[UnionAll33]. [C1] AS [C1]
FROM (SELECT
[UnionAll32]. [C1] AS [C1]
FROM (SELECT
[UnionAll31]. [C1] AS [C1]
FROM (SELECT
[UnionAll30]. [C1] AS [C1]
FROM (SELECT
[UnionAll29]. [C1] AS [C1]
FROM (SELECT
[UnionAll28]. [C1] AS [C1]
FROM (SELECT
[UnionAll27]. [C1] AS [C1]
FROM (SELECT
[UnionAll26]. [C1] AS [C1]
FROM (SELECT
[UnionAll25]. [C1] AS [C1]
FROM (SELECT
[UnionAll24]. [C1] AS [C1]
FROM (SELECT
[UnionAll23]. [C1] AS [C1]
FROM (SELECT
[UnionAll22]. [C1] AS [C1]
FROM (SELECT
[UnionAll21]. [C1] AS [C1]
FROM (SELECT
[UnionAll20]. [C1] AS [C1]
FROM (SELECT
[UnionAll19]. [C1] AS [C1]
FROM (SELECT
[UnionAll18]. [C1] AS [C1]
FROM (SELECT
[UnionAll17]. [C1] AS [C1]
FROM (SELECT
[UnionAll16]. [C1] AS [C1]
FROM (SELECT
[UnionAll15]. [C1] AS [C1]
FROM (SELECT
[UnionAll14]. [C1] AS [C1]
FROM (SELECT
[UnionAll13]. [C1] AS [C1]
FROM (SELECT
[UnionAll12]. [C1] AS [C1]
FROM (SELECT
[UnionAll11]. [C1] AS [C1]
FROM (SELECT
[UnionAll10]. [C1] AS [C1]
FROM (SELECT
[UnionAll9]. [C1] AS [C1]
FROM (SELECT
[UnionAll8]. [C1] AS [C1]
FROM (SELECT
[UnionAll7]. [C1] AS [C1]
FROM (SELECT
[UnionAll6]. [C1] AS [C1]
FROM (SELECT
[UnionAll5]. [C1] AS [C1]
FROM (SELECT
[UnionAll4]. [C1] AS [C1]
FROM (SELECT
[UnionAll3]. [C1] AS [C1]
FROM (SELECT
[UnionAll2]. [C1] AS [C1]
FROM (SELECT
[UnionAll1]. [C1] AS [C1]
FROM (SELECT
convert (datetime, '2001-01-01 00: 00: 00.000', 121) AS [C1]
FROM (SELECT 1 AS X) AS [SingleRowTable1]
UNION ALL
SELECT
convert (datetime, '2001-02-01 00: 00: 00.000', 121) AS [C1]
FROM (SELECT 1 AS X) AS [SingleRowTable2]) AS [UnionAll1]
UNION ALL
SELECT
convert (datetime, '2001-03-01 00: 00: 00.000', 121) AS [C1]
FROM (SELECT 1 AS X) AS [SingleRowTable3]) AS [UnionAll2]
UNION ALL
SELECT
convert (datetime, '2001-04-01 00: 00: 00.000', 121) AS [C1]
FROM (SELECT 1 AS X) AS [SingleRowTable4]) AS [UnionAll3]
UNION ALL
SELECT
convert (datetime, '2001-05-01 00: 00: 00.000', 121) AS [C1]
FROM (SELECT 1 AS X) AS [SingleRowTable5]) AS [UnionAll4]
UNION ALL
SELECT
convert (datetime, '2001-06-01 00: 00: 00.000', 121) AS [C1]
FROM (SELECT 1 AS X) AS [SingleRowTable6]) AS [UnionAll5]
UNION ALL
SELECT
convert (datetime, '2001-07-01 00: 00: 00.000', 121) AS [C1]
FROM (SELECT 1 AS X) AS [SingleRowTable7]) AS [UnionAll6]
UNION ALL
SELECT
convert (datetime, '2001-08-01 00: 00: 00.000', 121) AS [C1]
FROM (SELECT 1 AS X) AS [SingleRowTable8]) AS [UnionAll7]
UNION ALL
SELECT
convert (datetime, '2001-09-01 00: 00: 00.000', 121) AS [C1]
FROM (SELECT 1 AS X) AS [SingleRowTable9]) AS [UnionAll8]
UNION ALL
SELECT
convert (datetime, '2001-10-01 00: 00: 00.000', 121) AS [C1]
FROM (SELECT 1 AS X) AS [SingleRowTable10]) AS [UnionAll9]
UNION ALL
SELECT
convert (datetime, '2001-11-01 00: 00: 00.000', 121) AS [C1]
FROM (SELECT 1 AS X) AS [SingleRowTable11]) AS [UnionAll10]
UNION ALL
SELECT
convert (datetime, '2001-12-01 00: 00: 00.000', 121) AS [C1]
FROM (SELECT 1 AS X) AS [SingleRowTable12]) AS [UnionAll11]
UNION ALL
SELECT
convert (datetime, '2002-01-01 00: 00: 00.000', 121) AS [C1]
FROM (SELECT 1 AS X) AS [SingleRowTable13]) AS [UnionAll12]
UNION ALL
SELECT
convert (datetime, '2002-02-01 00: 00: 00.000', 121) AS [C1]
FROM (SELECT 1 AS X) AS [SingleRowTable14]) AS [UnionAll13]
UNION ALL
SELECT
convert (datetime, '2002-03-01 00: 00: 00.000', 121) AS [C1]
FROM (SELECT 1 AS X) AS [SingleRowTable15]) AS [UnionAll14]
UNION ALL
SELECT
convert (datetime, '2002-04-01 00: 00: 00.000', 121) AS [C1]
FROM (SELECT 1 AS X) AS [SingleRowTable16]) AS [UnionAll15]
UNION ALL
SELECT
convert (datetime, '2002-05-01 00: 00: 00.000', 121) AS [C1]
FROM (SELECT 1 AS X) AS [SingleRowTable17]) AS [UnionAll16]
UNION ALL
SELECT
convert (datetime, '2002-06-01 00: 00: 00.000', 121) AS [C1]
FROM (SELECT 1 AS X) AS [SingleRowTable18]) AS [UnionAll17]
UNION ALL
SELECT
convert (datetime, '2002-07-01 00: 00: 00.000', 121) AS [C1]
FROM (SELECT 1 AS X) AS [SingleRowTable19]) AS [UnionAll18]
UNION ALL
SELECT
convert (datetime, '2002-08-01 00: 00: 00.000', 121) AS [C1]
FROM (SELECT 1 AS X) AS [SingleRowTable20]) AS [UnionAll19]
UNION ALL
SELECT
convert (datetime, '2002-09-01 00: 00: 00.000', 121) AS [C1]
FROM (SELECT 1 AS X) AS [SingleRowTable21]) AS [UnionAll20]
UNION ALL
SELECT
convert (datetime, '2002-10-01 00: 00: 00.000', 121) AS [C1]
FROM (SELECT 1 AS X) AS [SingleRowTable22]) AS [UnionAll21]
UNION ALL
SELECT
convert (datetime, '2002-11-01 00: 00: 00.000', 121) AS [C1]
FROM (SELECT 1 AS X) AS [SingleRowTable23]) AS [UnionAll22]
UNION ALL
SELECT
convert (datetime, '2002-12-01 00: 00: 00.000', 121) AS [C1]
FROM (SELECT 1 AS X) AS [SingleRowTable24]) AS [UnionAll23]
UNION ALL
SELECT
convert (datetime, '2003-01-01 00: 00: 00.000', 121) AS [C1]
FROM (SELECT 1 AS X) AS [SingleRowTable25]) AS [UnionAll24]
UNION ALL
SELECT
convert (datetime, '2003-02-01 00: 00: 00.000', 121) AS [C1]
FROM (SELECT 1 AS X) AS [SingleRowTable26]) AS [UnionAll25]
UNION ALL
SELECT
convert (datetime, '2003-03-01 00: 00: 00.000', 121) AS [C1]
FROM (SELECT 1 AS X) AS [SingleRowTable27]) AS [UnionAll26]
UNION ALL
SELECT
convert (datetime, '2003-04-01 00: 00: 00.000', 121) AS [C1]
FROM (SELECT 1 AS X) AS [SingleRowTable28]) AS [UnionAll27]
UNION ALL
SELECT
convert (datetime, '2003-05-01 00: 00: 00.000', 121) AS [C1]
FROM (SELECT 1 AS X) AS [SingleRowTable29]) AS [UnionAll28]
UNION ALL
SELECT
convert (datetime, '2003-06-01 00: 00: 00.000', 121) AS [C1]
FROM (SELECT 1 AS X) AS [SingleRowTable30]) AS [UnionAll29]
UNION ALL
SELECT
convert (datetime, '2003-07-01 00: 00: 00.000', 121) AS [C1]
FROM (SELECT 1 AS X) AS [SingleRowTable31]) AS [UnionAll30]
UNION ALL
SELECT
convert (datetime, '2003-08-01 00: 00: 00.000', 121) AS [C1]
FROM (SELECT 1 AS X) AS [SingleRowTable32]) AS [UnionAll31]
UNION ALL
SELECT
convert (datetime, '2003-09-01 00: 00: 00.000', 121) AS [C1]
FROM (SELECT 1 AS X) AS [SingleRowTable33]) AS [UnionAll32]
UNION ALL
SELECT
convert (datetime, '2003-10-01 00: 00: 00.000', 121) AS [C1]
FROM (SELECT 1 AS X) AS [SingleRowTable34]) AS [UnionAll33]
UNION ALL
SELECT
convert (datetime, '2003-11-01 00: 00: 00.000', 121) AS [C1]
FROM (SELECT 1 AS X) AS [SingleRowTable35]) AS [UnionAll34]
UNION ALL
SELECT
convert (datetime, '2003-12-01 00: 00: 00.000', 121) AS [C1]
FROM (SELECT 1 AS X) AS [SingleRowTable36]) AS [UnionAll35]
UNION ALL
SELECT
convert (datetime, '2004-01-01 00: 00: 00.000', 121) AS [C1]
FROM (SELECT 1 AS X) AS [SingleRowTable37]) AS [UnionAll36]
UNION ALL
SELECT
convert (datetime, '2004-02-01 00: 00: 00.000', 121) AS [C1]
FROM (SELECT 1 AS X) AS [SingleRowTable38]) AS [UnionAll37]
UNION ALL
SELECT
convert (datetime, '2004-03-01 00: 00: 00.000', 121) AS [C1]
FROM (SELECT 1 AS X) AS [SingleRowTable39]) AS [UnionAll38]
UNION ALL
SELECT
convert (datetime, '2004-04-01 00: 00: 00.000', 121) AS [C1]
FROM (SELECT 1 AS X) AS [SingleRowTable40]) AS [UnionAll39]
UNION ALL
SELECT
convert (datetime, '2004-05-01 00: 00: 00.000', 121) AS [C1]
FROM (SELECT 1 AS X) AS [SingleRowTable41]) AS [UnionAll40]
UNION ALL
SELECT
convert (datetime, '2004-06-01 00: 00: 00.000', 121) AS [C1]
FROM (SELECT 1 AS X) AS [SingleRowTable42]) AS [UnionAll41]
UNION ALL
SELECT
convert (datetime, '2004-07-01 00: 00: 00.000', 121) AS [C1]
FROM (SELECT 1 AS X) AS [SingleRowTable43]) AS [UnionAll42]
UNION ALL
SELECT
convert (datetime, '2004-08-01 00: 00: 00.000', 121) AS [C1]
FROM (SELECT 1 AS X) AS [SingleRowTable44]) AS [UnionAll43]
UNION ALL
SELECT
convert (datetime, '2004-09-01 00: 00: 00.000', 121) AS [C1]
FROM (SELECT 1 AS X) AS [SingleRowTable45]) AS [UnionAll44]
UNION ALL
SELECT
convert (datetime, '2004-10-01 00: 00: 00.000', 121) AS [C1]
FROM (SELECT 1 AS X) AS [SingleRowTable46]) AS [UnionAll45]
UNION ALL
SELECT
convert (datetime, '2004-11-01 00: 00: 00.000', 121) AS [C1]
FROM (SELECT 1 AS X) AS [SingleRowTable47]) AS [UnionAll46]
UNION ALL
SELECT
convert (datetime, '2004-12-01 00: 00: 00.000', 121) AS [C1]
FROM (SELECT 1 AS X) AS [SingleRowTable48]) AS [UnionAll47]
UNION ALL
SELECT
convert (datetime, '2005-01-01 00: 00: 00.000', 121) AS [C1]
FROM (SELECT 1 AS X) AS [SingleRowTable49]) AS [UnionAll48]
UNION ALL
SELECT
convert (datetime, '2005-02-01 00: 00: 00.000', 121) AS [C1]
FROM (SELECT 1 AS X) AS [SingleRowTable50]) AS [UnionAll49]
UNION ALL
SELECT
convert (datetime, '2005-03-01 00: 00: 00.000', 121) AS [C1]
FROM (SELECT 1 AS X) AS [SingleRowTable51]) AS [UnionAll50]
UNION ALL
SELECT
convert (datetime, '2005-04-01 00: 00: 00.000', 121) AS [C1]
FROM (SELECT 1 AS X) AS [SingleRowTable52]) AS [UnionAll51]
UNION ALL
SELECT
convert (datetime, '2005-05-01 00: 00: 00.000', 121) AS [C1]
FROM (SELECT 1 AS X) AS [SingleRowTable53]) AS [UnionAll52]
UNION ALL
SELECT
convert (datetime, '2005-06-01 00: 00: 00.000', 121) AS [C1]
FROM (SELECT 1 AS X) AS [SingleRowTable54]) AS [UnionAll53]
UNION ALL
SELECT
convert (datetime, '2005-07-01 00: 00: 00.000', 121) AS [C1]
FROM (SELECT 1 AS X) AS [SingleRowTable55]) AS [UnionAll54]
UNION ALL
SELECT
convert (datetime, '2005-08-01 00: 00: 00.000', 121) AS [C1]
FROM (SELECT 1 AS X) AS [SingleRowTable56]) AS [UnionAll55]
UNION ALL
SELECT
convert (datetime, '2005-09-01 00: 00: 00.000', 121) AS [C1]
FROM (SELECT 1 AS X) AS [SingleRowTable57]) AS [UnionAll56]
UNION ALL
SELECT
convert (datetime, '2005-10-01 00: 00: 00.000', 121) AS [C1]
FROM (SELECT 1 AS X) AS [SingleRowTable58]) AS [UnionAll57]
UNION ALL
SELECT
convert (datetime, '2005-11-01 00: 00: 00.000', 121) AS [C1]
FROM (SELECT 1 AS X) AS [SingleRowTable59]) AS [UnionAll58]
UNION ALL
SELECT
convert (datetime, '2005-12-01 00: 00: 00.000', 121) AS [C1]
FROM (SELECT 1 AS X) AS [SingleRowTable60]) AS [UnionAll59]
INNER JOIN (SELECT
[UnionAll117]. [C1] AS [C1]
FROM (SELECT
[UnionAll116]. [C1] AS [C1]
FROM (SELECT
[UnionAll115]. [C1] AS [C1]
FROM (SELECT
[UnionAll114]. [C1] AS [C1]
FROM (SELECT
[UnionAll113]. [C1] AS [C1]
FROM (SELECT
[UnionAll112]. [C1] AS [C1]
FROM (SELECT
[UnionAll111]. [C1] AS [C1]
FROM (SELECT
[UnionAll110]. [C1] AS [C1]
FROM (SELECT
[UnionAll109]. [C1] AS [C1]
FROM (SELECT
[UnionAll108]. [C1] AS [C1]
FROM (SELECT
[UnionAll107]. [C1] AS [C1]
FROM (SELECT
[UnionAll106]. [C1] AS [C1]
FROM (SELECT
[UnionAll105]. [C1] AS [C1]
FROM (SELECT
[UnionAll104]. [C1] AS [C1]
FROM (SELECT
[UnionAll103]. [C1] AS [C1]
FROM (SELECT
[UnionAll102]. [C1] AS [C1]
FROM (SELECT
[UnionAll101]. [C1] AS [C1]
FROM (SELECT
[UnionAll100]. [C1] AS [C1]
FROM (SELECT
[UnionAll99]. [C1] AS [C1]
FROM (SELECT
[UnionAll98]. [C1] AS [C1]
FROM (SELECT
[UnionAll97]. [C1] AS [C1]
FROM (SELECT
[UnionAll96]. [C1] AS [C1]
FROM (SELECT
[UnionAll95]. [C1] AS [C1]
FROM (SELECT
[UnionAll94]. [C1] AS [C1]
FROM (SELECT
[UnionAll93]. [C1] AS [C1]
FROM (SELECT
[UnionAll92]. [C1] AS [C1]
FROM (SELECT
[UnionAll91]. [C1] AS [C1]
FROM (SELECT
[UnionAll90]. [C1] AS [C1]
FROM (SELECT
[UnionAll89]. [C1] AS [C1]
FROM (SELECT
[UnionAll88]. [C1] AS [C1]
FROM (SELECT
[UnionAll87]. [C1] AS [C1]
FROM (SELECT
[UnionAll86]. [C1] AS [C1]
FROM (SELECT
[UnionAll85]. [C1] AS [C1]
FROM (SELECT
[UnionAll84]. [C1] AS [C1]
FROM (SELECT
[UnionAll83]. [C1] AS [C1]
FROM (SELECT
[UnionAll82]. [C1] AS [C1]
FROM (SELECT
[UnionAll81]. [C1] AS [C1]
FROM (SELECT
[UnionAll80]. [C1] AS [C1]
FROM (SELECT
[UnionAll79]. [C1] AS [C1]
FROM (SELECT
[UnionAll78]. [C1] AS [C1]
FROM (SELECT
[UnionAll77]. [C1] AS [C1]
FROM (SELECT
[UnionAll76]. [C1] AS [C1]
FROM (SELECT
[UnionAll75]. [C1] AS [C1]
FROM (SELECT
[UnionAll74]. [C1] AS [C1]
FROM (SELECT
[UnionAll73]. [C1] AS [C1]
FROM (SELECT
[UnionAll72]. [C1] AS [C1]
FROM (SELECT
[UnionAll71]. [C1] AS [C1]
FROM (SELECT
[UnionAll70]. [C1] AS [C1]
FROM (SELECT
[UnionAll69]. [C1] AS [C1]
FROM (SELECT
[UnionAll68]. [C1] AS [C1]
FROM (SELECT
[UnionAll67]. [C1] AS [C1]
FROM (SELECT
[UnionAll66]. [C1] AS [C1]
FROM (SELECT
[UnionAll65]. [C1] AS [C1]
FROM (SELECT
[UnionAll64]. [C1] AS [C1]
FROM (SELECT
[UnionAll63]. [C1] AS [C1]
FROM (SELECT
[UnionAll62]. [C1] AS [C1]
FROM (SELECT
[UnionAll61]. [C1] AS [C1]
FROM (SELECT
[UnionAll60]. [C1] AS [C1]
FROM (SELECT
convert (datetime, '2001-01-31 00: 00: 00.000', 121) AS [C1]
FROM (SELECT 1 AS X) AS [SingleRowTable61]
UNION ALL
SELECT
convert (datetime, '2001-02-28 00: 00: 00.000', 121) AS [C1]
FROM (SELECT 1 AS X) AS [SingleRowTable62]) AS [UnionAll60]
UNION ALL
SELECT
convert (datetime, '2001-03-31 00: 00: 00.000', 121) AS [C1]
FROM (SELECT 1 AS X) AS [SingleRowTable63]) AS [UnionAll61]
UNION ALL
SELECT
convert (datetime, '2001-04-30 00: 00: 00.000', 121) AS [C1]
FROM (SELECT 1 AS X) AS [SingleRowTable64]) AS [UnionAll62]
UNION ALL
SELECT
convert (datetime, '2001-05-31 00: 00: 00.000', 121) AS [C1]
FROM (SELECT 1 AS X) AS [SingleRowTable65]) AS [UnionAll63]
UNION ALL
SELECT
convert (datetime, '2001-06-30 00: 00: 00.000', 121) AS [C1]
FROM (SELECT 1 AS X) AS [SingleRowTable66]) AS [UnionAll64]
UNION ALL
SELECT
convert (datetime, '2001-07-31 00: 00: 00.000', 121) AS [C1]
FROM (SELECT 1 AS X) AS [SingleRowTable67]) AS [UnionAll65]
UNION ALL
SELECT
convert (datetime, '2001-08-31 00: 00: 00.000', 121) AS [C1]
FROM (SELECT 1 AS X) AS [SingleRowTable68]) AS [UnionAll66]
UNION ALL
SELECT
convert (datetime, '2001-09-30 00: 00: 00.000', 121) AS [C1]
FROM (SELECT 1 AS X) AS [SingleRowTable69]) AS [UnionAll67]
UNION ALL
SELECT
convert (datetime, '2001-10-31 00: 00: 00.000', 121) AS [C1]
FROM (SELECT 1 AS X) AS [SingleRowTable70]) AS [UnionAll68]
UNION ALL
SELECT
convert (datetime, '2001-11-30 00: 00: 00.000', 121) AS [C1]
FROM (SELECT 1 AS X) AS [SingleRowTable71]) AS [UnionAll69]
UNION ALL
SELECT
convert (datetime, '2001-12-31 00: 00: 00.000', 121) AS [C1]
FROM (SELECT 1 AS X) AS [SingleRowTable72]) AS [UnionAll70]
UNION ALL
SELECT
convert (datetime, '2002-01-31 00: 00: 00.000', 121) AS [C1]
FROM (SELECT 1 AS X) AS [SingleRowTable73]) AS [UnionAll71]
UNION ALL
SELECT
convert (datetime, '2002-02-28 00: 00: 00.000', 121) AS [C1]
FROM (SELECT 1 AS X) AS [SingleRowTable74]) AS [UnionAll72]
UNION ALL
SELECT
convert (datetime, '2002-03-31 00: 00: 00.000', 121) AS [C1]
FROM (SELECT 1 AS X) AS [SingleRowTable75]) AS [UnionAll73]
UNION ALL
SELECT
convert (datetime, '2002-04-30 00: 00: 00.000', 121) AS [C1]
FROM (SELECT 1 AS X) AS [SingleRowTable76]) AS [UnionAll74]
UNION ALL
SELECT
convert (datetime, '2002-05-31 00: 00: 00.000', 121) AS [C1]
FROM (SELECT 1 AS X) AS [SingleRowTable77]) AS [UnionAll75]
UNION ALL
SELECT
convert (datetime, '2002-06-30 00: 00: 00.000', 121) AS [C1]
FROM (SELECT 1 AS X) AS [SingleRowTable78]) AS [UnionAll76]
UNION ALL
SELECT
convert (datetime, '2002-07-31 00: 00: 00.000', 121) AS [C1]
FROM (SELECT 1 AS X) AS [SingleRowTable79]) AS [UnionAll77]
UNION ALL
SELECT
convert (datetime, '2002-08-31 00: 00: 00.000', 121) AS [C1]
FROM (SELECT 1 AS X) AS [SingleRowTable80]) AS [UnionAll78]
UNION ALL
SELECT
convert (datetime, '2002-09-30 00: 00: 00.000', 121) AS [C1]
FROM (SELECT 1 AS X) AS [SingleRowTable81]) AS [UnionAll79]
UNION ALL
SELECT
convert (datetime, '2002-10-31 00: 00: 00.000', 121) AS [C1]
FROM (SELECT 1 AS X) AS [SingleRowTable82]) AS [UnionAll80]
UNION ALL
SELECT
convert (datetime, '2002-11-30 00: 00: 00.000', 121) AS [C1]
FROM ( SELECT 1 AS X ) AS [SingleRowTable83]) AS [UnionAll81]
UNION ALL
SELECT
convert(datetime, '2002-12-31 00:00:00.000', 121) AS [C1]
FROM ( SELECT 1 AS X ) AS [SingleRowTable84]) AS [UnionAll82]
UNION ALL
SELECT
convert(datetime, '2003-01-31 00:00:00.000', 121) AS [C1]
FROM ( SELECT 1 AS X ) AS [SingleRowTable85]) AS [UnionAll83]
UNION ALL
SELECT
convert(datetime, '2003-02-28 00:00:00.000', 121) AS [C1]
FROM ( SELECT 1 AS X ) AS [SingleRowTable86]) AS [UnionAll84]
UNION ALL
SELECT
convert(datetime, '2003-03-31 00:00:00.000', 121) AS [C1]
FROM ( SELECT 1 AS X ) AS [SingleRowTable87]) AS [UnionAll85]
UNION ALL
SELECT
convert(datetime, '2003-04-30 00:00:00.000', 121) AS [C1]
FROM ( SELECT 1 AS X ) AS [SingleRowTable88]) AS [UnionAll86]
UNION ALL
SELECT
convert(datetime, '2003-05-31 00:00:00.000', 121) AS [C1]
FROM ( SELECT 1 AS X ) AS [SingleRowTable89]) AS [UnionAll87]
UNION ALL
SELECT
convert(datetime, '2003-06-30 00:00:00.000', 121) AS [C1]
FROM ( SELECT 1 AS X ) AS [SingleRowTable90]) AS [UnionAll88]
UNION ALL
SELECT
convert(datetime, '2003-07-31 00:00:00.000', 121) AS [C1]
FROM ( SELECT 1 AS X ) AS [SingleRowTable91]) AS [UnionAll89]
UNION ALL
SELECT
convert(datetime, '2003-08-31 00:00:00.000', 121) AS [C1]
FROM ( SELECT 1 AS X ) AS [SingleRowTable92]) AS [UnionAll90]
UNION ALL
SELECT
convert(datetime, '2003-09-30 00:00:00.000', 121) AS [C1]
FROM ( SELECT 1 AS X ) AS [SingleRowTable93]) AS [UnionAll91]
UNION ALL
SELECT
convert(datetime, '2003-10-31 00:00:00.000', 121) AS [C1]
FROM ( SELECT 1 AS X ) AS [SingleRowTable94]) AS [UnionAll92]
UNION ALL
SELECT
convert(datetime, '2003-11-30 00:00:00.000', 121) AS [C1]
FROM ( SELECT 1 AS X ) AS [SingleRowTable95]) AS [UnionAll93]
UNION ALL
SELECT
convert(datetime, '2003-12-31 00:00:00.000', 121) AS [C1]
FROM ( SELECT 1 AS X ) AS [SingleRowTable96]) AS [UnionAll94]
UNION ALL
SELECT
convert(datetime, '2004-01-31 00:00:00.000', 121) AS [C1]
FROM ( SELECT 1 AS X ) AS [SingleRowTable97]) AS [UnionAll95]
UNION ALL
SELECT
convert(datetime, '2004-02-29 00:00:00.000', 121) AS [C1]
FROM ( SELECT 1 AS X ) AS [SingleRowTable98]) AS [UnionAll96]
UNION ALL
SELECT
convert(datetime, '2004-03-31 00:00:00.000', 121) AS [C1]
FROM ( SELECT 1 AS X ) AS [SingleRowTable99]) AS [UnionAll97]
UNION ALL
SELECT
convert(datetime, '2004-04-30 00:00:00.000', 121) AS [C1]
FROM ( SELECT 1 AS X ) AS [SingleRowTable100]) AS [UnionAll98]
UNION ALL
SELECT
convert(datetime, '2004-05-31 00:00:00.000', 121) AS [C1]
FROM ( SELECT 1 AS X ) AS [SingleRowTable101]) AS [UnionAll99]
UNION ALL
SELECT
convert(datetime, '2004-06-30 00:00:00.000', 121) AS [C1]
FROM ( SELECT 1 AS X ) AS [SingleRowTable102]) AS [UnionAll100]
UNION ALL
SELECT
convert(datetime, '2004-07-31 00:00:00.000', 121) AS [C1]
FROM ( SELECT 1 AS X ) AS [SingleRowTable103]) AS [UnionAll101]
UNION ALL
SELECT
convert(datetime, '2004-08-31 00:00:00.000', 121) AS [C1]
FROM ( SELECT 1 AS X ) AS [SingleRowTable104]) AS [UnionAll102]
UNION ALL
SELECT
convert(datetime, '2004-09-30 00:00:00.000', 121) AS [C1]
FROM ( SELECT 1 AS X ) AS [SingleRowTable105]) AS [UnionAll103]
UNION ALL
SELECT
convert(datetime, '2004-10-31 00:00:00.000', 121) AS [C1]
FROM ( SELECT 1 AS X ) AS [SingleRowTable106]) AS [UnionAll104]
UNION ALL
SELECT
convert(datetime, '2004-11-30 00:00:00.000', 121) AS [C1]
FROM ( SELECT 1 AS X ) AS [SingleRowTable107]) AS [UnionAll105]
UNION ALL
SELECT
convert(datetime, '2004-12-31 00:00:00.000', 121) AS [C1]
FROM ( SELECT 1 AS X ) AS [SingleRowTable108]) AS [UnionAll106]
UNION ALL
SELECT
convert(datetime, '2005-01-31 00:00:00.000', 121) AS [C1]
FROM ( SELECT 1 AS X ) AS [SingleRowTable109]) AS [UnionAll107]
UNION ALL
SELECT
convert(datetime, '2005-02-28 00:00:00.000', 121) AS [C1]
FROM ( SELECT 1 AS X ) AS [SingleRowTable110]) AS [UnionAll108]
UNION ALL
SELECT
convert(datetime, '2005-03-31 00:00:00.000', 121) AS [C1]
FROM ( SELECT 1 AS X ) AS [SingleRowTable111]) AS [UnionAll109]
UNION ALL
SELECT
convert(datetime, '2005-04-30 00:00:00.000', 121) AS [C1]
FROM ( SELECT 1 AS X ) AS [SingleRowTable112]) AS [UnionAll110]
UNION ALL
SELECT
convert(datetime, '2005-05-31 00:00:00.000', 121) AS [C1]
FROM ( SELECT 1 AS X ) AS [SingleRowTable113]) AS [UnionAll111]
UNION ALL
SELECT
convert(datetime, '2005-06-30 00:00:00.000', 121) AS [C1]
FROM ( SELECT 1 AS X ) AS [SingleRowTable114]) AS [UnionAll112]
UNION ALL
SELECT
convert(datetime, '2005-07-31 00:00:00.000', 121) AS [C1]
FROM ( SELECT 1 AS X ) AS [SingleRowTable115]) AS [UnionAll113]
UNION ALL
SELECT
convert(datetime, '2005-08-31 00:00:00.000', 121) AS [C1]
FROM ( SELECT 1 AS X ) AS [SingleRowTable116]) AS [UnionAll114]
UNION ALL
SELECT
convert(datetime, '2005-09-30 00:00:00.000', 121) AS [C1]
FROM ( SELECT 1 AS X ) AS [SingleRowTable117]) AS [UnionAll115]
UNION ALL
SELECT
convert(datetime, '2005-10-31 00:00:00.000', 121) AS [C1]
FROM ( SELECT 1 AS X ) AS [SingleRowTable118]) AS [UnionAll116]
UNION ALL
SELECT
convert(datetime, '2005-11-30 00:00:00.000', 121) AS [C1]
FROM ( SELECT 1 AS X ) AS [SingleRowTable119]) AS [UnionAll117]
UNION ALL
SELECT
convert(datetime, '2005-12-31 00:00:00.000', 121) AS [C1]
FROM ( SELECT 1 AS X ) AS [SingleRowTable120]) AS [UnionAll118] ON (((DATEPART (year, [UnionAll59].[C1])) = (DATEPART (year, [UnionAll118].[C1]))) OR ((DATEPART (year, [UnionAll59].[C1]) IS NULL) AND (DATEPART (year, [UnionAll118].[C1]) IS NULL))) AND (((DATEPART (month, [UnionAll59].[C1])) = (DATEPART (month, [UnionAll118].[C1]))) OR ((DATEPART (month, [UnionAll59].[C1]) IS NULL) AND (DATEPART (month, [UnionAll118].[C1]) IS NULL))) ) AS [Join3]
WHERE ([Extent1].[StartDate] <= [Join3].[C12]) AND ((CASE WHEN ([Extent1].[EndDate] IS NOT NULL) THEN [Extent1].[EndDate] ELSE GetDate() END) >= [Join3].[C11])
) AS [Project237]
GROUP BY [Project237].[Name], [Project237].[Name1], [Project237].[C1], [Project237].[C2]
) AS [GroupBy1]
) AS [Project238]
ORDER BY [Project238].[C2] ASC, [Project238].[C3] ASC, [Project238].[Name] ASC, [Project238].[Name1] ASC;
Method Name:GetEmployeesCount;
Class Name:CompanyEmployees.Data.ReportBuilder;
Elapsed Miliseconds:336
Afterword
I am sure there are still many bugs, but I will try to correct all errors as quickly as possible. Also, I want to ask advice from a respected Habr community how to deal with this profiler, on the one hand, I want to make a free product, but at the same time make some money. Currently, I am thinking about making the profiler completely free for personal use and paid for companies, but again at a minimum price of 20-30 bucks. Well, or just leave it free with a Donate button.
Thank you all for your attention.I hope this tool will be useful.
PS: For all questions, bug reports, etc. you can write to the e-mail: developer@ef-logger.com or st.glushak@gmail.com.
Nuget package for EF6: https://www.nuget.org/packages/EFlogger-For6/
Nuget package for EF4-5: https://www.nuget.org/packages/EFlogger-For4/
Updated: Added poll:
Source: https://habr.com/ru/post/221681/
All Articles