Introducing the Model Comparer for Entity Framework v4

This is a brief intro to the just-released Model Comparer for Entity Framework. The model comparer is part of the Huagati DBML/EDMX Tools add-in for VS2010, and was added in version 1.91 released on 29 July 2010.

 

The problem

The Entity Framework designer in Visual Studio 2010 has a lot of improvements over earlier versions. Like its' predecessor in VS2008 it has the ability to update the model from the database, but like its predecessor it gives the user a rather limited choice over what to update.

 

Developers often need a more fine-grained control over the diffs and updates; there may be hand-tweaked parts of the EF model that they don't want to change and there may be database changes that should not be propagated over to the model.

 

The model comparer

The model comparer is an attempt to give developers more control over the EF4 designer in Visual Studio 2010 in terms of handling db-model differences and intra-model-differences.

The add-in doesn't replace the EF designer and doesn't remove anything that is part of Visual Studio. It does however give a more detailed overview over differences between each of the layers; database, storage model, and conceptual model. The differences are displayed in three treeviews, and there are a number options for bringing individual or all differences across as well as for ignoring differences.

 

Whenever a model is updated using the model comparer, only the relevant portions of the SSDL, MSL and CSDL are updated. Entities with no changes are left untouched along with any customizations that may have been made to them. This is unlike the VS built in "update model" wizard that often overwrite manual changes to the model when only a specific change should have been brought across.

 

I have made a short intro video showing the model comparer in action in VS2010 against the Microsoft AdventureWorks database. Enjoy!

 

Simplify Entity Framework v4 models with complex types

One neat feature in Microsoft’s Entity Framework is support for complex types in the model. Complex types allow you to group together a bunch of properties in their own type and reuse that type from multiple entities. Complex types have been supported by Entity Framework from v1 although the v1 designer in Visual Studio 2008 didn’t support complex types, so until Visual Studio 2010 models using complex types had to be hand-edited in an XML editor.

The EFv4 designer in Visual Studio 2010 does support complex types, but it still involves a fair bit of manual work to define them, add them to entities and create mappings when working with multiple entities. There is a “Refactor into New Complex Type” feature in the VS2010 EF designer, but it unfortunately only works with one entity at a time.

To remedy this I looked into simplifying complex type creation and usage from multiple entity classes by adding a feature to Huagati DBML/EDMX Tools . The goal is to make generating and using complex types in EFv4 easier for EF models generated from existing databases.

The new feature is quite simple, it lists all seemingly identical entity members in a model, ordered by number of occurrences in the model. A user can then select the members they want to generate a complex type from, and give the new type a name.

While selecting members, the add-in shows which model entities share the selected members. When adding a complex type this way, the underlying entities and the conceptual-to-storage mapping will be automatically updated to incorporate the complex types and map to the correct storage members.

The process can then be repeated as many times as wanted, leading to a less cluttered model diagram with less repeated-over-and-over-again member properties.

…entities without complex types…

…and the same entities after adding complex types…

This feature will be part of Huagati DBML/EDMX Tools version 1.90, together with some other new EFv4-specific features. The EFv4 specific features will only be available when using the add-in from Visual Studio 2010. Previously existing features will still work in both VS2008 and VS2010.

Entity Framework support in Huagati Query Profiler (formerly known as Huagati Linq-to-SQL profiler)

When I first introduced the Huagati Linq-to-SQL Profiler nearly a year ago, I mentioned that it would eventually support other data access technologies than just Linq-to-SQL. Second up was support for LLBLGen profiling released in October 2009. Now the turn has finally come to Microsoft’s Entity Framework 4.0.

The concept is the same, the packaging is the same, and existing licensed users of the profiler can simply install the latest version to add support for Entity Framework query profiling in addition to the already existing Linq-to-SQL and LLBLGen profilers.

Why? Why not use SQL Profiler instead?

Database performance, and conserving db-side resources is often key to app performance in database driven applications. There are many tools around for profiling, and there can sometimes be an overlap between what they do.

The Huagati Query Profiler bridges the gap between SQL Server and OR-mapper code. It collects important information about queries executed by Linq-to-SQL, Entity Framework, and LLBLGen and combines that information with stack traces and other details that make it easier to find out what code in an application is responsible for the most expensive or most long-running database queries. It overlaps with the SQL Server Profiler in collecting query information, with Visual Studio, and with SQL Server Management Studio, but adds the benefit of linking information otherwise available separately in each of the three applications together.

What does it do? How does it work?

The profiler consists of two main parts:

1. A redistributable runtime DLL that you can reference from and include with your applications. There is one runtime logging DLL for each of EFv4, L2S, and LLBLGen. The runtime logging DLL collects information about what queries are executed against the database along with useful information such as timings, I/O cost, execution plans, and various feedback from SQL Server's query optimizer.
2. The profiler log explorer application that is used for exploring logs generated by the runtime DLL, and examining individual log entries in more detail.

