KristoferA's blog

Entity Framework Model Creation beyond ‘Database First’ / ‘Model First’

2011-05-19T18:08:00.001+07:00

The built in tools for Entity Framework 4 in Visual Studio forces users to make a distinct choice between the “Database First”, “Model First”, or “Code First” model creation and update strategies. A recent MSDN Magazine article by Julie Lerman describes the options and choices that has to be made on which approach to use for updating the model.

I think those three choices are a bit limiting in that not being able to continually evolve both the model and the underlying database is not an ideal situation for many projects. Applications evolve and so do the underlying databases, often with changes made by several people using different tools. That is the reason why I created the Model Comparer for EF4 in the first place.

Even if the model is based on an existing database, a.k.a. ‘Database First’, I think it should be possible to use the EF designer built into Visual Studio to update the model and add/remove/change entities/tables, members/columns, associations/foreign keys etc.

Likewise, if the model is designed from scratch in the EF designer (‘Model First’), it should be possible to make incremental changes with SQL-DDL diff scripts, as well as making schema changes directly in the database and sync those changes back to the model.

I made a short (4½ minute long) screencast illustrating how this is possible to do with the EFv4 visual designer together with the model comparer. In this video I make changes both to the database in SQL Server Management Studio and to the model in Visual Studio and then sync it up to replicate the changes into both the database and the model.

Enjoy:

The video above is also available on youtube at http://www.youtube.com/watch?v=doqYOlcEAZM , although the youtube version is lower resolution and has more MPEG compression artifacts than the one embedded above.

Oh, and if you want to try it out yourself, you can download the add-in that makes this possible from http://huagati.com/dbmltools/ and get a free trial license from the same site.

Redistribution licenses for the HuagatiEDMXTools.dll EDMX file object model

2011-01-18T19:53:00.001+07:00

