Represent View Without Key in Entity Framework

问题:

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?

回答1:

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

回答2:

You can establish the primary key (given that you can guarantee the view to return unique data for the key you define) against an oracle view in the devart EF provider by opening the edml in the designer and manually setting the primary key on the entity. THEN (this is the trick that makes this work) go to the model explorer window and make the same changes to the store part of the model. Typically, if you just drag a view on to the design surface of the EF model, devart will attempt to infer a primary key a view that does not have defined. Again, it is critical that your view return unique data for the key you define.