Filters

Filters can be set up in both layers; in the runtime component to avoid logging non-costly queries or to narrow down on specific types of query behavior, and in the log explorer to narrow down specific types of queries when examining existing logs.

The logging components have several built-in filters, and custom filters can easily be added if needed.

Code samples

After installing the profiler, Windows Programs menu will get a new program group called Huagati Query Profiler:

The first entry, Code Samples (zip) will open up a zip file containing a handful of sample Visual Studio projects in C# and VB. The sample projects shows how to integrate the runtime component with an existing application, and includes sample queries showing some typical operations. The Entity Framework 4 sample is called AdventureWorksEF and it uses the Microsoft AdventureWorks sample database.

AdventureWorks EF model in VS2010 (click for larger image)

Sample ObjectContext with EF Profiling support added (click for larger image)

The UI of the sample project is simple, a small form with a single button. Behind the button are several sample queries, some doing a simple primary key lookup, others doing group/aggregate queries etc.

Query in the code samples project (click for larger image)

To generate some sample log entries for the log explorer, run the sample project and click on the Test! button.

Code sample project UI: a single button

Before running the sample project, ensure that the connection string in app.config is pointing to where you have installed the Microsoft AdventureWorks sample database.

The profiler log explorer

After generating log entries, start up the Profiler Log Explorer (Start/All Programs/Huagati Query Profiler/Huagati Query Profiler-Log Explorer).

The Huagati Query Profiler Log Explorer main window (click for larger image)

The log explorer's main window is divided in a few different sections:

• The top portion of the screen contains a grid listing all queries in the log or all queries passing the criteria set under the Filters option in the toolbar):

Query list (click for larger image)

• In the lower left portion of the screen it shows the query behind the currently selected log entry, along with visual indicators for I/O, timings, and feedback from SQL Server's optimizer:

Query details (click for larger image)

• In the lower right portion of the screen it shows the call stack that led to the query being executed. If symbols (pdb) were present when the log entry was created, the call stack will contain convenient hyperlinks that can be used to open up the corresponding source code file on a developer machine.

Call stack (click for larger image)

Query information details

Many of the elements in the Query Information portion of the log explorer window will show additional details when hovered over, and/or when clicked on:

“Missing Indexes” alert with details in hover-popup (click for larger image) 


Table scan alert with accompanying tooltip (click for larger image) 


I/O statistics are shown when hovering over any of the I/O numbers or info bars


Timings are available by hovering over the timing numbers or indicator bars

Some of the links, such as the execution plan link or source code links will start up other applications such as SQL Server Management Studio or Visual Studio.

Clicking on the Execution Plan link for example will open up the logged execution plan for the query in SSMS:

Clicking on the “execution plan” link brings up SSMS with the logged execution plan (click for larger image)

Missing indexes sample

The particular sample query shown in the screenshots above has a missing index alert:

Missing indexes alert (click for larger image)

A missing index alert means that SQL Server's optimizer determined that the query in question can be made more efficient by adding indexes to one or more tables involved. Hovering over the index alert will display the optimizer's suggested indexes.

In this case, the optimizer suggested three new indexes:

create index IX_SalesOrderHeader_ShipToAddressID on [AdventureWorks].[Sales].[SalesOrderHeader] ([ShipToAddressID], [OrderDate]) include ([Status], [TotalDue]);

create index IX_SalesOrderHeader_OrderDate on [AdventureWorks].[Sales].[SalesOrderHeader] ([OrderDate], [Status]) include ([ShipToAddressID], [TotalDue]);

create index IX_Address_PostalCode on [AdventureWorks].[Person].[Address] ([PostalCode]) include ([AddressID]);

However, looking closer at the optimizer's suggestions, two of the indexes can be combined into one to avoid overlap:

create index IX_Address_PostalCode on [Person].[Address] ([PostalCode])

create index IX_SalesOrderHeader_ShipToAddressID on [Sales].[SalesOrderHeader] ([ShipToAddressID], [OrderDate], [Status]) include ([TotalDue]);

After re-running the sample app and hitting Refresh in the log explorer we can see what the result of the new indexes were - in this case the query went from 919 reads and a table scan, down to to 54 and index seeks:

Query I/O for sample #1 after adding indexes as per above 

Where do I get it, how do I get started?

The Entity Framework 4 logging component for the Huagati Query Profiler is included as of version 1.31. The EF component is currently marked as a beta version as it is still undergoing testing, but is available for anyone to download and try out. The current download URL for v1.31 (beta) is
http://www.huagati.com/l2sprofiler/download/HuagatiQueryProfiler_131_beta.zip

 

If you have an existing license for Huagati Linq-to-SQL Profiler or Huagati Query Profiler, you can simply install the latest version to get the EF logging component in addition to the Linq-to-SQL and LLBLGen logging components. If you don't have a license you can request a trial license key from
http://www.huagati.com/l2sprofiler/

 

