3 мая 2014 в 22:09

Профайлер для Entity Framework tutorial



Приветствую!

Продолжу серию статей про программирование. На этот раз, я хочу рассказать о написанном мной бесплатном профайлере для Entity Framework-а ( далее EF) c 4-й по 6-ю версию, а также о некоторых примененных мной решениях, таких как декомпилирование .NET кода на лету, форма для обработки исключений и прочее.
Nuget пакет для EF6: https://www.nuget.org/packages/EFlogger-For6/
Nuget пакет для EF4 — 5: https://www.nuget.org/packages/EFlogger-For4/

Подробности под катом.

Введение

Когда я начинал изучение EF, передо мной стал вопрос в профилировании запросов которые генерирует EF. После некоторого поиска я с неудовольствием обнаружил, что штатных средств EF не предоставляет, соответственно, приходилось пользоваться, на мой взгляд, крайне неудобной в использовании программой SQL Server Profiler или купить достаточно дорогой сторонний инструмент Entity Framework Profiler 2.0, за $389.00, а я жадный.

С появлением EF версии 6, ситуация с логированием команд изменилась. Появился механизм, который позволяет добавить свой интерцептор:
//Регистрируем  при старте программы свой 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));
	}
}

Для 4-й версии EF похожего механизма логирования я не нашел.

Мое решение

Идею написать свой профайлер, повторяющий функционал платного, я вынашивал достаточно долго. Первоначальная версия базировалась на механизме интерцепторов EF 6-й версии, но к сожалению в EF 4-й версии этот механизм не работает.
После недолгого гугления, была найдена отличная библиотека для профилировки EF в ASP MVC приложениях, под названием MiniProfiler . На его основе и было решено сделать свое приложения.

Что получилось

Система получилась максимально простой для использования и интеграции. Забрать бинарники с примером проекта можно на созданном мной сайте на wordpress-e http://ef-logger.com/
Nuget пакет для EF6: https://www.nuget.org/packages/EFlogger-For6/
Главное окно приложения:


Использование

1. Забрать архив с бинарниками и примером проекта

2. Подключить библиотеку EFlogger.EntityFramework4.dll для EF 4-5 версии и EFlogger.EntityFramework6.dll для EF 6-й версии
3. Инициализировать профайлер путем выполнения статического метода
// Для EF 6-й версии
EFloggerFor6.Initialize();
// или для  EF с 4-й по 6-ю
EFloggerFor4.Initialize();

4. Запустить приложение EFlogger.exe

API

Пример для библиотеки EFlogger.EntityFramework6.dl, в случае использования EFlogger.EntityFramework4.dl статический класс будет назваться 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”);


Как это все работает

Механизм внедрения в EF 4-й версии сильно отличается от EF 6-й версии.
Штатные механизмов подмены DbProviderFactory в EF 4 отсутствуют, поэтому приходится идти на подмену штатной фабрики на свою через рефлекцию.
Сам процесс выглядит так:
// проверяем что все фабрики загруженны
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);
    }
}

Для интеграции в EF 6-й версии можно воспользоваться штатным механизмом подстановки сервисов:
DbConfiguration.Loaded += (_, a) => a.ReplaceService<DbProviderServices>((s, k) => new EFProfiledDbProviderServices());


Декомпиляция

EFlogger на лету декомпелирует код и отображает тело метода, который сгенерировал тот или иной запрос. Для этого используются библиотеки из бесплатного аналога рефлектора ILSpy. Чтобы декомпировать метод нужно его сначала найти поднявшись по Stack Trace и исключить лишние вызовы сторонних библиотек:
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);
        }
    }
}

Пример рабочего проекта декомпиляции на яндекс файлах: yadi.sk/d/AzBGet5-Nwns2

Окно обработки исключительной ситуации

Ни для кого не секрет, что приложения периодически падают, это может произойти по самым разным причинам и задача разработчика показать какое-то вменяемое окно для сообщения об ошибке.
Мой вариант формы выглядит так:


