title: How To Set a Primary Key On a View Using ROW_NUMBER()?
body: <p>I use SQL Server 2008 and Entity Framework for an app I work on. </p>
  
  <p>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.</p>
  
  <p>Here is my  view:</p>
  
  <pre><code>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
  </code></pre>
  
  <p>So, my question is how to set ROWID column as NOT NULL, so I could use it as primary key in EF.</p>
  
  <p>Thanks in advance!</p>