Entity Framework relies on primary keys with views and tables...
- January 18, 2017
Entity Framework relies on primary keys with views and tables. If you are trying to bring a view into dbContext, and the view does not have a primary key the following should help you out. The schema object has to have primary key defined and it be set to not allow nulls (not-null).
The solution for me was to refashion my view definition using RowId like the following
This will create a non-null RowId for each row in the result.
SELECT ISNULL(ROW_NUMBER() OVER(ORDER BY P1), -1) AS RowID, P1 AS Code, P3, P2 FROM dbo.BigTable WHERE P1 IS NOT NULL
After you create the view take a look at the view definition within SQL Server Management Studio. You should see that you have defined a column RowId which is non-null.
This will satisfy Entity Framework and dbContext can now include a reference to these views or tables.