Export (0) Print
Expand All

How to: Add a Defining Query

[This topic is pre-release documentation and is subject to change in future releases. Blank topics are included as placeholders.]

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.

Cc982038.note(en-us,VS.110).gifNote:
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 and Walkthrough: Mapping an Entity to Stored Procedures.

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

  1. 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.

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.

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.

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.

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.

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

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

Show:
© 2014 Microsoft