Entity Framework — The View does not have a primary key defined and no valid primary key could be inferred.

A possible solution for the truly frustrated.

If the Internets have directed you to this lowly blog after a long day of searching, I truly feel for you. If you’re trying to make Entity Framework play nice with your RDBMS, it’s incredibly frustrating when the code-first migration wizard excludes half your database objects while making a ton of changes that you can’t cleanly roll back. You’re having a bad day.

Background

Implicit in this post facto “warning” after the Entity Framework’s migration wizard exits is the following fact: Entity Framework has no internal way of referring to entities except through a primary key. Therefore, Entity Framework supports tables and views if and only if those tables and views have a primary key. This is a very disconcerting statement, though, for people who know what a view is.

It’s my understanding there are work-arounds in the .edmx editor and that there are some tricks you can work from SQL Server with row_number() or ISNULL(), but as someone who is trying to go code-first from an Oracle database, I really have no way of knowing that for sure, and the Oracle equivalents or ROWNUM and NVL() just don’t do the trick by themselves. Nevertheless, I’ve worked my way around my own hack, and I thought I’d share it here.

Find Your Pseudo-Key

Let me say, first of all: you do need a key. You need something unique and non-nullable. With that being said, I just want to point out that if you’re working with views, it doesn’t have to be a very good primary key. If you don’t need to update, you probably don’t really need to track entities. For this reason, I’m partial to using grabbing a ROWNUM and calling it ROW_ID.

And at this point, if you’re a fan of doing work, you could probably muddle through from here. The wizard won’t accept your hacky ROW_ID as a key because it shows up as nullable, but you could still import the entities that you can, and then manually type out your entity in a new file and map all of your properties in the DbContext like some kind of animal.

But we’re humans, and we use tools. And sometimes when those tools fail, we can trick them into doing what we want. (Besides, I find manually mapping entities to be a process that is both fickle and difficult to debug)

Pulling a Fast One on Entity Framework

Alright, so let’s say you’ve got a view DOGS_VW to which you have added ROWNUM as ROW_ID like so:

SELECT
    ROWNUM ROW_ID,
    d.BREED,
    d.ADULT_WEIGHT
FROM DOG d
;

This yields:

column name  | type        | nullable
-------------------------------------
ROW_ID       | NUMBER      | Yes
BREED        | VARCHAR2(50)| Yes
ADULT_WEIGHT | NUMBER      | Yes

The reason Entity Framework still rejects your view is that the data types are all nullable, and there’s nothing you can do about the fact that Oracle marks your ROW_ID as nullable, even though you know it’s not.

So let’s add something not nullable into the mix. The fun part: it doesn’t have to make sense. Like so:

SELECT 
    c.CAT_ID,
    ROWNUM ROW_ID,
    d.BREED,
    d.ADULT_WEIGHT
FROM DOG d
RIGHT JOIN CAT c
    ON c.BREED = d.BREED
;

Note that the only thing I care about is that CAT has a real, decent primary key, and it has a column that has the same datatype as a column in DOG so we can join. Now, the join doesn’t actually mean anything. There aren’t any cat breeds in this table that are the same name as dog breeds, so we just got rid of any rows we had in this view.

However, look at what happened to our columns.

column name  | type        | nullable
------------------------------------- 
CAT_ID       | NUMBER      | No
ROW_ID       | NUMBER      | Yes
BREED        | VARCHAR2(50)| Yes
ADULT_WEIGHT | NUMBER      | Yes

Aha! Now we can run our Entity Framework migration wizard, and it let’s us through by inferring a primary key of CAT_ID, saving you the trouble of typing out your DOG_VW entity class.

Then all you have to do is remove the CAT_ID and related mappings and revert to your old version of the view in the database. Be sure to mark your actual key, your ROW_ID, as your primary key in the absence of CAT_ID.

That’s all I’ve got. I still hate Entity Framework. It just has too many limitations to be a useful ORM. But I live to fight another day.