Additionally, during the beta period - if you become a beta tester and the provide feedback from your testing you will get a full license key for the final released version. To apply to become a beta tester, send an email to support (at) huagati.com.

 

Feedback and questions are always welcome, you can send your feedback or questions to support (at) huagati.com, or post it in the support forum, or post a comment in this blog.

Query Profiling for LLBLGen Pro-based applications

When I introduced the first release of the Huagati Linq-to-SQL Profiler back in August, I mentioned that there would be additional profiler runtime logging providers besides the initial one for Linq-to-SQL; "Additional logging providers; if there is enough interest I may extend the runtime logging component to also support Entity Framework, plain ADO.NET, or other data access technologies such as 3rd party OR/Ms.".

The profiler's log file format and the profiler log viewer is not tied in any way to Linq-to-SQL specifically. Although the choice of name for the tool may not be ideal, it was designed to be used with other OR mappers and data access technologies as well.

A few weeks ago I contacted Frans Bouma, author of the popular LLBLGen Pro O/R Mapper Generator. LLBLGen Pro has been around for six years and is packed with great functionality; no doubt the market leading OR mapper for .net. Frans was enthusiastic about the idea, so we exchanged a couple of emails to determine the best approach to integrate profiler logging into LLBLGen based projects without requiring too much of an effort of work by end-users. Frans suggested using a partial class implementation of LLBLGen's DataAccessAdapter class, so that's the direction I went.

Due to some time constraints in other projects I had to suspend work on the LLBLGen profiler logging component for a couple of weeks, but it is now ready for release.

To enable query profiling in a LLBLGen based project, some minor additions are needed in the application to be profiled. A reference to the profiler logging assembly must be added, and a new partial class implementation of the LLBLGen Pro DataAccessAdapter containing calls to the profiler logger need to be included in the same project as the LLBLGen generated code.

The following screenshots shows step-by-step how to enable query profiling in the LLBLGen Northwind Adapter sample project (downloadable from http://www.llblgen.com/pages/files/Example_NorthwindCS1_Adapter_06062008.zip ). Click on the individual screenshots for a full-size version.

Step 1: Add a reference to the profiler logging assembly (HuagatiLLBLGenProfiler.dll) (A), include LoggingAdapter.cs (B) (both are installed together with the profiler itself under C:\Program Files [(x86)]\Huagati Systems Co Ltd\Huagati Linq-to-SQL Profiler\redist ). Next, change the namespace in LoggingAdapter.cs to match the namespace of the application's own DataAccessAdapter class (C).


Step 2: Add a call to one of the InitializeLogging methods in the DataAccessAdapter's constructor(s). There are a few different overloads of InitializeLogging, depending on what logging features, filters, etc that you want to use.


Step 3: Compile and run the application.


Step 4: Start the Huagati Linq-to-SQL Profiler Viewer, open the logging directory used, and have fun with the logs:


Just as when profiling a Linq-to-SQL application, the top part of the window shows log entries and the bottom part shows details such as I/O statistics, timings, call stack etc pertaining to the selected log entry. All blue underlined links are clickable and will bring up additional details; clicking on the execution plan link will open the execution plan diagram in SSMS, clicking on the source code links in the call stack will open up the source file linked to in Visual Studio and so on.

Database-side I/O statistics and timings are shown in easy-to-spot bars indicating the relative cost. Each one has a tooltip containing more detailed information from the database engine:


The call stack portion shows the call stack that led to a certain query being executed, with hyperlinks directly to the source code (whenever debug information and source code is available):


In short, it is as easy to get started with profiling LLBLGen based applications as it is to profile Linq-to-SQL based applications.

The filters available in the LLBLGen logging component are the same as the filters available in the Linq-to-SQL logging component. This means that after adding profiling to a LLBLGen app as in the steps above, the examples shown in "A walkthrough of the newest filters and visual cues in the Huagati Linq-to-SQL Profiler", and "Profiling Linq-to-SQL Applications" will work with the LLBLGen profiler logging component as well. Custom logging filters can of course also be added the same way; by creating a new class inheriting from the ProfilerFilter base class or from one of the pre-defined filters.

The profiler logging component for LLBLGen Pro is included in Huagati Linq-to-SQL Profiler version 1.20 (scheduled for release on Oct 12 2009).

Code sample: Search and replace in Linq expression trees

I recently had the need* to do a 'search and replace' of a parameter in a linq query. It would have been nice to have a set of .Replace methods on the expression object to achieve this, but unfortunately there is no such thing. Instead I had to write a new expression tree visitor to clone the tree, and in the process replace the expression I want replaced with its' replacement.

I figured it would make sense to post it here in case anyone else need a search-and-replace for expression trees. This sample will replace all occurences of one ParameterExpression with another one but can easily be changed to replace any other type of expression.

* = And as for the 'why would I ever need to do that' - my specific need was to take a linq query and switch around portions of the projection it was doing without having to define an entirely new projection. I will get to the details of that in a follow up post - to keep this one short and simple... :)

