mysql查询ef_定义查询-EF 设计器-EF6 | Microsoft Docs

定义查询 EF 设计器Defining Query - EF Designer

10/23/2016

本文内容

本演练演示如何使用 EF 设计器将定义查询和相应的实体类型添加到模型。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. 使用定义查询可执行在 .edmx 文件的 DefiningQuery元素中指定的 SQL 语句   。A defining query allows you to execute a SQL statement that is specified in the DefiningQuery element of an .edmx file. 有关详细信息，请参阅SSDL 规范中的 DefiningQuery 。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.

本主题演示如何执行以下任务。This topic shows how to perform the following tasks.

添加定义查询Add a Defining Query

向模型添加实体类型Add an Entity Type to the Model

将定义查询映射到实体类型Map the Defining Query to the Entity Type

先决条件Prerequisites

若要完成此演练，您需要：To complete this walkthrough, you will need:

Visual Studio 的最新版本。A recent version of Visual Studio.

设置项目Set up the Project

本演练使用 Visual Studio 2012 或更高版本。This walkthrough is using Visual Studio 2012 or newer.

打开 Visual Studio。Open Visual Studio.

在 “文件” 菜单上，指向 “新建” ，再单击 “项目” 。On the File menu, point to New, and then click Project.

在左窗格中，单击 " Visual # C"，然后选择 "控制台应用程序" 模板。In the left pane, click Visual C#, and then select the Console Application template.

输入 DefiningQuerySample 作为项目名称，然后单击 "确定"。Enter DefiningQuerySample as the name of the project and click OK.

基于 School 数据库创建模型Create a Model based on the School Database

右键单击 "解决方案资源管理器中的项目名称，指向" 添加"，然后单击" 新建项"。Right-click the project name in Solution Explorer, point to Add, and then click New Item.

从左侧菜单中选择 " 数据 "，然后在 "模板" 窗格中选择 " ADO.NET 实体数据模型 。Select Data from the left menu and then select ADO.NET Entity Data Model in the Templates pane.

输入 DefiningQueryModel 作为文件名，然后单击 " 添加"。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. 在 "连接属性" 对话框中，输入服务器名称 (例如， ** (localdb) \ mssqllocaldb**) ，选择身份验证方法，为数据库名称键入 School，然后   单击 **"确定"**。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. 这会将所有表添加到 School 模型。This will add all the tables to the School model.

单击 " 完成"。Click Finish.

在解决方案资源管理器中，右键单击 DefiningQueryModel 文件并选择 " 打开方式 ..."。In Solution Explorer, right-click the DefiningQueryModel.edmx file and select Open With….

选择 " XML (文本) 编辑器"。Select XML (Text) Editor.

如果出现以下消息，请单击 "是" ：Click Yes if prompted with the following message:

添加定义查询Add a Defining Query

在此步骤中，我们将使用 "XML 编辑器" 向 .edmx 文件的 SSDL 部分添加定义查询和实体类型。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.

将 EntitySet   元素添加到 .EDMX 文件的 SSDL 部分 (第5行到第 13) 。Add an EntitySet element to the SSDL section of the .edmx file (line 5 thru 13). 指定下列各项：Specify the following:

仅 Name   指定 EntitySet 元素的 Name 和 EntityType   特性 EntitySet   。Only the Name and EntityType attributes of the EntitySet element are specified.

实体类型的完全限定名称用于 EntityType   特性中。The fully-qualified name of the entity type is used in the EntityType attribute.

要执行的 SQL 语句是在 DefiningQuery元素中指定的   。The SQL statement to be executed is specified in the DefiningQuery element.

SELECT CourseID, Grade, FirstName, LastName

FROM StudentGrade

JOIN

(SELECT * FROM Person WHERE EnrollmentDate IS NOT NULL) AS p

ON StudentID = p.PersonID

将 EntityType 元素添加到 .EDMX 的 SSDL 部分。Add the EntityType element to the SSDL section of the .edmx. 文件，如下所示。file as shown below. 注意以下事项：Note the following:

