entity-framework/ef6/modeling/designer/advanced/defining-query.md
This walkthrough demonstrates how to add a defining query and a corresponding entity type to a model using the EF Designer. A defining query is commonly used to provide functionality similar to that provided by a database view, but the view is defined in the model, not the database. A defining query allows you to execute a SQL statement that is specified in the DefiningQuery element of an .edmx file. For more information, see DefiningQuery in the SSDL Specification.
When using defining queries, you also have to define an entity type in your model. The entity type is used to surface data exposed by the defining query. Note that data surfaced through this entity type is read-only.
Parameterized queries cannot be executed as defining queries. 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 Insert, Update, and Delete with Stored Procedures.
This topic shows how to perform the following tasks.
To complete this walkthrough, you will need:
This walkthrough is using Visual Studio 2012 or newer.
Right-click the project name in Solution Explorer, point to Add, and then click New Item.
Select Data from the left menu and then select ADO.NET Entity Data Model in the Templates pane.
Enter DefiningQueryModel.edmx for the file name, and then click Add.
In the Choose Model Contents dialog box, select Generate from database, and then click Next.
Click New Connection. In the Connection Properties dialog box, enter the server name (for example, (localdb)\mssqllocaldb), select the authentication method, type School for the database name, and then click OK. The Choose Your Data Connection dialog box is updated with your database connection setting.
In the Choose Your Database Objects dialog box, check the Tables node. This will add all the tables to the School model.
Click Finish.
In Solution Explorer, right-click the DefiningQueryModel.edmx file and select Open With….
Select XML (Text) Editor.
Click Yes if prompted with the following message:
In this step we will use the XML Editor to add a defining query and an entity type to the SSDL section of the .edmx file.
<!-- SSDL content -->
<edmx:StorageModels>
<Schema Namespace="SchoolModel.Store" Alias="Self" Provider="System.Data.SqlClient" ProviderManifestToken="2008" xmlns:store="http://schemas.microsoft.com/ado/2007/12/edm/EntityStoreSchemaGenerator" xmlns="http://schemas.microsoft.com/ado/2009/11/edm/ssdl">
<EntityContainer Name="SchoolModelStoreContainer">
<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>
<EntitySet Name="Course" EntityType="SchoolModel.Store.Course" store:Type="Tables" Schema="dbo" />
<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>
[!NOTE] If later you run the Update Model Wizard dialog, any changes made to the storage model, including defining queries, will be overwritten.
In this step we will add the entity type to the conceptual model using the EF Designer. Note the following:
Open the model in the EF Designer.
Double-click the DefiningQueryModel.edmx.
Say Yes to the following message:
The Entity Designer, which provides a design surface for editing your model, is displayed.
As a result, the following elements were added to the CSDL section of the .edmx file.
<EntitySet Name="GradeReport" EntityType="SchoolModel.GradeReport" />
<EntityType Name="GradeReport">
. . .
</EntityType>
In this step, we will use the Mapping Details window to map the conceptual and storage entity types.
As a result, the EntitySetMapping element is added to the mapping section of the .edmx file.
<EntitySetMapping Name="GradeReports">
<EntityTypeMapping TypeName="IsTypeOf(SchoolModel.GradeReport)">
<MappingFragment StoreEntitySet="GradeReport">
<ScalarProperty Name="LastName" ColumnName="LastName" />
<ScalarProperty Name="FirstName" ColumnName="FirstName" />
<ScalarProperty Name="Grade" ColumnName="Grade" />
<ScalarProperty Name="CourseID" ColumnName="CourseID" />
</MappingFragment>
</EntityTypeMapping>
</EntitySetMapping>
You can now execute the defining query by using the GradeReport entity type.
using (var context = new SchoolEntities())
{
var report = context.GradeReports.FirstOrDefault();
Console.WriteLine("{0} {1} got {2}",
report.FirstName, report.LastName, report.Grade);
}