Enjoy:

internal static class ExpressionExtensions
{
    /// <summary>
    /// Replace all occurences of a ParameterExpression within an expression tree with another ParameterExpression, and return a cloned tree
    /// </summary>
    /// <param name="expression">Tree to replace parameters in</param>
    /// <param name="oldParameter">Parameter to replace</param>
    /// <param name="newParameter">Parameter to use as replacement</param>
    /// <returns>A cloned expression tree with all occurences of oldParameter replaced with newParameter</returns>
    public static Expression ReplaceParameter(this Expression expression, ParameterExpression oldParameter, ParameterExpression newParameter)
    {
        Expression exp = null;
        Type expressionType = expression.GetType();
        if (expressionType == typeof(ParameterExpression))
        {
            exp = ((ParameterExpression)expression).ReplaceParameter(oldParameter, newParameter);
        }
        else if (expressionType == typeof(MemberExpression))
        {
            exp = ((MemberExpression)expression).ReplaceParameter(oldParameter, newParameter);
        }
        else if (expressionType == typeof(MethodCallExpression))
        {
            exp = ((MethodCallExpression)expression).ReplaceParameter(oldParameter, newParameter);
        }
        else if (expressionType == typeof(NewExpression))
        {
            exp = ((NewExpression)expression).ReplaceParameter(oldParameter, newParameter);
        }
        else if (expressionType == typeof(UnaryExpression))
        {
            exp = ((UnaryExpression)expression).ReplaceParameter(oldParameter, newParameter);
        }
        else if (expressionType == typeof(ConstantExpression))
        {
            exp = ((ConstantExpression)expression).ReplaceParameter(oldParameter, newParameter);
        }
        else if (expressionType == typeof(ConditionalExpression))
        {
            exp = ((ConditionalExpression)expression).ReplaceParameter(oldParameter, newParameter);
        }
        else if (expressionType == typeof(LambdaExpression))
        {
            exp = ((LambdaExpression)expression).ReplaceParameter(oldParameter, newParameter);
        }
        else if (expressionType == typeof(MemberInitExpression))
        {
            exp = ((MemberInitExpression)expression).ReplaceParameter(oldParameter, newParameter);
        }
        else if (expressionType == typeof(BinaryExpression))
        {
            exp = ((BinaryExpression)expression).ReplaceParameter(oldParameter, newParameter);
        }
        else
        {
            //did I forget some expression type? probably. this will take care of that... :)
            throw new NotImplementedException("Expression type " + expression.GetType().FullName + " not supported by this expression tree parser.");
        }
        return exp;
    }

    /// <summary>
    /// Replace all occurences of a ParameterExpression within an expression tree with another ParameterExpression, and return a cloned tree
    /// </summary>
    /// <param name="expression">LambdaExpression to replace parameters in</param>
    /// <param name="oldParameter">Parameter to replace</param>
    /// <param name="newParameter">Parameter to use as replacement</param>
    /// <returns>A cloned expression tree with all occurences of oldParameter replaced with newParameter</returns>
    public static LambdaExpression ReplaceParameter(this LambdaExpression expression, ParameterExpression oldParameter, ParameterExpression newParameter)
    {
        LambdaExpression lambdaExpression = null;
        lambdaExpression = Expression.Lambda(
            expression.Type,
            expression.Body.ReplaceParameter(oldParameter, newParameter),
            (expression.Parameters != null) ? expression.Parameters.ReplaceParameter(oldParameter, newParameter) : null
            );
        return lambdaExpression;
    }

    /// <summary>
    /// Replace all occurences of a ParameterExpression within an expression tree with another ParameterExpression, and return a cloned tree
    /// </summary>
    /// <param name="expression">BinaryExpression to replace parameters in</param>
    /// <param name="oldParameter">Parameter to replace</param>
    /// <param name="newParameter">Parameter to use as replacement</param>
    /// <returns>A cloned expression tree with all occurences of oldParameter replaced with newParameter</returns>
    public static BinaryExpression ReplaceParameter(this BinaryExpression expression, ParameterExpression oldParameter, ParameterExpression newParameter)
    {
        BinaryExpression binaryExp = null;
        binaryExp = Expression.MakeBinary(
            expression.NodeType,
            (expression.Left != null) ? expression.Left.ReplaceParameter(oldParameter, newParameter) : null,
            (expression.Right != null) ? expression.Right.ReplaceParameter(oldParameter, newParameter) : null,
            expression.IsLiftedToNull,
            expression.Method,
            (expression.Conversion != null) ? expression.Conversion.ReplaceParameter(oldParameter,newParameter) : null
            );
        return binaryExp;
    }