In an earlier blog post, Creating or modifying Entity Framework EDMX files from code: an introduction to HuagatiEDMXTools.dll, I wrote about the EDMX file wrapper that is used by the Huagati DBML/EDMX Tools add-in for parsing, updating, creating, and writing EDMX files. HuagatiEDMXTools adds an easy to use object model on top of EDMX files (see documentation at http://huagati.com/edmxtools/help ), making it a lot easier to create/read/update EDMX files, and to write LINQ queries against Entity Framework 4 metadata. It also has a number of built-in queries and lookup functions that make it a breeze to work with Entity Framework 4 EDMX files from code.

Previously, using the HuagatiEDMXTools runtime library required an end-user license for Huagati DBML/EDMX Tools. As of the latest version (v 2.20) this has been extended with a new redistributable license option, allowing third party applications to use and include HuagatiEDMXTools.

Redistribution licenses are divided into three categories:

Non-development tool license: allows redistribution of HuagatiEDMXTools.dll together with software products not primarily targeted at software developers and whose primary purpose is not to develop software. This may be line-of-business applications that need to generate, update, or read EDMX data to fulfill some functional requirement.
Development tool license: allows redistribution of HuagatiEDMXTools.dll together with software products whose primary purpose is to assist software development. This may be a tool specifically for working with Entity Framework 4 EDMX files, such as a new model editor or EF model designer.
Source code license: Source code licenses includes the source code for the HuagatiEDMXTools runtime library. The source code can not be redistributed, and any libraries/assemblies based on or derived from it may only be distributed as part of another software product that is not substantially similar to or competitive with HuagatiEDMXTools. (It may however be used for products competitive with the Huagati DBML/EDMX Tools add-in.)

Pricing and additional details are available at http://huagati.com/dbmltools/

Inferring Foreign Key Constraints in Entity Framework Models (Part 2)

2010-12-28T17:43:00.001+07:00

In my last blog entry, Inferring Foreign Key Constraints in Entity Framework Models, I wrote about a new feature in the Model Comparer for EFv4 that allow foreign key constraints to be inferred and added to an Entity Framework model even if some or all FKs are missing in the database. The inferred FKs can then be used to generate associations and navigation properties in Entity Framework models.

In the latest release of Huagati DBML/EDMX Tools, the FK inference feature has been improved with a new regex match-and-replace feature for matching foreign key columns to primary key columns. This allows FKs to be inferred even if the FK/PK columns to be matched use inconsistent naming and/or are named with special prefixes/suffixes etc.

Of course, the basic name matching feature described in the previous article is still there, and can be used under many (if not most) common scenarios. However, when the PK/FK naming is more complex then using Regex match+replace comes in handy for resolving what columns in one table can be mapped to the PK in another table.

The following screenshot shows the settings dialog with the new settings for RegExp-based column matching:

In addition to allowing the standard regular expression language elements to be used in the Match field, and the substitution elements to be used in the Replace field, there are two reserved keywords that can be used in the replacements as well; %pktable% which is replaced with the name of the primary-key-side table and %fktable% which is replaced with the name of the foreign key table when the match is evaluated.

The above screenshot shows how regular expression replacements plus the %pktable% substitution keyword can be used to match two tables even if the PK column is named “id” and the FK column in another table is named using the PK table’s name followed by “_key”; if the result of the Match+Replace on both sides evaluate to the same value, a foreign key constraint is inferred and will be displayed in the diff-tree and diff-reports in the Model Comparer’s main window.

The Model Comparer

If you want to read more about the Model Comparer and the other features for Entity Framework contained in the Huagati DBML/EDMX Tools add-in for Visual Studio, see the following links:

Introducing the Model Comparer for Entity Framework v4

What’s new in the latest version of the Model Comparer for Entity Framework 4

Using the Model Comparer to generate difference reports for Entity Framework v4 models

Mixing inheritance strategies in Entity Framework models

SQL Azure support in Huagati DBML/EDMX Tools

Inferring Foreign Key Constraints in Entity Framework Models

Simplify Entity Framework v4 models with complex types

…and of course, there is a brief summary of the add-in’s supported features for both Entity Framework and Linq-to-SQL at the product page: http://huagati.com/dbmltools/ where you can also download the add-in and get a free 30-day trial license.

Inferring Foreign Key Constraints in Entity Framework Models

2010-12-21T18:35:00.001+07:00

Foreign key constraints are one of the most important components of relational database models. At the database level they are key to maintaining data integrity and prevents applications and users from writing invalid data or from orphaning data by inadvertently deleting data referenced from other tables.

When reverse-engineering entity relationships in modelling tools and OR mappers, foreign key constraints serve yet another important role; they also describe the relationship between tables/entities. Without foreign key constraints, OR mappers can’t generate navigation properties between entities, and will not know in what order* it should process inserts, updates, and deletes for various tables.

* = e.g. generating order detail records before the main order record has been created.

Databases with no FK constraints

Unfortunately, there are lots of real-world databases that - although they are created and deployed in RDBMSes – lack foreign key constraints. The reasons for omitting FK constraints vary, but it is not uncommon that the reasons stated are based on misconceptions, misunderstandings, or lack of information. Sometimes they are omitted by programmers that can’t get their head around proper order of inserts/deletes, sometimes they are omitted “for performance reasons”, sometimes noone can remember anymore why they were omitted. Whatever the reason, relational databases with some or all FK constraints missing are out there and they’re fairly common.

Adding FK constraints to legacy databases

One way to tackle FK-less databases is simply to add the missing FK constraints. Simple solution to data integrity issues, and makes OR mappes happy, right? Unfortunately it is not that simple. The real problem with FK-less databases is the applications behind them. If there are tens-of-thousands, hundreds-of-thousands, or millions of lines of code that interact with those databases you can be certain that some of it will break if you just add all the missing FK constraints. There’s almost certainly code that do inserts/deletes in the wrong order, and there’s a fairly good chance that there is junk data or orphaned data that will break the FK constraint already during creation, and so on.

Inferring FK constraints

Since FK constraints are key to generating the associations in OR mappers such as Entity Framework, I decided to add a feature to infer FKs. Instead of creating them in the database, I use table and column names and types to deduce what might be a FK candidate in the database. After that, the inferred FKs are displayed in the Model Comparer’s list of missing FK constraints.

The user can choose and pick both real and inferred foreign key constraints and add them to the model selectively, and generate model-level associations and navigation properties without actually creating the FKs in the database.

The tool also allows inferred FK constraints to be materialized into SQL-DDL scripts, but that part is entirely optional.

Demonstration

The following set of screenshots demonstrate how the FK inference feature in the Model Comparer for EFv4 can be used to infer FKs in a database and to add selected keys to the EF4 model.

Step 1 – open a EFv4 model in Visual Studio 2010, and bring up the Model Comparer:

Step 2 – Click on Settings to open the settings dialog. Go to the Infer Foreign Keys tab.

Select the type of objects you want to infer FKs for; tables and/or views. Although views can not have FK constraints in the database it is perfectly fine to infer FKs and use them to add associations to views. This makes it possible to have navigation properties to/from views.

There are four different name matching options controlled by two checkboxes: whether PK members contain the name of the entity or not, whether entity names and member names are separated with underscores or not. Changing the checkboxes updates the table example in the middle of the dialog between the four different naming conventions, to make it easy to verify that the settings match the desired naming conventions.

Step 3 – with the Infer Foreign Key Constraints for Tables setting and/or the Infer Foreign Key Constraints for Views setting enabled, the model comparer will infer FK constraints where they don’t exist in the database. Inferred FK constraints start with the FKI prefix and has a different icon than existing FK constraints to make it easy to differentiate between inferred and real FK constraints.

Step 4 (optional) – The Report view also show all inferred keys. If generating a lot of keys the report view is a more eye-friendly way to go through them in order to determine which ones are valid/desired in the model.

Step 5 – Select the FK constraints that you want to use for generating associations in the model. Click Update SSDL to add them to the storage model (SSDL) in the Entity Framework 4 model.

Step 6 (optional) – The SSDL-to-DB tree will now show the newly added association. This allows inferred FKs to be materialized as SQL-DDL-scripts if desired. If not, select them and click Ignore. If you want to generate a SQL-DDL script, allowing the inferred FKs to be added to the database, select the FKs and click on Update Database.

Step 7 (optional) – If you generated SQL-DDL in Step 6, the SQL-DDL script will appear in a separate SQL editor in the Visual Studio instance that the Model Comparer belongs to. This allows you to review, edit, and save the script before applying to the database.

Step 8 – Select the new association(s) in the SSDL-to-CSDL tree and click Update CSDL to add them to the conceptual layer of the model. This will result in navigation properties added between the involved entities.

Step 9 – Close the model comparer and return to Visual Studio. Continue coding. Enjoy.

The Huagati Model Comparer

The Model Comparer is a feature in the Huagati DBML/EDMX Tools add-in for Visual Studio 2010. The add-in can be downloaded from http://huagati.com/dbmltools/ , and time-limited free trial licenses are available from the same site.

Screencasts showcasing some of the basic functionality in the Model Comparer for EFv4 is available at http://huagati.blogspot.com/2010/07/introducing-model-comparer-for-entity.html and http://huagati.blogspot.com/2010/08/using-model-comparer-to-generate.html

Query Profiling SQL Azure when using Entity Framework or Linq-to-SQL

2010-12-13T12:38:00.001+07:00

One slight shortcoming in Microsoft’s SQL Azure (the SQL Server 2008 flavor that is hosted in Microsoft’s cloud platform) is that users don’t have the trace privileges needed to use SQL Profiler to profile query and performance behavior. Fortunately there are other ways to extract performance data for individual queries; SQL Azure exposes IO statistics, timings, and execution plans in the same way as normal non-cloud editions of SQL Server.

I have recently done some testing against SQL Azure using my tools. Huagati DBML/EDMX Tools needed some minor adjustments to work against SQL Azure as outlined in a previous blog post: http://huagati.blogspot.com/2010/12/sql-azure-support-in-huagati-dbmledmx.html

Next up was testing the profiling / logging components for Entity Framework 4 and Linq-to-SQL in Huagati Query Profiler against SQL Azure. Fortunately I can announce that it works just fine; the techniques used by the Huagati Query Profiler’s logging components for capturing server-side timings, I/O statistics, query execution plans are all supported in SQL Azure. The log entries will look the same and the same performance data and filtering options that work against local SQL Server instances can be used against SQL Server in the cloud.

One interesting thing I noticed while testing is that the roundtrip times against the SQL Azure instance I am accessing is only 100-150ms above accessing a database on my local LAN. That is impressive considering I am in Thailand and the SQL Azure instance is in Singapore, and faster than what I have seen mentioned in some forums and blog posts from US SQL Azure users.

To get started, download and install the Huagati Query Profiler from http://huagati.com/l2sprofiler/ and take a look at these blog posts and/or the sample code that is installed together with the profiler:

Linq-to-SQL: http://huagati.blogspot.com/2009/06/profiling-linq-to-sql-applications.html
Entity Framework: http://huagati.blogspot.com/2010/06/entity-framework-support-in-huagati.html

Next, you can either try the sample projects that ship with the profiler against your own SQL Azure instance, or add profiling support to your own Entity Framework 4 or Linq-to-SQL based projects against a SQL Azure database.

Creating or modifying Entity Framework EDMX files from code: an introduction to HuagatiEDMXTools.dll

2010-12-02T09:50:00.001+07:00

Microsoft’s Entity Framework stores models as xml-based EDMX files. Within the EDMX file, the model is divided up into the conceptual layer (CSDL) which describes the C# or VB entity classes and methods, the storage layer (SSDL) which describes the database tables/keys/views/procedures etc, the mapping layer (MSL) which describes how everything in the conceptual model maps to the storage model, and finally one portion describing the designer diagram layout.

The EDMX files are typically generated and modified by the designer in Visual Studio, by edmgen.exe/edmgen2.exe, or by third party tools such as the model comparer in Huagati DBML/EDMX Tools.

Reading/writing EDMX files

Sometimes people look for ways to generate, modify, or parse EDMX files from code. The straightforward answer to that is usually to use an XML parser; XmlDocument, XmlReader/XmlWriter, Linq-to-XML etc. Using XML parsers to read and write EDMX files works fine, but it also requires a fair bit of time spent on reading the CSDL, SSDL, and MSL specifications as well as some manual work figuring out some of the things not clearly spelt out in the specification such as how different common or uncommon mapping scenarios are best described in the MSL.

The HuagatiEDMXTools.dll library

Before developing the new Entity Framework 4 features in Huagati DBML/EDMX Tools, I decided to step away from directly using an XML parser and instead add an intermediate abstraction layer. That makes it a lot easier to work with the models and separates out most of the quirky mapping logic. The result was a separate wrapper library called HuagatiEDMXTools.dll . Underneath it still uses a XML parser, but all model artifacts are exposed as classes, properties, IEnumerables, IQueryables etc. Some related object types such as associationsets and associations are merged together, related objects available as properties, and useful queries and lookups are exposed as simple lookup properties and IQueryables/functions.

Although HuagatiEDMXTools was written to support the EFv4-related functionality in Huagati DBML/EDMX Tools, I decided to document it to allow add-in users to write their own code to create/modify/read EDMX models for scenarios that are not covered by the add-in or other tools. Although the library has been around and exposed since version 1.90 and for the most part fairly self-explanatory, the documentation for it wasn’t released until version 2.11. The documentation is in MSDN format (thanks, Sandcastle and SHFB), and is available online at http://huagati.com/edmxtools/help/ as well as in the form of a HTML help file that is installed together with the Huagati DBML/EDMX Tools add-in.

To use the HuagatiEDMXTools library from a VS project, simply add a reference to the file HuagatiEDMXTools.dll from the project references:

When using the library, start by initializing the licensing module by passing the license data from the add-in’s license file ( \users\[username]\Documents\Visual Studio 2010\Addins\HuagatiDBMLTools2010.dll.lic ) to the license class:

Samples

Next, you can use the library to create, modify, read EDMX files, or simply to query for various model metrics using some of the built-in queries, or by writing new ones against the many IEnumerables and IQueryables exposed by the library.

Download

As I mentioned above, the HuagatiEDMXTools library ships with Huagati DBML/EDMX Tools. You can download the latest version of Huagati DBML/EDMX Tools from http://huagati.com/dbmltools/ and get a license (free trial, or a full paid license) from the same site.

The online help for the library is available at http://huagati.com/edmxtools/help/ as well as in a .chm file that is installed together with Huagati DBML/EDMX Tools.

SQL Azure support in Huagati DBML/EDMX Tools

2010-12-01T18:16:00.001+07:00

Recently I have been asked by several users if Huagati DBML/EDMX Tools supports SQL Azure. Until now, the answer was no; SQL Azure has some limitations compared to normal SQL Server 2008/2005/2000. However, after the question was asked again in the support forum this morning I decided to take another look at it.

I came across two minor obstacles:

1) SQL Azure supports only a subset of SQL Server’s system views. The add-in rely on several of these to extract schema data, documentation, indexes etc from the database.

