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!