如何基于View选择生成实体 [英] How to generate Entity with selection based on View

问题描述

请考虑以下示例：

SQL表客户：

ID：主键
名称：名称

SQL视图ViewCustomerSelection：

ID ：链接到客户ID
SomeOption：不重要

我想创建一个仅在ViewCustomerSelection中选择具有相应值的客户的实体ADO.NET

以下不起作用：

- 创建映射到Customer和ViewCustomerSelection的实体，而不使用任何条件和ViewCustomerSelection中的任何字段：SQL中不包含ViewCustomerSelection并返回所有客户。
- 包含ViewCustomerSelection中的字段：正确选择返回，但现在Customer对象不可更新
- 指定"not null" SomeOption字段上的条件：错误 - 必须映射具有'IsNull = false'条件的属性ViewCustomerSelection.SomeOption。
- 指定"not null" ViewCustomerSelection.ID字段上的条件：错误 - "实体类型包含其主键上的条件。请从映射中删除条件。



如果它很重要：View使用CustomerRole表连接Customer并将其限制为仅指定某个角色的客户。
不幸的是CustomerRole表拥有它自己的主键（结构是CustomerRoleKey - CustomerKey - RoleKey），任何将它添加到实体模式的尝试都会失败。

Consider following example:

SQL Table Customer:

    ID: Primary Key
    Name: Name

SQL View ViewCustomerSelection:

    ID: Link to Customer ID
    SomeOption: Not important

I'd like to create ADO.NET an entity that only selects customers that have corresponding values in ViewCustomerSelection

Following does NOT work:

  - Create entity that maps to Customer and to ViewCustomerSelection without using any conditions and any fields from ViewCustomerSelection : ViewCustomerSelection is not included in SQL and all customers are returned.
  - Include a field from ViewCustomerSelection : Correct selection is returned but now Customer object is not updateable
  - Specify "not null" condition on SomeOption field : error - "Property ViewCustomerSelection.SomeOption with 'IsNull=false' condition must be mapped.
  - Specify "not null" condition on ViewCustomerSelection.ID field : error - "Entity type contains a condition on its primary key. Please remove the condition from the mapping.



In case it is important: View joins Customer with CustomerRole table and limits it to only customer where certain role is specified.
Unfortunately  CustomerRole table has it's own primary key ( the structure is CustomerRoleKey - CustomerKey - RoleKey ) and any attempt to add it to entity mode fails miserably.

推荐答案

Michael; < br>
默认情况下，实体设计器（或EDMgen.exe）将视图定义为只读。它通过在.edmx文件（或生成的.ssdl文件）中的存储架构定义（SSDL）中添加定义查询来实现此目的。

如果视图在数据库中是可更新的，则最简单的解决方案是删除定义查询并使其在SSDL中显示为常规表。您还需要删除"store："来自"Schema"的名称空间限定符例如，您可能在.edmx中有一个类似于以下内容的部分（以XML格式查看/编辑.edmx文件，在解决方案资源管理器中右键单击.edmx文件并选择" ;打开方式..."并选择"XML编辑器"）：

< EntitySet 名称 = " ViewCustomerSelection " EntityType = " YourModel.Store.ViewCustomerSelection "
商店：类型 = " 观点" store：Schema = " dbo " ; 商店：名称 = " ViewCustomerSelection " ; >

Michael;

By default, the Entity Designer (or EDMgen.exe) defines views as Read Only.  It does this by adding a defining query in the Storage Schema Definition (SSDL) within your .edmx file (or generated .ssdl file).

If the view is updatable in the database, the simplest solution is to remove the defining query and make it appear as a regular table in the SSDL.  You will also need to remove the "store:" namespace qualifier from the "Schema" attribute.

For example you probably have a section that looks something like the following in your .edmx (to view/edit your .edmx file as XML, right-click your .edmx file in Solution Explorer and select "Open With..." and choose "XML Editor"):

<EntitySet Name="ViewCustomerSelection" EntityType="YourModel.Store.ViewCustomerSelection"
   store:Type="Views" store:Schema="dbo" store:Name="ViewCustomerSelection">

< 定义查询 > SELECT

[ViewCustomerSelection]。[ID] AS [ID]，

[ViewCustomerSelection]。[SomeOption] AS [SomeOption]

FROM [dbo]。[ViewCustomerSelect] AS [ViewCustomerSelection]

< / 定义查询 >

< / EntitySet >

< EntitySet 名称 = " ViewCustomerSelection " EntityType = " YourModel.Store.ViewCustomerSelection "
商店：类型 = " 观点" 架构 = " dbo " < span style ="color：blue">>

<EntitySet Name="ViewCustomerSelection" EntityType="YourModel.Store.ViewCustomerSelection"
  store:Type="Views" Schema="dbo">

< / EntitySet >

这篇关于如何基于View选择生成实体的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！