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!
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
I was able to resolve this using the designer.
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.