Represent View Without Key in Entity Framework

Question

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?

Devart · Accepted Answer · 2012-07-02T10:34:22.280

1

You should perform the following steps:

For all columns of the entity set the Entity Key property to False in the conceptual and storage parts of the model.
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.
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.
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

edited Jul 02 '12 at 10:34

answered Jun 27 '12 at 07:36

Devart

119,203
23
166
186

I tried this and am receiving an error `ORA-00904: "Extent1"."Id": invalid identifier` when trying to query with Linq to Entities. I see that the EF-generated sql is trying to query for an id column that doesn't exist. Were you suggesting to update the database view to have a rowid? My question was about making changes without altering database. – ossek Jun 27 '12 at 17:43
1

We have edited our answer. Please note, that the described algorithm works for views created without using constructions like UNION , GROUP BY etc. – Devart Jul 02 '12 at 11:01
Thank you for your comments and edit. Unfortunately the view definition does involve UNIONs. Would there be another pseudocolumn or convention I could use instead of ROWID? – ossek Jul 02 '12 at 18:21
If you used XML mapping, you could use defining query, but since you use fluent mapping, you need either to modify the view by adding the ROWID column manually or to use the solution you have found by that link: http://elegantcode.com/2012/03/15/querying-entityframework-views-without-a-key/ – Devart Jul 03 '12 at 07:41

score 0 · Answer 2 · answered Jun 27 '12 at 02:17

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.

Represent View Without Key in Entity Framework

2 Answers2

Linked