5

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
  • 100
  • 1
  • 1
  • 8

0 Answers0