Форма предлагает пользователю добавить информацию об ошибке, шаги для воспроизведения и свой email для ответа.
При нажатии на кнопку Отправить, на почту разработчику приходить письмо со всеми данными.
 [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();
}

Пример рабочего проекта с формой обработки ошибок можете забрать на яндекс файлах: yadi.sk/d/7y4i_cz7NwtE3

Сетевое взаимодействие

Подробности прокола сетевого взаимодействия между подключаемой библиотекой и профайлером вы можете прочитать из моей предыдущей статьи: Cетевое взаимодействие посредством TCP в C#

Немного юмора

Во время тестирования инструмента на подопытном тулзе товарища, был найден вот такой невероятный сгенерированный запрос (очень большой и странный):
Запрос
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

Послесловие


Уверен, багов еще много, но я постараюсь все ошибки исправлять максимально быстро. Так же, я хочу спросить совета у уважаемого хабра сообщества как поступить с этим профайлером, так с одной стороны хочется сделать бесплатный продукт, но в то же время и немного заработать. В настоящее время, я думаю о том чтобы сделать профайлер полностью бесплатным для личного использования и платным для компаний, но опять же по минимальной цене баксов в 20-30. Ну или просто оставить бесплатным с кнопочкой Donate.

Всем спасибо за внимание. Надеюсь этот инструмент будет полезен.

PS: По всем вопросам, баг-репортам и прочее можете писать на почту: developer@ef-logger.com или st.glushak@gmail.com. Также, автор ищет подработку, ибо ипотека. Спасибо за понимание.
Nuget пакет для EF6: https://www.nuget.org/packages/EFlogger-For6/
Nuget пакет для EF4 — 5: https://www.nuget.org/packages/EFlogger-For4/
Updated: Добавил опрос:
Что делать дальше с профайлером?

Проголосовало 285 человек. Воздержалось 113 человек.

Только зарегистрированные пользователи могут участвовать в опросе. Войдите, пожалуйста.

Станислав @Diaver
карма
9,0
рейтинг 0,0
Похожие публикации
Самое читаемое Разработка

