Click here to Skip to main content
Click here to Skip to main content

DefiningQuery Element in Entity Framework

, 26 Jul 2010
Rate this:
Please Sign up or sign in to vote.
DefiningQueries are a very useful elements that you can use in Entity Framework in order to create views from the model against your database. The article will explain the DefiningQuery element.

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:

Example Database

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

Entity Designer Diagram

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:

New Entity Designer Diagram

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:

Map View To Entity

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

  • 27th July, 2010: Initial post

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Gil Fink
Technical Lead sparXys
Israel Israel
Gil Fink is a web development expert and ASP.Net/IIS Microsoft MVP. He is the founder and owner of sparXys. He is currently consulting for various enterprises and companies, where he helps to develop Web and RIA-based solutions. He conducts lectures and workshops for individuals and enterprises who want to specialize in infrastructure and web development. He is also co-author of several Microsoft Official Courses (MOCs) and training kits, co-author of "Pro Single Page Application Development" book (Apress) and the founder of Front-End.IL Meetup. You can read his publications at his website: http://www.gilfink.net
Follow on   Twitter   Google+   LinkedIn

Comments and Discussions

 
GeneralParam PinmemberRatko26024-Aug-10 0:26 
GeneralRe: Param PinmemberGil Fink4-Aug-10 4:15 
Generalnice PinmemberMubi | www.mrmubi.com29-Jul-10 12:54 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Mobile
Web02 | 2.8.140827.1 | Last Updated 27 Jul 2010
Article Copyright 2010 by Gil Fink
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid