Entity Framework improving performance using compiled queries for subsequent identical queries

One of the features to improve performance with Entity Framework is the possibility to use Compiled Queries. If might for example have a LINQ query searching for products depending on the name variable that the users pass along. The query will get executed a lot, but ussually with a different parameter, depending on what product name the client wants to search for. The query construction the Entity provider creates remains the same, only the variable passed by the user changes. By using Entity Framework Compiled Queries, you can compile this query which will result that the query is being hold in a cache.

Subsequent executions of this query will get the query from the cache, instead of constructing the LINQ query to a Provider agnostic query. This saves on the overhead of constructing the query at runtime every time again. Stored procedures might be more interesting for performance increase, but that’s out of the scope of this post now.

We will work with the Product entity from the AdventureWorks database:

Entity Framework Compiled Queries

Continue reading


Entity Framework using existing poco objects with change tracking

I’ve recently been covering some Entity Framework topics like

01/02/2012: Entity Framework using self tracking entities in a multi-layered architecture
31/01/2012: Entity Framework using Partial classes to add business logic and validation to generated entities
31/01/2012: Entity Framework mapping insert, update and delete stored procedures and using stored procedures as functions
27/01/2012: Entity framework Code First with Entity Framework 4.1 and System.Data.Entity.DbContext
25/01/2012: Entity framework creating your ADO.NET entity data model first and creating your database from your model

One of the topics I wanted to cover today was using existing POCO classes with Entity Framework. However instead of covering this myself, I found a 3 part series on the ADO.NET team blog covering POCO objects with change tracking, which I will share instead:

POCO in the Entity Framework: Part 1 – The Experience
POCO in the Entity Framework : Part 2 – Complex Types, Deferred Loading and Explicit Loading
POCO in the Entity Framework : Part 3 – Change Tracking with POCO



Entity Framework using self tracking entities in a multi-layered architecture

When using a multi layered architecture, you will often find that the data is provided by back-end services. The client application will get the needed data through the back-end services. That way the data logic is decoupled from the client application and can be reused for other applications which also need to access the same data.

In the .NET world the back-end services are most commonly WCF services which provide data to multiple clients who need some of the data the WCF service exposes. Client applications will request data to the WCF service which will execute a query against the data store, in our case by Entity Framework, and transfer the entities to the client application.

One of the issues that might arise in a scenario like this, is that the Entity that is transfered from the back-end WCF service can no longer be tracked for changes when it is transferred to the client. The transferred entity is no longer within the scope of our ObjectContext at our WCF service, making it impossible for the ObjectStateManager in the ObjectContext to track the changes to the Entity that was returned. A solution to this scenario is working with Self Tracking Entities, entities who have logic provided in their code to do the change tracking themselves, instead of having the change tracking being done by the ObjectStateManager in the ObjectContext.

There are a few considerations you need to keep in mind to use Self-Tracking Entities:

  • Make sure that your client project has a reference to the assembly containing the entity types. If you add only the service reference to the client project, the client project will use the WCF proxy types and not the actual self-tracking entity types. This means that you will not get the automated notification features that manage the tracking of the entities on the client. If you intentionally do not want to include the entity types, you will have to manually set change-tracking information on the client for the changes to be sent back to the service.
  • Calls to the service operation should be stateless and create a new instance of object context. We also recommend that you create object context in a using block
  • When you send the graph that was modified on the client to the service and then intend to continue working with the same graph on the client, you have to manually iterate through the graph and call the AcceptChanges method on each object to reset the change tracker. If objects in your graph contain properties with database-generated values (for example, identity or concurrency values), the Entity Framework will replace values of these properties with the database-generated values after the SaveChanges method is called. You can implement your service operation to return saved objects or a list of generated property values for the objects back to the client. The client would then need to replace the object instances or object property values with the objects or property values returned from the service operation.
  • Self-tracking entities are not enabled to perform lazy loading.
  • When you change the relationship between objects by setting the foreign key property, the reference navigation property is set to null and not synchronized to the appropriate principal entity on the client. After the graph is attached to the object context (for example, after you call the ApplyChanges method), the foreign key properties and navigation properties are synchronized.

Entity Framework using Partial classes to add business logic and validation to generated entities

With Entity Framework you can use partial classes and partial methods to add business logic or override certain methods of the generated Entity Framework entities. You can find the default MSDN information about partial classes here :

In my project I have a ADO.NET Entity Data Model which looks as following:

