EDM QueryViews vs Defining Queries (and read-only views)

In a recent ADO.NET forum post, someone asked how to do read-only views of data. There were two responses – use a QueryView and use a Defining Query.

What’s the difference?

A Query view is an Entity SQL Query that exists right in your mapping layer. It allows you to solve two problems. THe first is creating complex queries that you don’t want your coders to have to worry about building. You can just build the query in the mapping layer, expose the resulting entity in the conceptual layer and the classes that the developers code & query against will just be there for them to use.

The second benefit is that you can use this to limit access to particular data from the database. For example, if you have a table which has GUIDs or hashed passwords in it, you may not want those to ever be surfaced in the application. You can “filter those out”, by creating a QueryView. FWIW, you could also do this by just deleting the columns in the store layer definition.

The QueryVIew then queries against the storage schema, not directly against your database, and then the results are output to an entity in your conceptual layer. So in effect, it is a different way of mapping between your store layer and your conceptual layer.

The QueryView is part of the EntitySet mapping and looks like this:

  <EntitySetMapping Name=”Categories”>
      <QueryView>
        SELECT VALUE Microsoft.CDP.Samples.Northwind.Category(C.CategoryID, C.CategoryName, C.Description)
          FROM dbo.Categories as C
      </QueryView>
    </EntitySetMapping>

This simple query (note the syntax; it’s an Entity SQL query) is replacing the mapping for Categories. It gets 3 columns from the Category entity in the store model (dbo) and maps the results to the conceptual entity, Category. Remember that I have created a read-only view, so you might not want to use this to just filter out columns if you still need to do updates and want to do them using the mappings (vs. using sprocs to do the updates).

You can also invent new queries and new entities to go along with them.

Defining Query

I love that MIke Pizzo refers to Defining Query as “the ultimate escape hatch“.

Defining Queries are defined in the storage layer. They are really nothing more than database views – directly against the database. So you have a really complicated query that you can’t describe with LINQ or with Entity SQL but you can describe it in TSQL, but and the dba is not around, unable or unwilling (for a variety fo very good reasons) to add a new view into the database for you, you can just create it in the storage layer. In fact, when you run the EDM Wizard against a database and it finds views, the wizard represents these views in the store layer as Defining Queries.

There are two parts to a Defining Query.

The query itself which is an EntitySet in the store model and an Entity in the store model that describes/defines the result set.

The rest of the model then treats the query as it would any other table. You have entities in your conceptual layer and they map to the entity that describes the result set of the Defining Query.

Here’s what one looks like.

<EntitySet Name=custview EntityType=AdventureWorksLTModel.Store.custview>
 
<
DefiningQuery>SELECT [custview].[CustomerID] AS [CustomerID],
  
[custview].[FirstName] AS [FirstName],
  
[custview].[LastName] AS [LastName],
  
[custview].[CompanyName] AS [CompanyName]
  
FROM [dbo].[custview] AS [custview]
 
</DefiningQuery>
</
EntitySet>

And the entity that you will map to

<EntityType Name=custview>
  <
Key>
   <
PropertyRef Name=CustomerID />
   <
PropertyRef Name=FirstName />
   <
PropertyRef Name=LastName />
  </
Key>
  <
Property Name=CustomerID Type=int Nullable=false StoreGeneratedPattern=identity />
  <
Property Name=FirstName Type=nvarchar Nullable=false MaxLength=50 />
  <
Property Name=LastName Type=nvarchar Nullable=false MaxLength=50 />
  <
Property Name=CompanyName Type=nvarchar MaxLength=128 />
</
EntityType>

As I said above, you can then have an entity in your conceptual layer that just maps to this entity and nobody will ever know the difference. Which beg the reminder that this is read-only!

  Sign up for my newsletter so you don't miss my conference & Pluralsight course announcements!  

7 thoughts on “EDM QueryViews vs Defining Queries (and read-only views)

  1. I have a question on Query View. If I want to setup an alias on a column then how would I do that?

    Here is my situation. I have a table

    MyLookupTable

    MyLookupID,

    MyLookUpDescription

    MyLookupActive

    MyLookupType2Description

    MyLookupType3Description

    I want it to map to an object called lookup and will have the following properties

    Id, Code, descripton

    ID = MyLookupID

    Code = MyLookUpDescription

    Description = MyLookUpDescription

    I tried to use query view. However, I am not able to do any alias for the columns

  2. I setup a DefiningQuery like you have it in this posting and in your new book (page 433) and every time I update my model the EntitySet with the DefiningQuery get erased. Am I missing something? Please tell me I don’t have to redo it every time….Thanks!

  3. Hi Dan

    Yes, you are probably missing the frequent warnings in my book that when you update the model, all manual modifications to the SSDL are overwritten.Sorry and… "don’t shoot the messenger". 😉

  4. Wow, quick response! I was afraid you were going to say that…although it does make sense.

    Maybe there’s another way. I have a view in the database where all fields are nullable and no primary key. Whenever I try and import it into EF it yells at me about no PK and it can’t infer one. Is there a way I can get it to import it and tell it the primary key?

  5. Oy, I just spent a long time manually setting up a view that aggregates some data for me and then when I updated the model it destroyed all my work 🙁 How can we specify a PK on a view???

Leave a Reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.