    /// <summary>
    /// Replace all occurences of a ParameterExpression within an expression tree with another ParameterExpression, and return a cloned tree
    /// </summary>
    /// <param name="expression">ParameterExpression to replace parameters in</param>
    /// <param name="oldParameter">Parameter to replace</param>
    /// <param name="newParameter">Parameter to use as replacement</param>
    /// <returns>A cloned expression tree with all occurences of oldParameter replaced with newParameter</returns>
    public static ParameterExpression ReplaceParameter(this ParameterExpression expression, ParameterExpression oldParameter, ParameterExpression newParameter)
    {
        ParameterExpression paramExpression = null;
        if (expression.Equals(oldParameter))
        {
            paramExpression = newParameter;
        }
        else
        {
            paramExpression = expression;
        }
        return paramExpression;
    }

    /// <summary>
    /// Replace all occurences of a ParameterExpression within an expression tree with another ParameterExpression, and return a cloned tree
    /// </summary>
    /// <param name="expression">MemberExpression to replace parameters in</param>
    /// <param name="oldParameter">Parameter to replace</param>
    /// <param name="newParameter">Parameter to use as replacement</param>
    /// <returns>A cloned expression tree with all occurences of oldParameter replaced with newParameter</returns>
    public static MemberExpression ReplaceParameter(this MemberExpression expression, ParameterExpression oldParameter, ParameterExpression newParameter)
    {
        return Expression.MakeMemberAccess(
            (expression.Expression != null) ? expression.Expression.ReplaceParameter(oldParameter, newParameter) : null,
            expression.Member);
    }

    /// <summary>
    /// Replace all occurences of a ParameterExpression within an expression tree with another ParameterExpression, and return a cloned tree
    /// </summary>
    /// <param name="expression">MemberInitExpression to replace parameters in</param>
    /// <param name="oldParameter">Parameter to replace</param>
    /// <param name="newParameter">Parameter to use as replacement</param>
    /// <returns>A cloned expression tree with all occurences of oldParameter replaced with newParameter</returns>
    public static MemberInitExpression ReplaceParameter(this MemberInitExpression expression, ParameterExpression oldParameter, ParameterExpression newParameter)
    {
        return Expression.MemberInit(
            (expression.NewExpression != null) ? expression.NewExpression.ReplaceParameter(oldParameter, newParameter) : null,
            (expression.Bindings != null) ? expression.Bindings.ReplaceParameter(oldParameter, newParameter) : null
            );
    }

    /// <summary>
    /// Replace all occurences of a ParameterExpression within an expression tree with another ParameterExpression, and return a cloned tree
    /// </summary>
    /// <param name="expression">MethodCallExpression to replace parameters in</param>
    /// <param name="oldParameter">Parameter to replace</param>
    /// <param name="newParameter">Parameter to use as replacement</param>
    /// <returns>A cloned expression tree with all occurences of oldParameter replaced with newParameter</returns>
    public static MethodCallExpression ReplaceParameter(this MethodCallExpression expression, ParameterExpression oldParameter, ParameterExpression newParameter)
    {
        MethodCallExpression callExpression = null;
        callExpression = Expression.Call(
            (expression.Object != null) ? expression.Object.ReplaceParameter(oldParameter, newParameter) : null,
            expression.Method,
            (expression.Arguments != null) ? expression.Arguments.ReplaceParameter(oldParameter, newParameter) : null
            );
        return callExpression;
    }

    /// <summary>
    /// Replace all occurences of a ParameterExpression within an expression tree with another ParameterExpression, and return a cloned tree
    /// </summary>
    /// <param name="expression">NewExpression to replace parameters in</param>
    /// <param name="oldParameter">Parameter to replace</param>
    /// <param name="newParameter">Parameter to use as replacement</param>
    /// <returns>A cloned expression tree with all occurences of oldParameter replaced with newParameter</returns>
    public static NewExpression ReplaceParameter(this NewExpression expression, ParameterExpression oldParameter, ParameterExpression newParameter)
    {
        return Expression.New(
            expression.Constructor,
            (expression.Arguments != null) ? expression.Arguments.ReplaceParameter(oldParameter, newParameter) : null,
            expression.Members);
    }

    /// <summary>
    /// Replace all occurences of a ParameterExpression within a ReadonlyCollection of ParameterExpressions with another ParameterExpression, and return as an IEnumerable
    /// </summary>
    /// <param name="expression">ReadOnlyCollection&lt;ParameterExpression&gt; to replace parameters in</param>
    /// <param name="oldParameter">Parameter to replace</param>
    /// <param name="newParameter">Parameter to use as replacement</param>
    /// <returns>A IEnumerable returning the passed in set of ParameterExpressions, with occurences of oldParameter replaced with newParameter</returns>
    public static IEnumerable<ParameterExpression> ReplaceParameter(this System.Collections.ObjectModel.ReadOnlyCollection<ParameterExpression> expressionArguments, ParameterExpression oldParameter, ParameterExpression newParameter)
    {
        if (expressionArguments != null)
        {
            foreach (ParameterExpression argument in expressionArguments)
            {
                if (argument != null)
                {
                    yield return argument.ReplaceParameter(oldParameter, newParameter);
                }
                else
                {
                    yield return null;
                }
            }
        }
    }