Комментарии (13)

  • +2
    Как я понял из статьи за основу вы взяли код MiniProfiler. Тогда прежде чем решать вопрос монетизации, следует почитать лицензию на MiniProfiler. Возможно что кроме OpenSource у вас не останется вариантов.
    • 0
      Совершенно верно, за основу был взят код от MiniProfiler.
      Проект опубликован под Apache лицензией. Насколько я понял, с некоторыми оговорками лицензия разрешает использование кода в платных продуктах.
  • 0
    SQL Server Profiler — не то, чтобы сильно не удобный… он просто не для EF
    Он рассчитан на просмотр того, что происходит на SQL Server, а не того, что выходит с EF.

    Лично меня больше всего в sql server profile досаждало- необходимость иметь права, куда большие чем нужные приложению.
    Т.е. под стандартной учеткой на чтение данных использование профилировщика- не получится.
  • 0
    Есть еще кстати одно решение, если вы дебажитесь из студии- Intellitrace можно включить настройку логировать события ado.net.
    Оно конечно не подходит не для .net проектов, но EF я именно так дебажил, когда не хватало прав на сервере.
  • 0
    В стандартном SQL-профайлере есть очнь важная вещь, которой здесь нет — оценка трудоемкости выполнения запроса в терминах CPU/disk/IO cost — это гораздо важнее, чем время выполнения, которое в однопользовательском режиме с тестовыми данными практически ни о чем не говорит. Вернее, как — все, что выполняется больше, чем за период тика системного таймера при разработке — скорее всего будет приносить проблемы с производительностью в продакшене.

    С другой стороны, польза тула несомненно есть — хотя бы для того, чтобы показать, чего стоит на стороне бакенда «легкость» манипулирования данными средствами EF
    • 0
      Вы правы, но на мой взгляд, использование EF в проектах для которых нагрузка имеет критическое значение, не самая удачная идея.
  • 0
    По разделу «Немного юмора», было бы интересно узнать: как пацан к успеху шёл? Это EF4?
    • +1
      EF6.
      Ужаснитесь)
      CompanyEmployees
      	using (var ctx = new CompanyEmployees())
      	{
      		int minYear = ctx.OccupationHistories.Min(o => o.StartDate.Year);
      		int maxYear = ctx.OccupationHistories.Max(o => o.EndDate.HasValue ? o.EndDate.Value.Year : DateTime.Now.Year);
      
      		List<int> yearsList = Enumerable.Range(minYear, maxYear - minYear + 1).ToList();
      		List<int> monthList = Enumerable.Range(1, 12).ToList();
      
      		var qry1 = from y in yearsList
      				   from m in monthList
      				   select new DateTime(y, m, 1);
      		var firstDays = qry1.ToList();
      
      		var qry2 = from y in yearsList
      				   from m in monthList
      				   select new DateTime(y, m, DateTime.DaysInMonth(y, m));
      		var lastDays = qry2.ToList();
      
      		var qry = from s in
      					  (
      						  from o in ctx.OccupationHistories
      						  join d in ctx.Divisions
      							  on o.DivisionID equals d.Id
      						  join p in ctx.Positions
      							  on o.PositionID equals p.Id
      						  from x in
      							  (
      								  from a in firstDays
      								  join b in lastDays
      									  on new { a.Year, a.Month } equals new { b.Year, b.Month }
      								  select new
      								  {
      									  FirstDay = a,
      									  LastDay = b
      								  })
      						  where DateTime.Compare(o.StartDate, x.LastDay) <= 0 &&
      						  DateTime.Compare(o.EndDate.HasValue ? o.EndDate.Value : DateTime.Now, x.FirstDay) >= 0
      
      						  orderby o.EmployeeID
      						  select new
      						  {
      							  EmployeeId = o.EmployeeID,
      							  Division = d.Name,
      							  Position = p.Name,
      							  Year = x.FirstDay.Year,
      							  Month = x.LastDay.Month
      						  })
      				  group s by new { s.Division, s.Position, s.Year, s.Month } into g
      				  orderby g.Key.Year, g.Key.Month, g.Key.Division, g.Key.Position
      				  select new EmployeesCount
      				  {
      					  Division= g.Key.Division,
      					  Position= g.Key.Position,
      					  Year= g.Key.Year,
      					  Month= g.Key.Month,
      					  EmployeeCount = g.Count()
      				  };
      
      		return qry.ToList();
      

      • 0
        Кошмар. Средствами SQL — два подзапроса или временных таблицы-генератора для месяца и диапазона лет, подзапрос с cross join с ними внутри, дальше inner join с occupation histories по datepart(year… и datepart(month… и затем count / group by — как-то так
  • 0
    Я пользуюсь вот этой простой бесплатной штукой, отлично все показывает:
    expressprofiler.codeplex.com
    • 0
      Спасибо, искал в свое время что-то более удобное стандартного SQL профайлера, но все таки мой инструмент служит несколько для другого.
  • 0
    Очень странный и я бы даже сказал опасный пример с LogFormatter: инстанс интерцептора регистрируется один раз и используется изо всех тредов; в реальных условиях (например в каком-нть сайте с десятками одновременных реквестов) логика start / stop stopwatch-ей просто не работает — любая из команд начинает таймер, любая из других стопает таймер, да и вообще несколько тредов могут одновременно читать и писать в переменную таймера. Можно пытаться развлекаться с ThreadStatic на переменной, но это тоже не сработает в случае асинхронных запросов в EF.

Только полноправные пользователи могут оставлять комментарии. Войдите, пожалуйста.