Gil Fink's Blog

May 14, 2008

How And Why To Use DefiningQuery Element

In the last post in the entity framework series I introduced the EDM designer. Today I’m going to show how to do things that the designer isn’t able to perform (for the ADO.NET‘s team notice). You should be familiar with the EDM XML schema types and the general elements before you start to read this post. If you didn’t read my post about the subject you can read it in the following link.

DefiningQuery Element IntoroductionToday’s topic will be the DefiningQuery element. DefiningQuery elements are defined in the SSDL. These elements are created when you map a database view in the EDM wizard. These mappings are read only a projection of data and therefore read only like database views. By now you probably ask yourself why I’m writing about these elements. The great thing about DefiningQuery elements are that they can help us create every projection that we like and with the EDM designer we can then create entities to handle the created view. So what is great about what I wrote if the element gives us a read only data? By connecting the created entity of the CSDL to stored procedures you can add a write functionality to the defined query. The drawback of this method is that you need to do it manually in the SSDL. 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 file. You should be very careful when you define queries in the SSDL!

DefiningQuery Element ExampleIn 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:

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:

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:

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

What is Next to ComeIn the next post I’ll show how you can map the entity to stored procedures to enable insert, update and delete operations and by that to unleash the power of DefiningQuery element.