    /// <summary>
    /// Replace all occurences of a ParameterExpression within a ReadonlyCollection of Expressions with another ParameterExpression, and return as an IEnumerable
    /// </summary>
    /// <param name="expression">ReadOnlyCollection&lt;Expression&gt; to replace parameters in</param>
    /// <param name="oldParameter">Parameter to replace</param>
    /// <param name="newParameter">Parameter to use as replacement</param>
    /// <returns>A IEnumerable returning the passed in set of Expressions, with occurences of oldParameter replaced with newParameter</returns>
    public static IEnumerable<Expression> ReplaceParameter(this System.Collections.ObjectModel.ReadOnlyCollection<Expression> expressionArguments, ParameterExpression oldParameter, ParameterExpression newParameter)
    {
        if (expressionArguments != null)
        {
            foreach (Expression argument in expressionArguments)
            {
                if (argument != null)
                {
                    yield return argument.ReplaceParameter(oldParameter, newParameter);
                }
                else
                {
                    yield return null;
                }
            }
        }
    }

    /// <summary>
    /// Replace all occurences of a ParameterExpression within a ReadonlyCollection of ElementInits with another ParameterExpression, and return as an IEnumerable
    /// </summary>
    /// <param name="expression">ReadOnlyCollection&lt;ElementInit&gt; to replace parameters in</param>
    /// <param name="oldParameter">Parameter to replace</param>
    /// <param name="newParameter">Parameter to use as replacement</param>
    /// <returns>A IEnumerable returning the passed in set of ParameterExpressions, with occurences of oldParameter replaced with newParameter</returns>
    public static IEnumerable<ElementInit> ReplaceParameter(this System.Collections.ObjectModel.ReadOnlyCollection<ElementInit> elementInits, ParameterExpression oldParameter, ParameterExpression newParameter)
    {
        if (elementInits != null)
        {
            foreach (ElementInit elementInit in elementInits)
            {
                if (elementInit != null)
                {
                    yield return Expression.ElementInit(elementInit.AddMethod, elementInit.Arguments.ReplaceParameter(oldParameter, newParameter));
                }
                else
                {
                    yield return null;
                }
            }
        }
    }

    /// <summary>
    /// Replace all occurences of a ParameterExpression within a ReadonlyCollection of MemberBindings with another ParameterExpression, and return as an IEnumerable
    /// </summary>
    /// <param name="expression">ReadOnlyCollection&lt;MemberBinding&gt; to replace parameters in</param>
    /// <param name="oldParameter">Parameter to replace</param>
    /// <param name="newParameter">Parameter to use as replacement</param>
    /// <returns>A IEnumerable returning the passed in set of ParameterExpressions, with occurences of oldParameter replaced with newParameter</returns>
    public static IEnumerable<MemberBinding> ReplaceParameter(this System.Collections.ObjectModel.ReadOnlyCollection<MemberBinding> memberBindings, ParameterExpression oldParameter, ParameterExpression newParameter)
    {
        if (memberBindings != null)
        {
            foreach (MemberBinding binding in memberBindings)
            {
                if (binding != null)
                {
                    switch (binding.BindingType)
                    {
                        case MemberBindingType.Assignment:
                            MemberAssignment memberAssignment = (MemberAssignment)binding;
                            yield return Expression.Bind(binding.Member, memberAssignment.Expression.ReplaceParameter(oldParameter, newParameter));
                            break;
                        case MemberBindingType.ListBinding:
                            MemberListBinding listBinding = (MemberListBinding)binding;
                            yield return Expression.ListBind(binding.Member, listBinding.Initializers.ReplaceParameter(oldParameter, newParameter));
                            break;
                        case MemberBindingType.MemberBinding:
                            MemberMemberBinding memberMemberBinding = (MemberMemberBinding)binding;
                            yield return Expression.MemberBind(binding.Member, memberMemberBinding.Bindings.ReplaceParameter(oldParameter, newParameter));
                            break;
                    }
                }
                else
                {
                    yield return null;
                }
            }
        }
    }