2) SQL Azure is technically not available from Thailand until August 2011, and being based in Thailand I couldn’t directly sign up for it.

The first one is simple to solve in code, if connecting to SQL Azure the add-in simply have to avoid using some SQL Server features such as extended properties until those are supported in SQL Azure. This means the documentation sync feature in the add-in will not be able to retrieve documentation information from SQL Azure, since SQL Azure can not store it.

The second one was solved by Microsoft Thailand’s developer evangelist, Khun Ekaraj, who was nice enough to set me up with a one month SQL Azure trial pass. That allowed me to validate that the add-in worked against SQL Azure with the necessary changes applied.

As of today’s release, version 2.12, SQL Azure is now supported from Huagati DBML/EDMX Tools when running under Visual Studio 2010.

Mixing inheritance strategies in Entity Framework models

2010-10-14T13:15:00.001+07:00

The Microsoft Entity Framework v4 runtime supports three entity inheritance models: TPT, TPH, and TPC. When using the built-in EF designer and tools in Microsoft Visual Studio 2010, the default inheritance strategy in model-first scenarios is TPT. TPH inheritance can be manually mapped in the designer, and TPC requires editing the mapping (MSL) in the EDMX file manually or using third party tools.

TPT, TPH, and TPC explained

TPT is short for Table Per Type. In TPT inheritance, there is one central table containing all the base class members and then one table per subclass containing the key and the members that are immediate (non-inherited) members of the subclass. In database terms this can be described as a normalized model, with additional tables for members that are not always present.

