ADO.NET EF solution for "The table/view ' does not have a primary key defined. The key has been inferred and the definition was created as a read-only table/view."

Hey there,

In my current project we are using the ADO.NET Entity Framework (for multiple run-time environments: .NET & COM - see previous post for details) and we got the error from the title when generating the entities from database:The table/view '' does not have a primary key defined. The key has been inferred and the definition was created as a read-only table/view.
In order to fix this, you should do this:
- create a non-nullable PK in a table.
- create a virtual non-nullable "PK" and ID in a view.

For Micosoft SQL Server you can use the ROW_NUMBER() function and for Oracle (11g) you can use rownum but that's not enought (I think for MS SQL Server also). You have to add a constraints (yes, views can have constraints):

CREATE OR REPLACE FORCE VIEW "SCHEMA"."VIEW_NAME" ("ID", "COL1", "COL2")
AS
SELECT ROWNUM, COL1, COL2 FROM TABLE
WITH READ ONLY;

alter view VIEW_NAME add constraint id_pk primary key (ID) disable novalidate;.

 Hope it helps you sometime. :)

Bonus tip: if your view does a UNION (as it was in my case) the second SELECT will have something like SELECT ROWNUM+(SELECT COUNT(*) FROM TABLE1). ;)