How And Why To Use DefiningQuery Element

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 Intoroduction
Today'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 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.

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:

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 Come 
In 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.