    /// <summary>
    /// Replace all occurences of a ParameterExpression within an expression tree with another ParameterExpression, and return a cloned tree
    /// </summary>
    /// <param name="expression">UnaryExpression to replace parameters in</param>
    /// <param name="oldParameter">Parameter to replace</param>
    /// <param name="newParameter">Parameter to use as replacement</param>
    /// <returns>A cloned expression tree with all occurences of oldParameter replaced with newParameter</returns>
    public static UnaryExpression ReplaceParameter(this UnaryExpression expression, ParameterExpression oldParameter, ParameterExpression newParameter)
    {
        return Expression.MakeUnary(
            expression.NodeType,
            (expression.Operand != null) ? expression.Operand.ReplaceParameter(oldParameter, newParameter) : null,
            expression.Type,
            expression.Method);
    }

    /// <summary>
    /// Replace all occurences of a ParameterExpression within an expression tree with another ParameterExpression, and return a cloned tree. Note: this version of ReplaceParameter exists just for conformity - there can't be a parameter expression hiding under a constant expression so this could really be skipped.
    /// </summary>
    /// <param name="expression">ConstantExpression to replace parameters in</param>
    /// <param name="oldParameter">Parameter to replace</param>
    /// <param name="newParameter">Parameter to use as replacement</param>
    /// <returns>A cloned expression tree with all occurences of oldParameter replaced with newParameter</returns>
    public static ConstantExpression ReplaceParameter(this ConstantExpression expression, ParameterExpression oldParameter, ParameterExpression newParameter)
    {
        //return Expression.Constant(expression.Value, expression.Type);
        return expression;
    }

    /// <summary>
    /// Replace all occurences of a ParameterExpression within an expression tree with another ParameterExpression, and return a cloned tree
    /// </summary>
    /// <param name="expression">ConditionalExpression to replace parameters in</param>
    /// <param name="oldParameter">Parameter to replace</param>
    /// <param name="newParameter">Parameter to use as replacement</param>
    /// <returns>A cloned expression tree with all occurences of oldParameter replaced with newParameter</returns>
    public static ConditionalExpression ReplaceParameter(this ConditionalExpression expression, ParameterExpression oldParameter, ParameterExpression newParameter)
    {
        return Expression.Condition(
            (expression.Test != null) ? expression.Test.ReplaceParameter(oldParameter, newParameter) : null,
            (expression.IfTrue != null) ? expression.IfTrue.ReplaceParameter(oldParameter, newParameter) : null,
            (expression.IfFalse != null) ? expression.IfFalse.ReplaceParameter(oldParameter, newParameter) : null
            );
    }
}

A walkthrough of the newest filters and visual cues in the Huagati Linq-to-SQL Profiler

Some useful new features were added to the latest two versions of the Huagati Linq-to-SQL Profiler. First up in version 1.14 was a handful of new runtime filters in the redistributable DLLs that takes care of logging profiler data; among the new filters are the MissingIndexFilter and the MissingIndexAndPageReadsFilter . As the names suggest, these two new filters use the SQL Server optimizer's ability to detect and recommend indexes for queries when it is unable to find a suitable index. The SQL Server optimizer include that information in the generated execution plans as well as store it temporarily in distributed management views from where it can be extracted at runtime.

The new filters enables profiling of Linq-to-SQL based applications based on this information; with these filters we can tell the Linq-to-SQL Profiler to log only queries where the optimizer detect missing indexes, optionally filtered by a minimum number of page reads to avoid logging queries that have a low I/O cost anyway, and to avoid extracting execution plans for low cost queries.

The following is a quick walkthrough showing how to use the new filters together with the new visual cues added to the profiler's log viewer in version 1.15 to analyse the logs.

First of all, I created a test database with a decent amount of records and both indexed and unindexed columns that can be used in joins and where clause criteria. The test database contains three identical tables with a guid primary key and two nvarchar(100)'s - one indexed and one not indexed. To have some data to play with the creation script also adds 999,999 records to all three of the tables:



...next, I created a new project in Visual Studio 2008 with a Linq-to-SQL datacontext pointing to the new test database. Of course, I used my other Linq-to-SQL tool, Huagati DBML/EDMX Tools, to fix up the class and member names and to add inline xml documentation to the generated classes.



...after creating the datacontext, I added a new partial class containing a new constructor overload and new dispose method to the datacontext class. The new constructor contains the logic to enable profiling and to instruct the profiler to use the MissingIndexAndPageReadsFilter to only log queries that do more than 10k page reads, and where the SQL Server execution plan contain alerts for missing indexes.

In a test app like this one the runtime filter is not really necessary, but in a real world app containing hundreds of queries using runtime filters is a great way to ensure that only real 'offender' queries are logged.



...so with a test database set up, a Linq to SQL datacontext pointing to it, profiler logging enabled, time to write a test query. For this demonstration I do a simple three-table join with the first join done on indexed columns, the second join on columns with no covering indexes, and the where clause criteria using a non indexed column. This should result in two missing index alerts from the optimizer as well as a decent amount of page reads since we will do full table scans on two of the 999,999 record tables.



