QueryView Element (MSL)

The QueryView element in mapping specification language (MSL) defines a read-only mapping between an entity type or association in the conceptual model and a table in the underlying database. The mapping is defined with an Entity SQL query that is evaluated against the storage model, and you express the result set in terms of an entity or association in the conceptual model. Because query views are read-only, you cannot use standard update commands to update types that are defined by query views. You can make updates to these types by using modification functions. For more information, see How to: Map Modification Functions to Stored Procedures.

The QueryView element can be a child of the EntitySetMapping element or the AssociationSetMapping element. In the former case, the query view defines a read-only mapping for an entity in the conceptual model. In the latter case, the query view defines a read-only mapping for an association in the conceptual model.

The QueryView element cannot have any child elements.

Applicable Attributes

The following table describes the attributes that can be applied to the QueryView element.

Example

The following example shows the QueryView element as a child of the EntitySetMapping element and defines a query view mapping for the Department entity type in the School Model.

<EntitySetMapping Name="Departments" >
  <QueryView>
    SELECT VALUE SchoolModel.Department(d.DepartmentID, 
                                        d.Name, 
                                        d.Budget, 
                                        d.StartDate)
    FROM SchoolModelStoreContainer.Department AS d
    WHERE d.Budget > 150000
  </QueryView>
</EntitySetMapping>

Because the query only returns a subset of the members of the Department type in the storage model, the Department type in the School model has been modified based on this mapping as follows:

<EntityType Name="Department">
  <Key>
    <PropertyRef Name="DepartmentID" />
  </Key>
  <Property Type="Int32" Name="DepartmentID" Nullable="false" />
  <Property Type="String" Name="Name" Nullable="false" 
            MaxLength="50" FixedLength="false" Unicode="true" />
  <Property Type="Decimal" Name="Budget" Nullable="false" 
            Precision="19" Scale="4" />
  <Property Type="DateTime" Name="StartDate" Nullable="false" />
  <NavigationProperty Name="Courses" 
                      Relationship="SchoolModel.FK_Course_Department" 
                      FromRole="Department" ToRole="Course" />
</EntityType>

Example

The next example shows the QueryView element as the child of an AssociationSetMapping element and defines a read-only mapping for the FK_Course_Department association in the School model.

<EntityContainerMapping StorageEntityContainer="SchoolModelStoreContainer" 
                        CdmEntityContainer="SchoolEntities">
  <EntitySetMapping Name="Courses" >
    <QueryView>
      SELECT VALUE SchoolModel.Course(c.CourseID, 
                                      c.Title, 
                                      c.Credits)
      FROM SchoolModelStoreContainer.Course AS c
    </QueryView>
  </EntitySetMapping>
  <EntitySetMapping Name="Departments" >
    <QueryView>
      SELECT VALUE SchoolModel.Department(d.DepartmentID, 
                                          d.Name, 
                                          d.Budget, 
                                          d.StartDate)
      FROM SchoolModelStoreContainer.Department AS d
      WHERE d.Budget > 150000
    </QueryView>
  </EntitySetMapping>
  <AssociationSetMapping Name="FK_Course_Department" >
    <QueryView>
      SELECT VALUE SchoolModel.FK_Course_Department(
        CREATEREF(SchoolEntities.Departments, row(c.DepartmentID), SchoolModel.Department),
        CREATEREF(SchoolEntities.Courses, row(c.CourseID)) )
      FROM SchoolModelStoreContainer.Course AS c
    </QueryView>
  </AssociationSetMapping>
</EntityContainerMapping>

Comments

You can define query views to enable the following scenarios:

• Define an entity in the conceptual model that doesn't include all the properties of the entity in the storage model. This includes properties that do not have default values and do not support null values.

• Map computed columns in the storage model to properties of entity types in the conceptual model.

• Define a mapping where conditions used to partition entities in the conceptual model are not based on equality. When you specify a conditional mapping using the Condition element, the supplied condition must equal the specified value. For more information, see Condition Element (MSL).

• Map the same column in the storage model to multiple types in the conceptual model.

• Map multiple types to the same table.

• Define associations in the conceptual model that are not based on foreign keys in the relational schema.

• Use custom business logic to set the value of properties in the conceptual model. For example, you could map the string value "T" in the data source to a value of true, a Boolean, in the conceptual model.

• Define conditional filters for query results.

• Enforce fewer restrictions on data in the conceptual model than in the storage model. For example, you could make a property in the conceptual model nullable even if the column to which it is mapped does not support null values.

The following considerations apply when you define query views for entities:

• Query views are read-only. You can only make updates to entities by using modification functions.

• When you define an entity type by a query view, you must also define all related entities by query views.

• When you map a many-to-many association to an entity in the storage model that represents a link table in the relational schema, you must define a QueryView element in the AssociationSetMapping element for this link table.

• Query views must be defined for all types in a type hierarchy. You can do this in the following ways:

  • With a single QueryView element that specifies a single Entity SQL query that returns a union of all of the entity types in the hierarchy.

  • With a single QueryView element that specifies a single Entity SQL query that uses the CASE operator to return a specific entity type in the hierarchy based on a specific condition.

  • With an additional QueryView element for a specific type in the hierarchy. In this case, use the TypeName attribute of the QueryView element to specify the entity type for each view.

• When a query view is defined, you cannot specify the StorageSetName attribute on the EntitySetMapping element.

• When a query view is defined, the EntitySetMapping element cannot also contain Property mappings.

See Also

Concepts

EntitySetMapping Element (MSL)
AssociationSetMapping Element (MSL)

Other Resources

CSDL, SSDL, and MSL Specifications
Modeling and Mapping