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!
Accepted Answer
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
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.
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