TPH is short for Table Per Hierarchy. In TPH inheritance there is only one table representing the base class and all subclasses. Members must be nullable to avoid errors when a subclass don't contain members of another subclass. In database terms TPH can be described as a denormalized model, since one wide table is used to represent all of the types in the hierarchy with columns that are left with a null value if they are not members of the entity type.

TPC is short for Table Per Concrete type. In TPC inheritance, every class in a inheritance hierarchy will have their own table. The inheritance hierarchy masks the fact that there are several independent underlying tables, representing each subtype.

Choosing inheritance strategy in Entity Framework

Choosing an inheritance strategy when using the VS2010 built-in tools often means making a model-wide decision on what type of entity inheritance to use. If you work in a 'Model First' pattern, where you create your entity classes in the model designer and then generate SSDL and the database from that, the designer will by default use TPT inheritance for all hierarchies. Microsoft have some plugins that can add model-first TPH support to the designer, and if you want to use TPC inheritance you need to do your mappings by hand using an XML editor or third-party tools.

Microsoft's Alex James has written a nice blog post on how to choose inheritance strategy, that describes the differences and merits to each of the inheritance strategies supported by Entity Framework:
http://blogs.msdn.com/b/alexj/archive/2009/04/15/tip-12-choosing-an-inheritance-strategy.aspx

However, I don't think the decision of what inheritance strategy to use should be a model-level decision. Each of the inheritance strategies have their merits and drawbacks:

TPT inheritance is sleek storage wise, the tables involved contain only the valid members. However, the SQL queries generated for complex TPT hierarchies can easily become very large and complex even for simple hierarchies. For complex hierarchies there can be a noticeable overhead in generating the queries, when SQL Server generates execution plans, and in cost of executing the queries.

Even with a handful of subtypes, TPT hierarchies require EF to create all possible joins and then create a big UNIONed query covering all options, unless the query specifically asks for a single type within the hierarchy.

Some of the performance considerations related to TPT are outlined in a blog post in the EF team’s blog:
http://blogs.msdn.com/b/adonet/archive/2010/08/17/performance-considerations-when-using-tpt-table-per-type-inheritance-in-the-entity-framework.aspx
TPH inheritance results in wider tables with many nullable columns, since all types within a inheritance hierarchy maps to the same table. Nullable columns opens up the possibility for missing values going undetected (bad data), and wide tables will use more storage space than narrower tables.

However, the SQL queries generated for TPH hierarchies are more simple and TPH inheritance can have a performance advantage over TPT both when generating queries and when running the queries. (Although it is worth noting that it doesn’t always have a performance advantage; pulling a few fields from a very wide table with many records can result in a lot of unnecessary I/O).
TPC inheritance is also good storage-wise since each type has its own table with no redundant columns. However, due to having multiple tables, TPC adds some constraints on the choice of keys.

Using SQL Server’s int identity columns don't work very well together with TPC since there will be duplicate entity keys when inserting in different tables all having their own identity seed, so either a spread seed (where each table has its’ own initial seed value) will be needed, or a mechanism other than SQL Server’s identity keyword is needed. Some other RDBMSes have other mechanisms allowing a sequence to be shared by multiple tables, and something similar can be achieved with customized default constraints in SQL Server. Using GUID keys, or int identity keys with different starting ranges solves that problem.

TPC is not supported by the VS2010 EFv4 designer’s mapping editor, so mapping TPC requires using an XML editor or third-party tools.

Queries against TPC hierarchies, like TPT hiearchies, makes EF generate UNIONed queries, although the queries for TPC hierarchies are typically less complex than the queries generated for TPT inheritance hierarchies since all tables are self-contained and no joins are needed between base- and subtype tables, and no additional unions when the inheritance hierarchy is more than one level deep.

Depending on complexity of the inheritance hierarchy, the shape and volume of the data, the type of queries, etc., there are often good reasons to use different inheritance strategies within one model. Instead of a model-level decision, I think it is necessary to allow inheritance strategy to be decided on a case-by-case basis for each inheritance hierarchy within a model.

New inheritance strategy settings in the Model Comparer

Enter the third party tools. The latest version of the Model Comparer for EFv4 in Huagati DBML/EDMX Tools has added support for all three inheritance strategies supported by Entity Framework: TPT, TPH, and TPC. The settings dialog for the model comparer has a new setting allowing users to pick the default inheritance strategy used in model first scenarios; when creating SSDL/MSL/SQL-DDL from CSDL.

In addition to allowing a model-level default inheritance strategy for model-first scenarios, there is a fourth inheritance strategy setting called "Mixed".

When the inheritance strategy setting is set to Mixed, a dropdown for choosing inheritance strategy is displayed in the conceptual model tools area in the model comparer. This enable developers to pick inheritance strategy on-the-fly when adding new inheritance hierarchies to the model. The SSDL and MSL generated by the model comparer's "Update SSDL" feature will then be structured based on what is selected in the dropdown.

