DefiningQuery Element in Entity Framework

Introduction

There are times when we need to do things in Entity Framework that aren't supported by its designer. One of those things is using the DefininqQuery element which creates a view in the model that can be used to project custom entities. In order to understand the article, you should be familiar with the Entity Data Model XML schema types. You can go to the following link if you aren't familiar with the EDM.

DefiningQuery Element

DefiningQuery elements are defined in the Storage Schema Definition Language (SSDL).
These elements are automatically created when you map a database view in the EDM wizard. 
The elements are read only projection of data and therefore read only like database views.
By now, you probably ask yourself why I'm writing about them at all.
The great thing about DefiningQuery elements is that they can help you create every projection that you would like. After the creation of the element, you can use the EDM designer to create entities to handle the created view.
So what is great about element that gives us a read only data?
By connecting stored procedures to the created entity on the Conceptual Schema Definition Language (CSDL), you can add write functionality to the defined query.
The drawback of this method is that you will need to update the SSDL manually with an XML editor. Also, you don't have intellisense while writing the query so I suggest that you try it first in the database management studio and then move it to the SSDL.
You should be very careful when you define queries in the SSDL or edit the model manually!

DefiningQuery Element Example

In the next example, I'll continue using the database and example from my previous post. The database schema is shown in the next figure:

The current state of the designer is shown in the next figure:

How to define a DefiningQuery element?

First, write the query for the data projection.
The query I'm going to use will select details from two tables - Employees and Companies.

SELECT e.EmpolyeeID AS EmpolyeeID,
       e.EmployeeFirstName AS EmployeeFirstName, 
       e.EmployeeLastName AS EmployeeLastName, 
       c.CompanyName AS CompanyName 
FROM Employees AS e INNER JOIN Companies AS c ON (e.CompanyID = c.CompanyID)

Open the edmx file with XML editor and look for the SSDL area. Insert a new EntitySet to the SSDL with the DefiningQuery element. In the example, I inserted a new entity set with the name of EmployeeWithCompany:

<EntitySet Name="EmployeeWithCompany" 
	EntityType="TestLINQModel.Store.EmployeeWithCompany">
   <DefiningQuery>
      SELECT e.EmpolyeeID as EmpolyeeID,
      e.EmployeeFirstName AS EmployeeFirstName,
      e.EmployeeLastName AS EmployeeLastName,
      c.CompanyName AS CompanyName
      FROM Employees AS e INNER JOIN Companies AS c ON (e.CompanyID = c.CompanyID)
   </DefiningQuery>
</EntitySet>

After you inserted the new EntitySet, you need to provide a new entity type which is called in my example EmployeeWithCompany. You can see that the entity set reference this type.
The entity type should look like:

<EntityType Name="EmployeeWithCompany">
   <Key>
      <PropertyRef Name="EmpolyeeID"/>
   </Key>
   <Property Name="EmpolyeeID" Type="int" 
	Nullable="false" StoreGeneratedPattern="Identity" />
   <Property Name="CompanyName" Type="nvarchar" MaxLength="100" />
   <Property Name="EmployeeFirstName" Type="nvarchar" MaxLength="50" />
   <Property Name="EmployeeLastName" Type="nvarchar" MaxLength="50" />
</EntityType>

Pay attention to define the property elements exactly as their definition in the database (type and constraints). It's done manually and therefore you can have errors.

After these operations, the projection of the data is ready to use in the CSDL. Open the designer and create a new entity with the EmployeeWithCompany name. Add four properties to the entity to match the properties of the entity type that was defined in the SSDL. Your designer surface should look like the next figure:

After the creation of the entity, we need to connect it to the created view and its properties. You do it by selecting the view name in the Tables column of the Mapping View and by mapping the relevant entity set properties to the entity properties. The result is as follows:

Build the solution and you are set to use the new read only entity.

Summary

The DefiningQuery element is a very useful and powerful way to customize your Entity Framework model. In order to use it, you need to manually create an entity set that holds the DefiningQuery and an entity type for the returning type. In the article, I showed a simple example for doing such a thing and create a read only entity. In order to make it a writable entity, you will need to create a stored procedure for the Create/Update/Delete operations.

History

• 27^th July, 2010: Initial post