Using LINQ and EntityFramework, Measuring and analyzing the LINQ effectiveness

Some Entity Framework Components

When you use something like the extensive “Entity Framework” or any other ORM (Object Relational Mapping) tool, the process of writing sophisticated database driven applications becomes a lot easier.
It eliminates most of the data access code used to be required to create object-based data applications, and contains tools that makes it possible to create object-oriented data applications from an existing database, by using .edmx files that describes the CLR (Common Language Run-time) objects and these can be “extracted” from any existing relational database that the Entity Framework has a driver for.

This enables developers to program directly against data types defined at the conceptual level, presented as regular object instances.

But.
The responsibility to create clean queries and dispose the resources that’s being used, are just as important as it was in more “old fashioned” database connected applications that does all the SQL manually.

Many people assume that, as long as the application compiles and runs without complaining loudly, everything is probably done correctly, right?
This is .Net we’re talking about, so if something was wrong I would probably get a warning or something, right?

Sadly life is not that simple in this case.

The risk of introducing data-access “anti-patterns” in your code that may decrease the performance of your application and greatly slow down your organization’s database server is highly possible, and  actually quite likely to happen if you’re new to ORM and the Entity Framework in particular.

How Can You Measure the LINQ query’s Efficiency?

To reduce the chances of creating inefficient ORM based applications, we need some way to easily (or it won’t be used) analyze exactly what goes on in the EF brain when it executes our code.

This post focuses on a product called LINQ Insight by DevArt, that addresses this problem (and a lot of other LINQ related problems) and may be downloaded for free from the DevArt site:

Download LINQ Insight Standard

NB! The FULL functionality is free for 30 days, before it changes to the more restricted, but highly useful Free Edition.

Data access anti-patterns decreases performance greatly and must be avoided.

When you write applications using for example LINQ to Entities with the Entity Framework, what goes on behind the scenes is something like:

1) The Entity Framework “Mapped” Objects are created with a ObjectQuery() method, run against an existing ObjectContext instance.

The ObjectContext instance is a container for in-memory objects and metadata extracted from the relational database.
It manages the object identities and states, keeps a pointer to original and current values of the objects properties, and tracks changes made to each object in its cache store.

The ObjectContext provides the connection and metadata information that is required to compose and execute queries, and should therefore be set up in advance.

2) Entity Framework components like the ObjectQuery class are compiled behind the scenes, and generate “regular” SQL statements that does the “actual” query against the database.

The queried data is returned as entity objects, most often as anonymous class instances, and the application works with these objects instead of directly querying the SQL database.
This is an important point to understand.
Using LINQ in this way, to offload the database server is a fundamental aspect of ORM frameworks.

Constructing an ObjectQuery Instance

The ObjectQuery returns a collection of zero or more typed entities.  The ObjectQuery generic class implements the IQueryable generic interface, whose methods enable LINQ queries to be incrementally built. You can also let the compiler infer the type of entities by using the C# var keyword, which is something that simplifies specific query situations considerably.

Instances of the ObjectQuery generic class, which implements the generic IQueryable interface, serve as the data source for LINQ to Entities queries. In a query, you specify exactly the information that you want to retrieve from the data source. A query can also specify how that information should be sorted, grouped, and shaped before it is returned. In LINQ, a query is stored in a variable. This query variable takes no action and returns no data; it only stores the query information. After you create a query you must execute that query to retrieve any data.

// Call ctor with query for "persons" and the named ObjectContext
var personsQuery = new ObjectQuery("Persons", context);

3) You can get access to the entities via the reference and collection properties, and this in turn produces additional SQL queries to get the necessary data.

4) When your application finishes working out what entities are needed, the “mapping” process of EF is more or less done.

BUT: if there are any changes to the queried entities, the application will have to save them to database somehow.
It does this by transforming your LINQ, like SubmitChanges() to “regular” SQL statements, like SELECT, UPDATE etc.

All of this happens without you being able to “see” exactly what’s going on at a “lower” level. That’s kinda the idea, to abstract away the unnecessary details from the user, and let you think about designing the flow of the application, not the technicalities used to make things work.

Though all these actions are performed by Entity Framework run-time, and are transparent for the application that works with entities, they may be complex and affect application performance greatly. That’s why you probably need a good Entity Framework profiler to create efficient applications using Entity Framework for data access. Entity Framework profiler in LINQ Insight really profiles ORM events and allows you to see how much time LINQ statement or SubmitChanges call takes to execute as a whole even if it generated multiple SQL statements.

Example Application

Let’s show how to profile Entity Framework applications on a simple example. We have used the widely used NorthwindEF sample database for our example, and generated the model for it with EDM wizard using default settings. The application is a simple console application with the following code:

Advertisements