Caveats

Does this mean inheritance strategies can be mixed within one inheritance hierarchy? No, it doesn't. Each inherited tree of entity types must stick to one inheritance strategy, but independent inherited hierarchy of entities within the model can use different inheritance strategies. To enforce this, the model comparer stores the selected inheritance strategy for each CSDL entity set when generating SSDL, and will reuse the same inheritance strategy if e.g. a new subtype is added within a hierarchy.
Does this make it easier to choose what inheritance strategy to use? No, it doesn't. Choosing inheritance strategy should be done on a case-by-case basis, and based on facts. Will you have a lot of data or just a handful of records? How complex is your inheritance strategy? Look at the queries generated by EF for your particular model, profile the queries, generate sample data corresponding or exceeding the amount of data you expect your application will generate and profile again.
Does this make it a good idea to use entity inheritance in every corner of the model? No, it doesn't. Entity inheritance is a nice feature from a code and object-orientation perspective. However, in many scenarios it is not really needed. Don't make your model more complex than necessary. There are always tradeoffs on one side or the other: going berzerk with object-orientation can add database-side complexities such as performance issues, so try to find the right balance between object orientation and a clean database that keep both developers, DBAs, and end-users happy.

The Model Comparer for Entity Framework v4

The Model Comparer is part of the Huagati DBML/EDMX Tools add-in for VS2010. If you want to try it out, you can download it from http://huagati.com/dbmltools/ and get a trial license from the same site.

If you want to learn more about the basic features in the Model Comparer, and how it improves the EFv4 designer experience in Visual Studio 2010 with new options and features, please watch the screencasts in the following blog posts:
http://huagati.blogspot.com/2010/07/introducing-model-comparer-for-entity.html
http://huagati.blogspot.com/2010/08/using-model-comparer-to-generate.html

The Model Comparer and other features in the Huagati DBML/EDMX Tools add-in keep evolving, so keep an eye out for the new features that we keep adding to it. Release cycles are generally very short and we try to get new features, improvements, and of course also bugfixes out to users quickly.

Getting started with Linq-to-SQL or Entity Framework

2010-08-26T11:42:00.001+07:00

Although they were released in 2007 (Linq-to-SQL) and 2008 (EFv1), these two data access technologies and the tools, frameworks, and language features surrounding them are new to lots of people who are just about to start using them.

The following blog post covers some resources (books, blogs, forums) that contain everything you need to get started with and/or understand how Entity Framework, Linq-to-Entities, Linq-to-SQL, or LINQ in general works.

Books and Blogs

Professional ADO.NET 3.5 with LINQ and the Entity Framework and Oakleafblog

Professional ADO.NET 3.5 with LINQ and the Entity Framework by Roger Jennings covers all the new data access technologies that were introduced with .net 3.5. Roger has written 30+ books on everything from operating systems, databases, programming, data access etc. Nowadays he is covering cloud computing in his blog and in his latest book.

Before his interest for cloud computing took over completely, Roger covered just about everything that happened in the .net data access world (Linq-to-SQL, Linq-to-[everything else], Entity Framework, ADO, etc) with daily updates in his blog,
http://oakleafblog.blogspot.com/. All those L2S/EF/ADO articles are still there if you scroll down a bit, and links to lots of useful code samples and tips and tricks.

Programming Entity Framework, Second Edition and The Data Farm

Julie Lerman has written two books on Entity Framework. The first one covered EFv1, the first version of Entity Framework that shipped with .net 3.5 SP1.

Her latest book, Programming Entity Framework, Second Edition, is for Entity Framework 4 or EFv4 that shipped with .net 4 / Visual Studio 2010. Programming Entity Framework, Second Edition is 914 pages covering every little corner and aspect of EFv4.
http://oreilly.com/catalog/9780596807252

Julie has a couple of blogs to go with the book:
http://thedatafarm.com/blog/
http://learnentityframework.com/learnentityframework/

…and she also writes the Data Points column in MSDN Magazine.

Linq in Action and ThinqLinq

Linq in Action by Jim Wooley, Fabrice Marguerie, and Steve Eichert dives into the details of all flavors of LINQ.
http://www.amazon.com/gp/product/1933988169

Jim Wooley also has a blog with lots of useful Linq tips, tricks, and solutions at http://www.thinqlinq.com/ . His blog also has a page with links to various third party tools for L2S and EF:
http://www.thinqlinq.com/default/LINQ-Tools.aspx

Damien Guard’s blog

Damien Guard used to work with the Linq-to-SQL and Entity Framework team at Microsoft. Now he is working with some other team at Microsoft (XBox..?). His blog has a lot of L2S and EF goodies, among those a neat one-page reference to the most commonly used Linq syntax. Very handy to have on the wall when writing code against Linq-to-SQL or Linq-to-Entities: http://damieng.com/blog/2009/08/12/linq-to-sql-cheat-sheet

Damien also has some links to various Linq-to-SQL resources; tools, blogs etc:
http://damieng.com/blog/2009/06/04/linq-to-sql-resources

Alex James’ blog

Nowadays focused more on OData, Microsoft’s Alex James has a lot of EF tips and tricks and useful code samples that can be used with both EF and L2S in his blog:
http://blogs.msdn.com/b/alexj/

MSDN blogs

The ADO.net team has two blogs, one covering EF in general and one with articles about future features in EF:
http://blogs.msdn.com/b/adonet/
http://blogs.msdn.com/b/efdesign/

Forums

MSDN forums

The MSDN forums, run by Microsoft, is a gold mine of useful information on both EF and L2S. Just browsing/searching these forums can reveal a lot of useful information, best practices, tips and tricks etc. If you can't find what you're looking for, post a question and you will probably have a solution within a day.

http://social.msdn.microsoft.com/Forums/en-US/adodotnetentityframework/threads
http://social.msdn.microsoft.com/Forums/en-US/linqtosql/threads

