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

Question

Our customer has given the access to views in which there is no primary key is defined. I know Entity Framework needs a primary key for table to identify.

But for views not having primary key is it still possible to query.

I try to find but always Entity Framework gives error saying:

Error: : EntityType 'ViewWeight' has no key defined. Define the key for this EntityType.

I understand key is important for tables, but for views just to read is there any hack or way to read the values without modifying the view itself.

possible duplicate of [how to use views in code first entity framework](http://stackoverflow.com/questions/7461265/how-to-use-views-in-code-first-entity-framework) and look also http://stackoverflow.com/questions/5889905/how-do-i-define-a-database-view-using-entity-framework-4-code-first — HaveNoDisplayName, Jun 22 '15 at 18:53

score 7 · Accepted Answer · edited Jul 27 '20 at 18:14

7

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.

edited Jul 27 '20 at 18:14

AlignedDev

8,102
9
56
91

answered Jun 22 '15 at 18:52

Marc Cals

2,963
4
30
48

Thanks for reply, but as i said i dont have the access to change the view, so i am looking for some kind of hack or raw sql query which may do the trick :) – ThomasBecker Jun 22 '15 at 18:57
var result = entityContext.Database.SqlQuery("SELECT NEWID(), MyView.* FROM MyView").ToList(); Same problem that key is not defined on MyEntity, may be I should use non entity type like tuple – ThomasBecker Jun 22 '15 at 19:09
Have you add the Primary Key to your model and set as a Primary Key? – Marc Cals Jun 22 '15 at 19:10
Strange i m getting ORA-00955: name is already used by an existing object, also i m using Oracle database is newid works in oracle also i m not sure – ThomasBecker Jun 22 '15 at 19:17
Try this adding IsNull http://stackoverflow.com/questions/1013333/entity-framework-and-sql-server-view – Marc Cals Jun 22 '15 at 19:17
Sorry I miss the part that you use Oracle, I've updated the Sql raw code for it. – Marc Cals Jun 22 '15 at 19:20
now error is Guid Id is not mapped to database column name.. which is quiet correct, is there any way to prevent this The data reader is incompatible with the specified 'ViewWeight'. A member of the type, 'ID', does not have a corresponding column in the data reader with the same name. – ThomasBecker Jun 22 '15 at 19:28
1

Yes for example `Select SYS_GUID() As ID, ...` you have to rename the column as the same name in your model. – Marc Cals Jun 22 '15 at 19:31
Works like charm now, Thanks a lot for help. Only one disadvantages of raw sql that i loose my custom column name mapping but I can generate T4 for this. Thanks :) – ThomasBecker Jun 22 '15 at 19:42

score 0 · Answer 2 · edited Jun 22 '15 at 18:58

0

I create the view which includes a primary key. Ensure that all fields in the view are of a specific data type:

Number(9) rather than Number, use CAST to get the type you want

Then add a disabled primary key constraint. It won't do anything except be recognized by entity framework as a key

alter view emp_view add constraint vemp_pk primary key (empno) disable

edited Jun 22 '15 at 18:58

HaveNoDisplayName

8,291
106
37
47

answered Jun 22 '15 at 18:56

kevinskio

4,431
1
22
36

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

2 Answers2

Linked