Name特性的值对应于上面的EntitySet元素中的EntityType特性的值，但该实体类型的完全限定名称在EntityType特性中使用。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.

属性名称对应于上面) (的 DefiningQuery 元素中的 SQL 语句返回的列名称。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.

Type="int"

Nullable="false" />

Type="decimal"

Precision="3"

Scale="2" />

Type="nvarchar"

Nullable="false"

MaxLength="50" />

Type="nvarchar"

Nullable="false"

MaxLength="50" />

备注

如果以后运行 模型更新向导 对话框，对存储模型所做的任何更改(包括定义查询)都将被覆盖。If later you run the Update Model Wizard dialog, any changes made to the storage model, including defining queries, will be overwritten.

向模型添加实体类型Add an Entity Type to the Model

在此步骤中，我们将使用 EF 设计器将实体类型添加到概念模型。In this step we will add the entity type to the conceptual model using the EF Designer. 注意以下事项：Note the following:

实体的名称与上面的EntitySet元素中的EntityType属性的值相对应。The Name of the entity corresponds to the value of the EntityType attribute in the EntitySet element above.

属性名称对应于上面的 DefiningQuery 元素中的 SQL 语句返回的列名称。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.

在 EF 设计器中打开模型。Open the model in the EF Designer.

双击 "DefiningQueryModel"。Double-click the DefiningQueryModel.edmx.

对以下消息说为 "是" ：Say Yes to the following message:

此时会显示 Entity Designer，它提供了用于编辑模型的设计图面。The Entity Designer, which provides a design surface for editing your model, is displayed.

右键单击设计器图面，然后选择 "添加新 - > 实体 ..."。Right-click the designer surface and select Add New->Entity….

为 "键" 属性的 "实体名称" 和 " CourseID " 指定GradeReport 。Specify GradeReport for the entity name and CourseID for the Key Property.

右键单击 " GradeReport " 实体，然后选择 "添加新 - > 的标量属性"。Right-click the GradeReport entity and select Add New-> Scalar Property.

将属性的默认名称更改为 FirstName。Change the default name of the property to FirstName.

添加另一个标量属性，并为名称指定 LastName 。Add another scalar property and specify LastName for the name.

添加另一个标量属性，并为该名称指定 等级 。Add another scalar property and specify Grade for the name.

在 " 属性 " 窗口中，将 评分的 " 类型 " 属性更改为 Decimal。In the Properties window, change the Grade’s Type property to Decimal.

选择 FirstName 和 LastName 属性。Select the FirstName and LastName properties.

在 " 属性 " 窗口中，将 EntityKey 属性值更改为 True。In the Properties window, change the EntityKey property value to True.

因此，将以下元素添加到 .edmx 文件的 CSDL 部分。As a result, the following elements were added to the CSDL section of the .edmx file.

. . .

将定义查询映射到实体类型Map the Defining Query to the Entity Type

在此步骤中，我们将使用 "映射详细信息" 窗口来映射概念性实体类型和存储实体类型。In this step, we will use the Mapping Details window to map the conceptual and storage entity types.

右键单击设计图面上的 " GradeReport " 实体，然后选择 " 表映射"。Right-click the GradeReport entity on the design surface and select Table Mapping.

将显示 " 映射详细信息 " 窗口。The Mapping Details window is displayed.

从 " ** < 添加表" 或 "视图 > ** " 下拉列表中选择 " GradeReport "， (位于表s) 下。Select GradeReport from the dropdown list (located under Tables).

显示概念和存储 GradeReport 实体类型之间的默认映射。Default mappings between the conceptual and storage GradeReport entity type appear.

因此，会将EntitySetMapping   元素添加到 .edmx 文件的映射部分。As a result, the EntitySetMapping element is added to the mapping section of the .edmx file.

编译该应用程序。Compile the application.

在代码中调用定义查询Call the Defining Query in your Code

现在可以使用 GradeReport 实体类型执行定义查询。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);

}