StackOverflow

StackOverflow also has a lot of useful information and helpful people answering questions, whether newbie/beginner questions or advanced stuff. Check out the EF and L2S-related tags:
http://stackoverflow.com/questions/tagged/entity-framework
http://stackoverflow.com/questions/tagged/linq-to-entities
http://stackoverflow.com/questions/tagged/linq-to-sql

Did I miss something?

The above are just a handful of the resources available, but should be good starting points. In all the blogs and forums I have linked to you will come across links to hundreds of other resources with useful information.

Using the Model Comparer to generate difference reports for Entity Framework v4 models

2010-08-17T15:31:00.001+07:00

In the previous two blog entries, Introducing the Model Comparer for Entity Framework v4, and What’s new in the latest version of the Model Comparer for Entity Framework 4 I covered the basics of using the Model Comparer to keep EFv4 models in sync within the model and with the underlying database without replacing or losing customizations made to the model.

Now the turn has come to show the latest feature in the Model Comparer: Reports.

The main work area in the model comparer shows database <=> SSDL differences, SSDL <=> database, SSDL <=> CSDL, and CSDL => SSDL differences broken down in three treeviews and allows users to sync (or ignore) individual/selected (or all) differences.

In some situations it may be more useful to generate simple diff reports listing all differences in a readable format; printable/mailable reports come in handy when emailing to colleagues, for use during meetings, to review differences in more detail before applying changes, etc.

The newest feature added to the Model Comparer for EF4 is a "Reports" tab made just for creating reports from EF4 models:

The diff reports are generated from the same model diff comparison as the diff data shown in the treeviews in the differences tab, but the report format allows a lot more details pertaining to unmapped and changed objects can be shown; from database column attributes to SQL Server extended property descriptions, and in-model documentation.

Each type of difference get a section in the report detailing all differences of that specific type:

The Model Comparer compares all available attributes; from entity level down to data types, max size, precision/scale, nullability, default values, default constraints and just about everything else that is reflected in both the database, and the SSDL and CSDL layers of the EFv4 model:

Screencast / Video

I have created a short video/screencast demonstrating the basics of the Model Comparer's new report-generator feature:

Want to try it out?

If you want to try out the Model Comparer, you can download Huagati DBML/EDMX Tools and get a trial license from http://www.huagati.com/dbmltools/ . (The model comparer is just one of many features for Entity Framework and Linq-to-SQL contained within the add-in. The report viewer was added in version 1.93.)

Keep an eye on this blog for new features; we’re rapidly adding new EFv4-related features to the Huagati DBML/EDMX Tools Add-in. Note that the EFv4-related feature set only works under Visual Studio 2010.

What’s new in the latest version of the Model Comparer for Entity Framework 4

2010-08-10T13:37:00.001+07:00

The following is a brief description of some of the new functionality that was just added to the Model Comparer for EF4.

An introduction video to the model comparer is available in the previous blog entry: “Introducing the Model Comparer for Entity Framework v4”.

New Version – new features

Yesterday I released a new version of Huagati DBML/EDMX Tools, and with it a new version of the Model Comparer with a pile of fixes, improvements, and some new features.

The goal of the Model Comparer is to give developers using Entity Framework 4.0 in Visual Studio 2010 better control over differences between the conceptual model (CSDL) and storage model (SSDL), and between the database and the storage model in EFv4 models. It does that by comparing the three layers and showing detected differences in three treeviews. The user can then select individual differences (or all) and either bring them across to the opposite layer or tell the Model Comparer to ignore differences.

The core functionality in the Model Comparer is described in more detail in the intro video in “Introducing the Model Comparer for Entity Framework v4”. If you haven’t used the model comparer or watched the intro video you may want to start with that one to get an idea of what the Model Comparer itself can do and how it works.

To give users even more control over what the Model Comparer takes into account when determining differences between the layers, it has a number of user-controllable settings. In the first version (add-in version 1.91) those settings were stored in XML files but not readily accessible from the UI.

As of version 1.92 there are UI dialogs exposing the most common settings. The settings dialogs are accessible through two new buttons in the lower left corner of the Model Comparer dialog:

The first button, “Settings” controls the behavior of the model comparer, and the second one, “CSDL Name Rules” define the naming rules used when creating CSDL entities and members from SSDL objects.

Model Comparer Settings

Most of these settings are simple on-off options so the settings dialog ~~are cluttered with~~ has a lot of checkboxes. (Yes, I know, checkbox-hell, but hopefully the settings themselves are self-explanatory enough to make it survivable on the rare occasions that these settings may need to be tweaked. It was really down to a choice between checkbox-hell and property-grid-hell and I think checkbox-hell is still easier to get an overview over.)

So, let’s break it down. The settings are grouped by the model area that they correspond to: Database, SSDL, CSDL.

Additionally there is one shared group of settings for SSDL/CSDL member differences, and one group that define what – if any - database default constraints should translate into store generated patterns in the model.

Database-to-SSDL difference settings

The first group of checkboxes controls if the model comparer should detect tables, views, columns, FK constraints, procedures and functions that exist in the database but has no representation in the SSDL:

The next group of checkboxes control what types of differences between database columns and SSDL member properties should be included in the database tree as DB->SSDL differences:

Finally, the last group of database diff settings are for stored procedures and functions; these settings control what type of differences should land stored procedures and database functions on the list of procedures with differences between the database and SSDL:

SSDL difference settings

The second portion of the settings dialog, SSDL settings, controls what types of objects present in the SSDL should be shown in the SSDL tree if not present in the database or CSDL.

The first five settings control the SSDL-to-database missing-objects comparison:

…and the following four settings controls the SSDL-to-CSDL missing-objects comparison:

CSDL difference settings

The next portion of the settings dialog, CSDL, controls if CSDL objects not present in the SSDL portion of the model should be displayed in the Model Comparer’s CSDL tree:

CSDL-to-SSDL difference settings

