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]

   SELECT User1ID AS UserID, NULL AS FriendID, Object1ID, Object2ID, ActionType, ActionDate
   FROM vw_ActionBase
   WHERE ActionType = 1
   GROUP BY User1ID, Object1ID, Object2ID, ActionType, ActionDate


   SELECT User1ID AS UserID, NULL AS FriendID, Object1ID, Object2ID, ActionType, ActionDate
   FROM vw_ActionBase
   WHERE ActionType = 2
   GROUP BY User1ID, Object1ID, Object2ID, ActionType, ActionDate


   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


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:


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">
        <PropertyRef Name="ActionType" />
    <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" />

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]

SELECT UserID, FriendID, Object1ID, Object2ID, NULLIF(ActionType, -1) AS ActionType,

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:


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

——— EDIT ———
Related post: A Quick Note on COALESCE and ISNULL


  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?

      • 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

    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


    • Rodney

      I know this is an old post, but in case anyone else comes across this, you can create a PK on a view in Oracle and set it to disabled. Entity Framework will pick it up and set the entity key.

      ALTER VIEW my_view
      ADD CONSTRAINT my_view

  4. Jon Shipman

    Thank you very much! I too spent hours with this. I had a view that had no candidate keys. The EF inferred a key, but it was not unique. The query generated by the EF was good (as seen in SQL Server Query Profiler). When I captured that query and ran it in SSMS, the results for valid. However, the EF must “massage” the results, because even though accurate results were returned from the query, it gave inaccurate results in a one-to-many situation. Very confusing!

    In my case, my view has over 300k rows (and growing). I used:
    But, I found a ~20% performance hit. Instead, I used the following:
    ISNULL(NEWID(), ‘EA18E104-E07A-49BA-A163-D9D37E4CAF1B’) AS Id
    Where the ‘EA18E…’ is a random GUID. With this I found no performance hit.
    Do you see any downside to this alternative?

    Thanks again!

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=""> <s> <strike> <strong>