How To Set a Primary Key On a View Using ROW_NUMBER()?

Question

How To Set a Primary Key On a View Using ROW_NUMBER()?

entity-framework primary-key sql-server-2008

Question

I use SQL Server 2008 and Entity Framework for an app I work on.

I've set primary keys on all tables in SQL Server, since EF requires it, but I have a problem with one view. Since it doesn't have primary key, I've used ROW_NUMBER() function to get unique row identifiers. I've expected for that column to be defined as NOT NULL, so I could use it as primary key in EF, but instead, the column is nullable.

Here is my view:

CREATE VIEW [dbo].[vw_Action]
AS

SELECT *, ROW_NUMBER() OVER(ORDER BY ActionDate DESC) AS RowID 
FROM
(
   SELECT User1ID AS UserID, NULL AS FriendID, Object1ID, Object2ID, ActionType,    ActionDate
   FROM vw_ActionBase
   WHERE ActionType = 1
   GROUP BY User1ID, Object1ID, Object2ID, ActionType, ActionDate 

   UNION

   SELECT User1ID AS UserID, NULL AS FriendID, Object1ID, Object2ID, ActionType,   ActionDate
   FROM vw_ActionBase
   WHERE ActionType = 2
   GROUP BY User1ID, Object1ID, Object2ID, ActionType, ActionDate 

   UNION

   SELECT User1ID AS UserID, NULL AS FriendID, Object1ID, Object2ID, ActionType, ActionDate
   FROM vw_ActionBase
   WHERE ActionType = 3 
   GROUP BY User1ID, Object1ID, Object2ID, ActionType, Object1ID, ActionDate 

   )x

So, my question is how to set ROWID column as NOT NULL, so I could use it as primary key in EF.

Thanks in advance!

Ana M

Popular Answer

I was able to resolve this using the designer.

Open the Model Browser.
Find the view in the diagram.
Right click on the primary key, and make sure "Entity Key" is checked.
Multi-select all the non-primary keys. Use Ctrl or Shift keys.
In the Properties window (press F4 if needed to see it), change the "Entity Key" drop-down to False.
Save changes.
Close Visual Studio and re-open it. I am using Visual Studio 2013 with EF 6 and I had to do this to get the warnings to go away.

I did not have to change my view to use the ISNULL, NULLIF, or COALESCE workarounds. If you update your model from the database, the warnings will re-appear, but will go away if you close and re-open VS. The changes you made in the designer will be preserved and not affected by the refresh.

Casey Plummer

View more on Stack Overflow

Tillito · Accepted Answer · 4/26/2010 5:00:42 PM

We had the same problem and this is the solution:

To force entity framework to use a column as a primary key, use ISNULL.

To force entity framework not to use a column as a primary key, use NULLIF.

An easy way to apply this is to wrap the select statement of your view in another select.

Example:

SELECT
  ISNULL(MyPrimaryID,-999) MyPrimaryID,
  NULLIF(AnotherProperty,'') AnotherProperty
  FROM ( ... ) AS temp

How To Set a Primary Key On a View Using ROW_NUMBER()?

Question

Fastest Entity Framework Extensions

Accepted Answer

Popular Answer

Performance

Expression Evaluator

Related