The CSDL to SSDL settings portion controls what types of differences at entity member level should land entities and their members on the diff list in the CSDL and SSDL trees. These settings are shared between both portions since differences in this area are shown in both trees:

Database constraint settings

The final portion of the Model Comparer’s settings dialog controls which – if any – database default constraints should be treated as StoreGeneratedPattern: Identity or StoreGeneratedPattern: Computed instead of the default StoreGeneratedPattern: None.

This comes in handy if primary key values are generated database-side by a default constraint like NewID or NewSequentialID, or if a table has ‘created date/time’ fields that are populated by the database rather than the client application.

There is also one field that control what the default constraint should be set to if generating SQL-DDL from the model comparer for tables with GUID PKs marked as StoreGeneratedPattern: Identity in the model. The default value is NewSequentialID but it can be changed to NewID or a user-defined function, as needed.

CSDL name rules

The second settings button available in the Model Comparer controls how CSDL entities and CSDL members generated from SSDL artifacts should be named. It allows you to e.g. strip underscores, apply ProperCase/pascal casing to class and member names, control pluralization and singularization, remove (or add) prefixes/suffixes and apply a whole set of other naming rules to ensure that the final classes and member properties conform to .net naming conventions (or your own naming conventions) even if the database objects don’t.

The naming rule settings are applied by the Model Comparer only when adding entities or members to the CSDL portion of the model. For renaming existing CSDL entities and members, the add-in has a separate feature that use the same set of settings but allows renaming existing entities and entity members.

What’s next?

The Model Comparer is a brand new feature in Huagati DBML/EDMX Tools, and it will get new features and improvements in various areas in the near (and far) future. Besides general usability improvements, the next version (1.93) will introduce new reporting features. More on this when 1.93 is released.

Want to try it?

If you want to try out the Model Comparer, all you need to do is head over to http://www.huagati.com/dbmltools/ and download the Huagati DBML/EDMX Tools add-in.

After downloading, click on the ‘trial license’ link to get a trial license, fill out your details, and you will receive a license key that will enable you to use the add-in for free for 20 days. If you’re happy with it and want to continue using it after that, please buy a license (or subscribe for only $15/month/user) to support further development of this and other features.

The add-in includes the Model Comparer for EF4 as well as a whole lot of other useful extensions to the Entity Framework and Linq-to-SQL designers in Visual Studio 2010 and 2008. (Note: Some add-in features such as the EF4 Model Comparer are only available under VS2010)

Introducing the Model Comparer for Entity Framework v4

2010-07-30T17:16:00.001+07:00

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

2010-07-01T16:49:00.001+07:00

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)

2010-06-09T17:30:00.001+07:00

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:

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.
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, and is included in the installer that can be downloaded from http://huagati.com/L2SProfiler/

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

2009-10-05T11:13:00.004+07:00

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

2009-10-04T09:19:00.006+07:00

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

2009-08-07T13:00:00.007+07:00

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

2009-07-30T13:33:00.005+07:00

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.

Profiling Linq-to-SQL applications

2009-06-24T17:50:00.002+07:00

Profiling - why?

When developing database driven applications - especially apps with many users and different usage patterns - database performance is key to application performance and the database is often the 'resource bottleneck' when it comes to scaling applications to cope with a larger number of users.

If a certain operation spends a bit more time, CPU, and other resources client side it is often not as big of a matter scalability-wise as if it spends time or resources database-side/server-side. As the number of users in a large system grow, the impact of query costs - and savings of reducing it - is often the single most important factor to look at to improve performance and scalability, and the cheapest way to improve scalability.

Additionally, a production environment will often differ from test/development in ways that can affect how queries behave:

Applications are often used different in a production environment than what developers and designers envisioned; users may refresh 'screen x' twice as often as was envisioned during design and development, or use search filters in search screen differently than anyone thought.
SQL Server will often execute queries differently on different systems; depending on data volumes, available resources, system load, system and db configuration, and many other factors.

Because of this, runtime profiling live production systems can sometimes reveal bottlenecks and/or room for improvements/optimization that would not show up during development/test/QA, or on dev/test systems.

Profiling tools

There are many great tools available for investigating database performance at runtime / in production environments. The Microsoft tool stack offer some nice stuff for Linq-to-SQL and SQL Server based apps:

SQL Server Profiler is a really great tool that comes with the management tools for SQL Server (Standard edition and up, unfortunately it not available in Express edition).
Linq-to-SQL has a built in logging feature that allows executed queries to be written to log files by simply attaching a textwriter to the datacontext object.

To take this a step further, I have decided to throw a new tool onto the stack for L2S developers: a brand new profiler specifically targeting Linq-to-SQL applications.

But before we take a look at the L2S Profiler, a brief look at what comes out-of-the-box with SQL Server and Linq-to-SQL, and then what the L2S Profiler brings to the table compared to the existing tool stack.

SQL Server Profiler

Although SQL Server Profiler goes a long way when it comes to pinpointing what queries are causing the most stress db-side, it provides information from the SQL Server side of things only. It is built as a generic tool for all kinds of SQL Server profiling - not only queries but targeting other kinds of database activity as well. But a missing part is the application side of it: where did that query come from, what [user actions/calls] led to it being executed etc. In a large application with many developers involved this can make it time consuming to determine another important aspect who's code is responsible for hitting the database too frequently, or with too heavy queries, etc.

Add to that that SQL Server Profiler is made for DBAs, sys-admins, developers, and must be manually set up in a production environment. It can't be controlled from within applications, and it requires the person using it to know what they're looking for. It requires access to and privileges on the database server which is not always available in hosted environments, or in locked-down customer environments. It won't record execution plans so if you're reviewing a profiler log from a customer's system on your own system there is no way of determining what execution plan SQL Server used when it was executed.

Don't get me wrong: SQL Server profiler is an invaluable tool that I use on almost a daily basis, but there are some things it won't do that I would want it to.

