Entity Framework and Setting Primary Keys on Views

When using Entity Framework, it’s important to set primary keys on table and view objects in SQL Server. If you skip this step, EF will define composite primary key made of all non-nullable columns belonging to the object. That way you can end up with some strange results and spend hours finding out what went wrong (trust me, I’ve been there). So, first of all: set primary keys in the database!

OK, with tables, that’s just it – set the primary keys and you’re good to go. Now, what about views? In case of views having primary keys defined in underlying tables, that should also be enough. But what to do if there are simply no columns to use as PK? Well, this can be achieved by using ROW_NUMBER() function. Here is the actual example from one of my projects:

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

Now, here’s something that made me confused: I’ve expected RowID column to be defined as NOT NULL. I thought that if there was a row, it should have a number. But I was wrong, as you can see:

vw_Action

So, the column I wanted to use as primary key (RowID) was nullable, and the other (ActionType) that shouldn’t be primary key was in fact the only PK candidate for Entity Framework. As expected, when I performed Update From Database option, the result in edmx file was:

<EntityType Name="vw_Action">
    <Key>
        <PropertyRef Name="ActionType" />
    </Key>
    <Property Name="UserID" Type="int" />
    <Property Name="FriendID" Type="int" />
    <Property Name="Object1ID" Type="int" />
    <Property Name="Object2ID" Type="int" />
    <Property Name="ActionType" Type="int" Nullable="false" />
    <Property Name="ActionDate" Type="datetime" />
    <Property Name="RowID" Type="bigint" />
</EntityType>

If you get tempted to set keys manually in EF, don’t! You will run into errors similar to these:

EFModel.Model.msl(266,10) : error 3002: Problem in Mapping Fragment starting at line 266: Potential runtime violation of table vw_Action’s keys (vw_Action.ActionType): Columns (vw_Action.ActionType) are mapped to EntitySet vw_Action’s properties (vw_Action.ActionType) on the conceptual side but they do not form the EntitySet’s key properties (vw_Action.RowID).\r\n\r\nEFModel.Model.msl(266,10) : error 3003: Problem in Mapping Fragment starting at line 266: All the key properties (vw_Action.RowID) of the EntitySet vw_Action must be mapped to all the key properties (vw_Action.ActionType) of table vw_Action.\r\n

Also, changes will be lost on next update from database.

So, what’s the solution?

The solution is to explicitly define columns you want for PK in SQL server as non-nullable using ISNULL function. Similary, if there is an actual non-nullable column that you want excluded from the PK, use NULLIF, like this:

CREATE VIEW [dbo].[vw_Action]
AS

SELECT UserID, FriendID, Object1ID, Object2ID, NULLIF(ActionType, -1) AS ActionType,
ActionDate, ISNULL(ROW_NUMBER() OVER(ORDER BY ActionDate DESC), -1) AS RowID
FROM
(
    ...
)x

What will be the result of this code? SQL Server will understand that RowID could never be NULL – it will be set to -1 in case of null value (although this will never happen in my case anyway). On the other hand, ActionType column, that can actually never be NULL since it has values 1, 2 and 3, will be interpreted as nullable.

Here is how it all looks in the end:

vw_Action_1

After updating model from the database, the right column will be set as entity primary key in edmx file.

6 comments

  1. SteveC

    Very useful post …

    One point, could you reformat the solution SQL so the ISNULL is easily visible … I got confused and was using NULLIF, which isn’t very useful as a key for for the view :-)

    • Ana Mrvoš

      Hi Steve,
      I’m not sure what you mean… I use a wordpress plugin for formatting the code, can’t change the way it is presented. But as much as I can see, both ISNULL and NULLIF are well marked in red. Is that what you had in mind?
      Ana

      • Stephen Gierek

        I think Steve is referring to the fact that in your code snippet, you have to scroll to the right in order to see the ISNULL function. He may just be suggesting that you break the SELECT statement across multiple lines so that it is all visible without any scrolling.

        Great post, by the way!

  2. Aaron West

    I was trying COALESCE, and it didn’t work. I was surprised to find that COALESCE returns a NULLable-typed column, whereas ISNULL returns a column that is NOT NULL.

  3. Sajjad

    Hi,
    I am using oracle 11g with Entity Framework 5. what will we use in oracle. Kindly note that NVL & COALESCE are not working…
    Any Help

    Regards,

Post a comment

You may use the following HTML:
<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>