代表实体框架中没有键的视图 [英] Represent View Without Key in Entity Framework

问题描述

我正在使用Devart Entity Developer生成的模型（.edml文件，我理解的大体上类似于.edmx）来生成其关系在DbContext类中初始化的Entity类。数据库模式不为View1指定PK，所有列都为空。但是代码生成假设对于缺少主键的视图，所有列都是关键。即，.ssdl具有Key元素下的所有列，DbContext全部指定了.IsRequired（），如下所示：

ssdl摘录：

  ... 
< EntityType Name =View1> 
< Key> 
< PropertyRef Name =FirstCol/> 
< PropertyRef Name =Col2/> 
< PropertyRef Name =LastCol/> 
< / Key> 
< Property Name =FirstColType =VARCHAR2Nullable =falseMaxLength =4000/> 
< Property Name =Col2Type =VARCHAR2Nullable =falseMaxLength =120/> 
< Property Name =LastColType =VARCHAR2Nullable =falseMaxLength =20/> 
< / EntityType> 
 .... 
  

从DbContext：

  protected override void OnModelCreating（DbModelBuilder modelBuilder）
 {
 #region View1 
 modelBuilder.Entity< View1>（）
 .HasKey（p => new {p.FirstCol，p.Col2，p.LastCol}）
 .ToTable（View1，Owner）; 
 //属性：
 modelBuilder.Entity< View1>（）
 .Property（p => p.FirstCol）
 .HasColumnName（FirstCol）
 .IsRequired（）
 .HasMaxLength（4000）
 .HasColumnType（VARCHAR2）; 
 modelBuilder.Entity< View1>（）
 .Property（p => p.Col2）
 .HasColumnName（Col2）
 .IsRequired（）
 .HasMaxLength（120）
 .HasColumnType（VARCHAR2）; 
 modelBuilder.Entity< View1>（）
 .Property（p => p.LastCol）
 .IsRequired（）
 .HasMaxLength（20）
 .HasColumnType （ VARCHAR2）; 
  

当查询返回一个具有空值的PK列之一的行时，这将导致NullReferenceException。 / p>

有没有办法在EF中表示视图，而不指定键或更改数据库模式？我发现了这一点： http：// elegantcode。 com / 2012/03/15 / querying-entityframework-views-without-a-key /

这是首选解决方案吗？我想我也可以使用DefiningQuery，但这似乎复制了用于生成视图的sql？

解决方案

你应执行以下步骤：

1. 对于实体的所有列，在模型的概念和存储部分中将Entity Key属性设置为False 。


2. 将字符串类型的Id属性添加到模型概念部分中的实体，并将Entity Key值设置为True。


3. 如果您启用了概念和存储部件的同步（模型设置对话框 - >同步 - >映射），则Id列将自动添加到存储部件中的现有实体。您应该为此列设置Type = ROWID和Name = ROWID。映射将自动定制。
   如果同步关闭，请将ROWID列添加到存储部件中的相应实体，并为其设置Type = ROWID。之后，调用该类的上下文菜单，选择映射详细信息项，并在显示的对话框中指定有效的列映射。


4. 对于类的其他属性设为Nullable为True。

已编辑项3：存储部分中实体的列名称必须为ROWID

I'm using a model produced with Devart Entity Developer (.edml file, which I understand is mostly similar to .edmx) to generate Entity classes whose relations are initialized in a DbContext class. The database schema specifies no PK for View1, and all columns are nullable. But the code generation assumes that for views lacking a primary key, all columns are the key. I.e., the .ssdl has all columns under the Key element and the DbContext has .IsRequired() specified on all, like so:

ssdl excerpt:

...
<EntityType Name="View1">
  <Key>
    <PropertyRef Name="FirstCol" />
    <PropertyRef Name="Col2" />
    <PropertyRef Name="LastCol" />
  </Key>
  <Property Name="FirstCol" Type="VARCHAR2" Nullable="false" MaxLength="4000" />
  <Property Name="Col2" Type="VARCHAR2" Nullable="false" MaxLength="120" />
  <Property Name="LastCol" Type="VARCHAR2" Nullable="false" MaxLength="20" />
</EntityType>
....

From DbContext:

protected override void OnModelCreating(DbModelBuilder modelBuilder)
{     
  #region View1
  modelBuilder.Entity<View1>()
    .HasKey(p => new { p.FirstCol, p.Col2, p.LastCol})
      .ToTable("View1", "Owner");
        // Properties:
        modelBuilder.Entity<View1>()
          .Property(p => p.FirstCol)
            .HasColumnName("FirstCol")
            .IsRequired()
            .HasMaxLength(4000)
            .HasColumnType("VARCHAR2");
        modelBuilder.Entity<View1>()
          .Property(p => p.Col2)
            .HasColumnName("Col2")
            .IsRequired()
            .HasMaxLength(120)
            .HasColumnType("VARCHAR2");
        modelBuilder.Entity<View1>()
          .Property(p => p.LastCol)
            .IsRequired()
            .HasMaxLength(20)
            .HasColumnType("VARCHAR2");

This causes a NullReferenceException when querying would return a row with one of the PK columns having null value.

Is there a way to represent a view in EF without specifying keys or altering the database schema? I've found this: http://elegantcode.com/2012/03/15/querying-entityframework-views-without-a-key/

Is this the preferred solution? I suppose I could use DefiningQuery as well, but this seems to duplicate the sql that was used to generate the view?

解决方案

You should perform the following steps:

1. For all columns of the entity set the Entity Key property to False in the conceptual and storage parts of the model.
2. Add the Id property of the string type to the entity in the conceptual part of the model and set the Entity Key value for it to True.
3. If you have the synchronization of the conceptual and storage parts turned on (Model Settings Dialog->Synchronization->Mapping), then the Id column will be added automatically to the existing entity in the storage part. You should set Type=ROWID and Name=ROWID for this column. Mapping will be customized automatically. If the synchronization is off, add the ROWID column to the corresponding entity in the storage part and set for it Type=ROWID. After this, call the context menu of the class, select the Mapping Details item and in the displayed dialog specify valid column mapping.
4. For other properties of the class set Nullable to True.

Edited item 3: column name of the entity in the storage part must be ROWID

这篇关于代表实体框架中没有键的视图的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！