Entity Framework ADO.NET Entity Data Model

If we look at the generated entity code for the entity Post in the designer code:

Entity Framework partial entities

Continue reading

Entity Framework mapping insert, update and delete stored procedures and using stored procedures as functions

Entity Framework, just as LINQ to SQL, allows you to define a stored procedure for the insert, update and delete of an entity, instead of letting the EntityProvider write a custom query to the database.

I have a database called BlogData that has 3 tables and 3 stored procedures for inserting, modifying and deleting Post data:

Entity Framework Stored Procedure

1. Mapping insert, update and delete stored procedures to Entity Framework

The stored procedures look like this. The insert procedure:

SQL Server stored procedure insert

Continue reading

Entity framework Code First with Entity Framework 4.1 and System.Data.Entity.DbContext

In the previous post I created my database from the ADO.NET Data Entity Model. You can find the post here:
Entity framework creating your ADO.NET entity data model first and creating your database from your model 

It is also possible to create your database from code first, meaning creating the database from your defined classes. In that case you don’t need to model your entities anymore in your ADO.NET Entity Data Model, but you just create the classes and properties you want to work with.

To be able to work with Entity Framework Code First you need to have Entity Framework 4.1 extension installed, as this update contains the features to work with Code First. You can download Entity Framework 4.1 here:

1. Entity Framework Code First with System.Data.Entity.DbContext

We will create a Console Application called “EntityFramework.CodeFirst“:

Entity Framework Code First

Add a reference to System.Data.Entity and a reference to EntityFramework (this is under extensions instead of assemblies).

Entity Framework Code First

Continue reading

Entity framework creating your ADO.NET entity data model first and creating your database from your model

In this post we will cover how to use Entity Framework by model first. This means we will create the  ADO.NET entity data model first that will contain menu’s from the restaurant, with the according dishes linked to it. After we have set up our data model in visual studio, we will create the database afterwards to match the model we created in visual studio. The most common way to the derive your ADO.NET entity data model from an existing database.

If you are looking for Entity Framework 4.1 Code First instead of Model First, you can find it here:
Entity framework Code First with Entity Framework 4.1 and System.Data.Entity.DbContext
We start by creating a Console Application called “EntityFramework.CodeFirst”.

Entity Framework code first

We will add an ADO.NET Entity Data Model to our project, called Restaurant.edmx

Entity Framework ADO.NET Entity Data Model

Continue reading

WCF REST service with ODATA and Entity Framework with client context, custom operations and operation interceptors

It has been a while since I’ve made a post. Real life needed some attention as well and I had to invest into some study into Entity Framework, which is one of the topics I have to master for the upcoming ADO.NET certification. During preparation I found some information on how to construct WCF REST services with ODATA exposing data directly from an entity framework datasource.

For some this might not be interesting, but I sure as hell find this an interesting topic, considering how interoperable and scalable REST services are. In this post I’ll run through the setup and configuration of a  Restfull WCF Data Service with Entity Framework.

I’ve written 2 post before on WCF REST Services, which could be interesting to browse through quickly to understand the basics:
WCF REST service with XML / JSON response format according to Content-Type header
WCF REST service operation with JSON and XML also supporting ATOM syndication feed format

If you are looking for building REST services, this can also be done by the ASP.NET Web API:
Building and consuming REST services with ASP.NET Web API and OData support

Quoted directly from the Open Data Protocol website:

The Open Data Protocol (OData) is a Web protocol for querying and updating data that provides a way to unlock your data and free it from silos that exist in applications today. OData does this by applying and building upon Web technologies such as HTTP, Atom Publishing Protocol(AtomPub) and JSON to provide access to information from a variety of applications, services, and stores. The protocol emerged from experiences implementing AtomPub clients and servers in a variety of products over the past several years.  OData is being used to expose and access information from a variety of sources including, but not limited to, relational databases, file systems, content management systems and traditional Web sites.

OData is consistent with the way the Web works – it makes a deep commitment to URIs for resource identification and commits to an HTTP-based, uniform interface for interacting with those resources (just like the Web).   This commitment to core Web principles allows OData to enable a new level of data integration and interoperability across a broad range of clients, servers, services, and tools.

For this post I used the AdventureWorks database that is provided by Microsoft. You can find it here:

After attaching the database to your local IIS SQL server, you should have something as following:

Adventureworks Entity Framework

Continue reading