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.

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:


18 Reasons to Use C# / .Net

18 Reasons to Use C# / .Net

(some of this also explain why C# > Java)

This list is by no means complete, but may be a helper for those that wonder if they should jump on the old Java Wagon or the shiny C#/.Net Wagon with dual suspension and enhanced body construction…

  • No pointers required! C# programs typically have no need for direct pointer
    manipulation (BUT you are free to drop down to that level if absolutely
  • Automatic memory management through garbage collection. Given this, C# does
    not support a delete keyword.
  • Formal syntactic constructs for classes, interfaces, structures, enumerations, and
  • The C++-like ability to overload operators for a custom type, without the
    complexity (e.g., making sure to “return *this to allow chaining” is not your
  • Support for attribute-based programming. This brand of development allows you
    to annotate types and their members to further qualify their behavior. For
    example, if you mark a method with the [Obsolete] attribute, programmers will
    see your custom warning message print out if they attempt to make use of the
    decorated member.
  • The ability to build generic types and generic members. Using generics, you are
    able to build very efficient and type-safe code that defines numerous placeholders
    specified at the time you interact with the generic item.
  • Support for anonymous methods, which allow you to supply an inline function
    anywhere a delegate type is required.
  • The ability to define a single type across multiple code files (or if necessary, as an
    in-memory representation) using the partial keyword.
  •  LINQ: Support for strongly typed queries used to interact with various forms
    of data.
  • Support for anonymous types that allow you to model the shape of a type rather
    than its behavior.
  • The ability to extend the functionality of an existing type (without subclassing)
    using extension methods.
  • Inclusion of a lambda operator (=>), which even further simplifies working with
    .NET delegate types.
  • An object initialization syntax, which allows you to set property values at the
    time of object creation.
  • Support for optional method parameters, as well as named method arguments.
  • Support for dynamic lookup of members at runtime via the dynamic keyword.
    This provides a unified approach to invoking members
    on the fly, regardless of which framework the member implemented (COM,
    IronRuby, IronPython, or via .NET reflection services)
  • Working with generic types is much more intuitive, given that you can easily map
    generic data to and from general System.Object collections via covariance and
  • More and more companies and projects are going for the Open Source Mono Project’s implementation to create true cross-platform applications, both on desktop and mobile platforms.
  • The current version of C#
    provides a pair of new keywords (async and await), which massively simplify multithreaded and
    asynchronous programming.

You’ll be the judge! Both Languages has their charm, but personally (I learned Java many years ago) I strongly prefer C#, as you probably reasoned from this article…