...and just to highlight a feature in Huagati DBML/EDMX Tools that could help catching this kind of mistakes already when writing the query in the code editor - the 'update documentation' feature in the add-in added information about what indexes exist (and don't exist) in the XML documentation for the entity classes and their members. Hovering over a member will show what index(es) (if any) exist on the underlying column in the database:



Next, I ran the test app once to generate some log data. After that I opened up the log directory I was interested in (I could go for the root log directory if I want to see everything stored in my logs, or I can - as in the screenshot below - use the directory structure generated by the logging component to filter down by date and time):



Since I ran the test app only once, there is only one entry in the log folder I opened. Now, look in the lower left corner of this screenshot of the log viewer:



The new part is the two little alerts in the lower left corner. One of them says that this log entry contain missing index alerts, and the other one says that there was a table scan on more than 10k rows. Had the scan been on between 1k and 10k rows that alert would have been yellow rather than red. Hovering over the table scan alert shows which table(s) was scanned, as well as estimated and actual number of rows that were scanned:



...and hovering over the Missing Index(es) alert brings up a floating window showing the indexes that the SQL Server optimizer would like to see in the database to execute this query in the most efficient way.



Now, it is worth mentioning that these are recommendations (or rather a wish list) generated by the SQL Server optimizer. That doesn't mean that it is always a good idea to generate the indexes it would need. In real life there is of course a balance; over indexing can be as bad as not having enough indexes since it increases I/O when writing as well as (if there are too many indexes covering the same column(s)) increasing the number of possibilities that the SQL Server optimizer must consider when compiling an execution plan. In other words, review the index recommendations carefully, edit as necessary, and create indexes thereafter. Blindly creating everything the optimizer asks for is a sure way to get into trouble. (Did I mention the database I recently reviewed for a customer where they had a large table with 70+ columns and 56 indexes? Don't do that....)

Despite my caution above, I will go ahead and just follow the optimizer generated recommendations blindly, just for the purpose of demonstration. So I clicked the "copy" link next to the "Missing index(es)" alert in the profiler log viewer to get the new indexes onto the clipboard, jumped right over to SSMS and created the indexes as they were:



Next, suspecting that the new indexes will result in the profiler not logging anything with the previous runtime filter, I removed the MissingIndexAndPageReadsFilter filter from the test app and replaced it with a null. This means 'no filtering at all, please' and will ensure that the profiler logging component writes all queries to the log:



...and after re-running the test app, I refreshed the profiler's log viewer window to see the new entry. The new entry shows significantly less reads (although there's still room to optimize this query additionally, but that's another story for another day), and both the table scan and missing index alerts are gone from the log entry details at the bottom of the screen:




So, that was a quick walkthrough of what the new runtime filters can do. Of course, there are plenty of other runtime filters and other goodies in the profiler - some covered in a previous blog entry ( Profiling Linq-to-SQL applications ). Also not mentioned above is the PrePlanPostPlanFilter that allows two filters to be combined together; one not relying on execution plans and one relying on execution plans. This is useful from a profiling performance perspective; the profiler will not extract the execution plans for a query unless it passes the first (non-execution-plan-dependant) filter and will then apply the execution plan dependant filter as the last step to determine what queries should be logged or not logged.

If you want to try out any of the above, or test the profiler with your own Linq-to-SQL based apps, go ahead and download it and get a trial license (free for 45 days) over at http://www.huagati.com/L2SProfiler/

Model first in Entity Framework v1.0

Every now and then people ask in forums etc how to do model first with Entity Framework version 1 (.net 3.5 SP1 / Visual Studio 2008 SP1). In other words, how to create a model using the Entity Framework designer in Visual Studio and then generate a database from that model. The simple answer is that it is not supported out of the box. However, since one of my tools has supported that for quite some time I figured it was about time to repost a tutorial on how to do model first with EFv1 (for those who don't want to wait until Visual Studio 2010 where it will be supported out-of-the-box).

So... here are the steps. Requires Visual Studio 2008 (not express edition, but any higher SKU will work), and Huagati DBML/EDMX Tools to be installed. (Huagati DBML/EDMX Tools is free to use for the first 45 days, if you want to continue using it after that please buy a license to support continued development).

Step 1: Create a new EDMX file (Entity Framework model file).

Step 2: Choose "empty model" if you start from scratch, or "generate from database" if you have an existing database that you want to use as a base.

Step 3: Add entities, entity members, associations etc. In other words, create a model.

Step 4: Review the model...

Step 5: Generate SSDL (Entity Framework's storage layer description) from the CSDL generated by the designer:

Step 6: After generating SSDL, the VS2008 output window shows a summary of the SSDL generation process (what was generated etc). Just as a FYI thing...

Step 7: Generate SQL-DDL for the database.

Step 8: Review the SQL-DDL that was generated, change data types as necessary, add precision/length where needed etc.