Using a View with No Primary Key with Entity

Using a view with no primary key with Entity

Is it at all possible to add a view to the Entity model without a
unique identifier?

If without a primary key, no. That will result to this kind of error:

One or more validation errors were detected during model generation:

System.Data.Edm.EdmEntityType: : EntityType 'SalesOnEachCountry' has
no key defined. Define the key for this EntityType.
System.Data.Edm.EdmEntitySet: EntityType: The EntitySet
SalesOnEachCountryList is based on type SalesOnEachCountry that has no
keys defined.

If without a unique identifier, yes, albeit it has a non-desirable output. Records with same identifier would reference the same object, this is called Identity Map Pattern

An example, even if your view produces these two rows:

Country     Year TotalSales
Philippines 2010 20.000000
Philippines 2011 40.000000

If you will just map the primary key on Country field only, e.g.

public class SalesOnEachCountry
{        
    [Key]
    public int CountryId { get; set; }
    public string CountryName { get; set; }        
    public int OrYear { get; set; }
    public long SalesCount { get; set; }
    public decimal TotalSales { get; set; }
}

, even your view produces the above two rows on your Oracle query editor, Entity Framework produces this incorrect output:

Country     Year TotalSales
Philippines 2010 20.000000
Philippines 2010 20.000000

Entity Framework will take it that the second row is same object as first row.

To guarantee uniqueness, you must identify what columns that makes each row unique. In the above example, Year must be included so the primary key is unique. i.e.

public class SalesOnEachCountry
{        
    [Key, Column(Order=0)] public int CountryId { get; set; }
    public string CountryName { get; set; }
    [Key, Column(Order=1)] public int OrYear { get; set; }

    public long SalesCount { get; set; }      
    public decimal TotalSales { get; set; }
}

Making your primary key similar to the attributes above, Entity Framework can correctly map your each view's row to their own objects. Hence, Entity Framework can now display exactly the same rows your view have.

Country     Year TotalSales
Philippines 2010 20.000000
Philippines 2011 40.000000

Full details here: http://www.ienablemuch.com/2011/06/mapping-class-to-database-view-with.html

---

Then regarding your views which don't have any columns to make a row unique, the easiest way to guarantee Entity Framework can map each of your view's row to their own objects is to create a separate column for your view's primary key, a good candidate is to just create a row number column on each row. e.g.

create view RowNumberedView as

select 
    row_number() over(order by <columns of your view sorting>) as RN
    , *
from your_existing_view

Then assign the [Key] attribute on RN property of your class RowNumberedView

Entity Framework code-first: querying a view with no primary key

It's not possible in Entity Framework to have Entities without primary key.

Try to get a possible unique key from the views, combining columns, ... to create a unique primary key.

If is not possible there is a workaround, if is only a queryable view, with out need to do other operations with retrieved values such delete or update. Modify the view to add NEWID() , it will generate a unique GUID ID for each row, use this new column as primary key for your entity.

CREATE VIEW FooView AS
    SELECT SELECT NEWID() AS ID,
           COLUMN_A,
           COLUMN_B
     .....

The problem is if you repeat the same query every time you will get different ID for the same row.

Updated

If you can't not modify the view you can use Entity with a raw Sql, create the raw sql as

List<MyView> myViewItems = context.MyView.SqlQuery("SELECT NEWID() AS ID, MyView.* FROM MyView").ToList();

In your models add

public Guid ID { get; set; }

And configure the new property as the primary key.

But be careful, because there is not compilation check with this kind of code.

JPA map view with no primary key to an entity

JPA Specification says that an Entity class must have a unique, immutable ID.

Logically, if it does not have any primary key, it can't be called entity. What you can do instead is create a POJO representation of your VIEW, then execute a SQL Native query, then map the result set to your POJO.
Here's a sample using @SqlResultSetMapping/@ConstructorResult
http://www.thoughts-on-java.org/result-set-mapping-constructor-result-mappings/

Mapping a SQL View with no Primary Key to JPA Entity

One way to solve this is use a composite primary key by just adding the @Id annotation to the appropriate fields.

How call tables without primary key with entity framework core

Short answer, you don't. Entity Framework requires a key. Good news is you can effectively spoof a key if your table doesn't have one. If your table has an implicit key that is distinct then just decorate that property with [Key] and you'll be good. The key thing is that it has to be a distinct unique value. If you do not have a singular column that does that then you'll need to start using columns together to make a composite key ([Key, Column(Order = 0)].

Entity Framework: table without primary key

The error means exactly what it says.

Even if you could work around this, trust me, you don't want to. The number of confusing bugs that could be introduced is staggering and scary, not to mention the fact that your performance will likely go down the tubes.

Don't work around this. Fix your data model.

EDIT: I've seen that a number of people are downvoting this question. That's fine, I suppose, but keep in mind that the OP asked about mapping a table without a primary key, not a view. The answer is still the same. Working around the EF's need to have a PK on tables is a bad idea from the standpoint of manageability, data integrity, and performance.

Some have commented that they do not have the ability to fix the underlying data model because they're mapping to a third-party application. That is not a good idea, as the model can change out from under you. Arguably, in that case, you would want to map to a view, which, again, is not what the OP asked.

Entity Framework: View exclusion without primary key

As already discussed in the comments you can try adding MAX(id) as id to the view. Based on your feedback this would become:

SELECT ISNULL(MAX(id), 0) as ID, 
       SUM(col1), 
       col2, 
       col3 
FROM   Table1 
GROUP BY col2, col3

Another option is to try creating an index on the view:

CREATE UNIQUE CLUSTERED INDEX idx_view1 ON dbo.View1(id)

---

Related Topics