QueryView Element in Entity Framework

March 12, 2009

QueryView Element in Entity Framework

In a very old post that I QueryView Element in Entity Framework
wrote I explained what
is the DefiningQuery
element and how to use it.
In this post I’m going to
explain what is the
QueryView element, how to use it and the difference between
using a DefiningQuery and using a QueryView.

What is the QueryView Element?

QueryView element is defined inside an EntitySetMapping in the
MSL part of the EDM schema and it is a read-only mapping between an
entity in the CSDL and an entity/ies in the SSDL. You define the QueryView
mapping with an E-SQL query inside the EntitySetMapping. That query is
evaluated against the storage model and the result set can be used as an
entity in the conceptual model.

How to Use a QueryView?

In the following example I’m going to use the following database definition:
Database Diagram 
When we use the Entity Framework Wizard the following conceptual
model is created:
Entity Designer Diagram

We want to use a QueryView element and to have an entity that
for each company has its country name inside of it.
How we can achieve it?

Step 1
Add the CountryName from the Country entity to the Company entity
and then remove the Country entity from the conceptual model.
The result should look like:
Entity Designer Diagram After Country Removal 

Step 2
Open the model in Xml editor and in the EntitySetMapping of
Company replace the mapping with a QueryView element like in
the following example:

<EntitySetMapping Name="CompanySet">
  <QueryView>
    SELECT VALUE EntityFrameworkExampleModel.Company(comp.CompanyID, comp.CompanyName, coun.CountryName)
    FROM EntityFrameworkExampleModelStoreContainer.Companies AS comp
    INNER JOIN EntityFrameworkExampleModelStoreContainer.Countries AS coun
    ON comp.CountryID = coun.CountryID
  </QueryView>
</EntitySetMapping>

Step 3

Test the result. I used the following code to fetch all the

companies and print them to the console:

using (EntityFrameworkExampleEntities context = new EntityFrameworkExampleEntities())
{
    foreach (var comp in context.CompanySet)
    {
        Console.WriteLine("{0} {1}", comp.CompanyName, comp.CountryName);
    }
 
    Console.ReadLine();
}

The result is show in the next figure:

Running Result 

QueryView Restrictions

Once you have created one QueryView in your MSL, every related EntitySet

and AssociationSet needs to be mapped using QueryView too.

This means that you need to use QueryViews a lot if you have a very

connected entity in your model which makes it hard to use the QueryView 
feature.

The Difference Between DefiningQuery and QueryView

A QueryView element lives inside the mapping definition and provides a

way to use E-SQL to get a read only view on top of the storage model. A very

practical use for it is to limit access to particular data from the database. On

the other hand, DefiningQuery is defined in the storage definition and it’s 
only a read only database view. When you import database views to your model

using the Entity Framework Wizard they are generated as a DefiningQuery.

Summary

Lets sum up, in the post I explained what is the QueryView element and

how to use it. Also, I explained the difference between QueryViews and

DefiningQueries. Like DefiningQueries, QueryViews are very useful

for implementing a conceptual model which apply to our needs but they

are harder to use.

DotNetKicks Image
Add comment
facebook linkedin twitter email

Leave a Reply

Your email address will not be published.

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

*