Linq-to-SQL's DataContext.Log

So what about the built-in logging in Linq-to-SQL? It is a great feature as it stands, with a few lines of code you can get all SQL statements and queries generated by Linq-to-SQL written to a log file. Unfortunately you will then need to sort through them and manually determine which ones are a cause for concern and which ones are not. Time consuming to say the least as soon as query volumes creep up, and finding what queries can/need to be optimized quickly becomes a forest vs trees situation. Great for debugging locally, but beyond that at least I need more data and more filtering options.

Getting more into details

What if we are just interested in the where/what/who aspects of say...

Queries that hit a specific table?
Queries that do more than n I/O operations / page reads?
Queries that take longer than n milliseconds to execute?
Queries that generate specific SQL Server warning messages?
Queries that result in scans?

Finding those queries by hand in a large text file isn't going to be easy. Spotting them in SQL Server Profiler is easier provided that all the requirements for using it in the first place are satisfied. On the other hand, SQL Server Profiler won't give us the where/what/who; where in the application it came from, what caused it to run, or who'se code generated it.

Add to that that we may want to control the profiling/logging from within the application by for example app settings. If we want to profile just a specific part of an application in a runtime environment, and filter out only queries that are costly then the combination of SQL Server Profiler, SQL Server Management Studio and the out-of-the-box logging capabilities of Linq-to-SQL can add quite a bit of manual work to map the logs together.

Introducting the Huagati Linq-to-SQL Profiler

So, to bring the .net application side together with the database performance side of things, I put together the Linq-to-SQL Profiler. It consists of two main components:

A runtime component that is referenced from within an application and that can be distributed with your applications. It hooks into a combination of the Linq-to-SQL built in logging, the underlying database connections, and SQL Server's own features for retrieving I/O information, timings, execution plans etc, and combines that information into log files. It also has some advanced (and customizable) filtering options so you can instruct it to log only queries that fulfill certain criteria.
A log viewer that read the log files generated by the runtime component, provides additional filtering, sorting (which is not available in SQL Server Profiler), and shows not only what query was executed by also the managed call stack that caused to to run, SQL Server I/O and timings etc. If execution plan logging was enabled in the runtime component it also provides quick links to bring up the execution plan as it stood on the machine where the query was executed, from when it was executed.

The runtime component

The runtime component is a small DLL that you add as a reference in your Linq-to-SQL application. It won't do anything just by being referenced, but it adds some new capabilities to the Linq-to-SQL DataContext. It can be distributed freely together with your app, provided that you have a license for the profiler.

Using it is very simple; it has two methods (with a couple of overloads) that can be used to instruct it to start profiler logging (datacontext.BeginProfiling), and to end profiler logging (datacontext.EndProfiling). Depending on your usage scenario you can either call those methods inline from your app code or you can extend the partial DataContext class generated by the Linq-to-SQL designer to call the profiler logging methods whenever a datacontext is created/used.

The following code samples shows a couple of usage scenarios which can be used to use for example app settings to determine if logging should be enabled or not, and what logging options to use.

Example 1: A profiler enabled datacontext partial class in C#

Example 2: Inline profiling with C# as an alternative to profiling-enabled DCs

Example 3: A profiling enabled datacontext in VB.NET

Example 4: Inline profiling in VB.NET as an alternative to profiling-enabled DataContexts

If you want to read more about the profiler runtime component API, check out the online help over at http://www.huagati.com/L2SProfiler/runtimehelp

The profiler viewer

The profiler viewer is a windows application that read the log files generated by the Linq-to-SQL Profiler runtime component. It has filter options and sorting to make it easier to find the 'worst offenders' or log entries that match more specific criteria than was used during the runtime logging.

The profiler's screen is divided into two parts; the upper part shows all queries from the log (minus those filtered out by any filters set in the viewer) and the lower part shows the details; the query itself, the managed call stack, I/O and timings etc. The I/O and timing data is accompanied by visual indicators showing the relative impact of a query. The thresholds used by the visual indicators can be configured in the application settings.

The lower part of the screen can also be customized; the log files contain a lot more information than what is shown in the default view and the rendering of the lower part is done using XSLT style sheets that can be switched in the application settings dialog. This allows developers to create their own detail views focusing on the specific information they're interested in.

The profiler settings dialog

Additional details such as the execution plan diagram, the source code involved, parameterized and 'sp_execute'-wrapped versions of the queries etc are available as hyperlinks in the detail view, but the same information can of course be visualized directly in a customized detail view template.

Taking it for a test spin

If you would like to take the profiler for a test spin, head over to the product page over at http://www.huagati.com/L2SProfiler/ , where you can download it and get a free 45-day trial license. After the trial expires I hope you're happy with it and want to continue using it. If so, please support continued development by purchasing a license (or a pile of licenses for your team :) ).

If you have any questions or concerns about the profiler, feel free to send an email to support@huagati.com, ask a question in the support forum (http://forum.huagati.com/forum4-huagati-linqtosql-profiler-support.aspx ), or drop a comment here on my blog.

Future features

There are of course new features are planned for the profiler, these will be made available as they're completed:

A centralized logging server that you can run to write logs to a database. It will have a web-based frontend very similar to the windows version of the profiler logger but is better suited for team environments where many people may want to access and work with the same logs.
More templates for the detail view in the profiler viewer
Additional filters; both runtime- and design time filters to help keep the log files focused on the interesting bits only.
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.

Fin.

New blog

2009-04-30T10:42:00.001+07:00

Welcome to my new blog.

I recently took my old blog offline, for a number of reasons:

It was becoming unstructured; lots of content that didn't fit the title of the blog etc.
It was a huge spam magnet (comment spam)
I didn't feel comfortable having the whole wordpress/php/mysql thing running on my server.

Some of the old content will probably be reposted here but I'll focus more on new content. Let me know if you miss something from the old blog and I'll look into migrating it.