How to: Add a Defining Query (Entity Framework)

This topic describes how to add a defining query and a corresponding conceptual entity type to an .edmx file. A defining query allows you to execute an SQL statement that is specified in the DefiningQuery element of an .edmx file. For more information, see DefiningQuery Element (SSDL). A defining query is commonly used to provide functionality similar to that provided by a database view, but the view is defined in the .edmx file, not the database. The conceptual entity type is used to surface data exposed by the defining query in the conceptual layer.

Note
Any changes made to the storage model, including defining queries, will be overwritten when you run the Update Model Wizard.

Parameterized queries cannot be executed as defining queries. By default, data that is surfaced by using a defining query is read-only data. However, the data can be updated by mapping the insert, update, and delete functions of the entity type that surfaces the data to stored procedures. For more information, see How to: Map Modification Functions to Stored Procedures (Entity Data Model Tools) and Walkthrough: Mapping an Entity to Stored Procedures (Entity Data Model Tools).

The following procedures provide a high-level outline of adding a defining query and a conceptual entity type to an .edmx file. The example that follows provides more detail about steps in the procedures.

The procedures assume that you have a valid .edmx file open in Visual Studio.

Adding a Defining Query

To add a defining query

1. Add an EntitySet element to the SSDL section of the .edmx file. Specify only the Name and EntityType attributes of the EntitySet element. For more information, see EntitySet Element (SSDL).

2. Add a DefiningQuery element to the newly added EntitySet element. Specify the SQL statement to be executed in the body of the DefiningQuery element. For more information, see DefiningQuery Element (SSDL).

3. Add an EntityType element to the SSDL section of the .edmx file. This EntityType element must describe the columns that are returned by the SQL statement in the DefiningQuery element. For more information, see EntityType Element (SSDL).

Adding an Entity Type to the Conceptual Model

To add a conceptual entity type

1. Add an EntitySet element to the CSDL section of the .edmx file. For more information, see EntitySet Element (CSDL).

2. Add an EntityType element to the CSDL section of the .edmx file. The Name attribute should have the same value as the Name attribute of the EntitySet element in the preceding step. The properties of the entity type should map to the data returned by the SQL statement that is specified in the DefiningQuery element of the procedure above. For more information, see EntityType Element (CSDL).

Mapping the Conceptual and Storage Entity Types

To map conceptual and storage entity types

• Add an EntitySetMapping element to the Mapping section of the .edmx file that maps the conceptual and storage entities in the previous two procedures. For more information see EntitySetMapping Element (MSL) and Mapping a Conceptual Model to a Storage Model.

Example

The following example adds a defining query to the School.edmx file to surface information about student grades with a GradeReport entity type. The School.edmx file can be generated by following the steps in the Generating the School Entity Data Model topic of the Entity Framework Quickstart (Entity Framework).

To add a defining query, you first add the following EntitySet element (which contains a DefiningQuery element) to the SSDL section of the .edmx file. Note the following:

• Only the Name and EntityType attributes of the EntitySet element are specified.

• The fully-qualified name of the entity type is used in the EntityType attribute.

• The SQL statement to be executed is specified in the DefiningQuery element.

<EntitySet Name="GradeReport"
 EntityType="SchoolModel.Store.GradeReport" >
  <DefiningQuery>
    SELECT CourseID, Grade, FirstName, LastName
    FROM StudentGrade
    JOIN
    (SELECT * FROM Person WHERE EnrollmentDate IS NOT NULL) AS p
    ON StudentID = p.PersonID
  </DefiningQuery>
</EntitySet>

Next you add the following EntityType element to the SSDL section of the .edmx. file. Note the following:

• The value of the Name attribute corresponds to the value of the EntityType attribute in the EntitySet element above, although the fully-qualified name of the entity type is used in the EntityType attribute.

• The property names correspond to the column names returned by the SQL statement in the DefiningQuery element (above).

• In this example, the entity key is composed of three properties to ensure a unique key value.

<EntityType Name="GradeReport">
  <Key>
    <PropertyRef Name="CourseID" />
    <PropertyRef Name="FirstName" />
    <PropertyRef Name="LastName" />
  </Key>
  <Property Name="CourseID"
            Type="int"
            Nullable="false" />
  <Property Name="Grade"
            Type="decimal"
            Precision="3"
            Scale="2" />
  <Property Name="FirstName"
            Type="nvarchar"
            Nullable="false"
            MaxLength="50" />
  <Property Name="LastName"
            Type="nvarchar"
            Nullable="false"
            MaxLength="50" />
</EntityType>

To add an entity type to the conceptual model, you first add the following EntitySet element to the CSDL section of the .edmx file. Note that the fully-qualified name of the entity type is used in the EntityType attribute.

<EntitySet Name="GradeReport" EntityType="SchoolModel.GradeReport" />

Next you add the following EntityType element to the CSDL section of the .edmx file. Note the following:

• The value of the Name attribute corresponds to the value of the EntityType attribute in the EntitySet element above, although the fully-qualified name of the entity type is used in the EntityType attribute.

• The property names correspond to the column names returned by the SQL statement in the DefiningQuery element (above).

• In this example, the entity key is composed of three properties to ensure a unique key value.

<EntityType Name="GradeReport">
  <Key>
    <PropertyRef Name="CourseID" />
    <PropertyRef Name="FirstName" />
    <PropertyRef Name="LastName" />
  </Key>
  <Property Name="CourseID"
            Type="Int32"
            Nullable="false" />
  <Property Name="Grade"
            Type="Decimal"
            Precision="3"
            Scale="2" />
  <Property Name="FirstName"
            Type="String"
            Nullable="false"
            MaxLength="50"
            Unicode="true"
            FixedLength="false" />
  <Property Name="LastName"
            Type="String"
            Nullable="false"
            MaxLength="50"
            Unicode="true"
            FixedLength="false" />
</EntityType>

To map the conceptual and storage entity types, you add the following EntitySetMapping element to the Mapping section of the .edmx file.

<EntitySetMapping Name="GradeReport">
  <EntityTypeMapping TypeName="IsTypeOf(SchoolModel.GradeReport)">
    <MappingFragment StoreEntitySet="GradeReport">
      <ScalarProperty Name="CourseID" ColumnName="CourseID" />
      <ScalarProperty Name="Grade" ColumnName="Grade" />
      <ScalarProperty Name="FirstName" ColumnName="FirstName" />
      <ScalarProperty Name="LastName" ColumnName="LastName" />
    </MappingFragment>
  </EntityTypeMapping>
</EntitySetMapping>

The GradeReport entity type is now added to the School model. Note that data surfaced through the GradeReport entity type is read-only.

See Also

Concepts

.edmx File Overview (Entity Framework)

Other Resources

Editing an .edmx File Manually (Entity Framework)