Fixing issue of not being able to add view to Database First Entity Framework model

Hi,

If you’re using Database First entity framework (EF) model, you might require to use views. Now to add views to the EF model, the process is straight forward, just the same way we add Tables. However, there is a chance the view may not show up in the Model at all.

Solution: Your view must have a column that it can infer as a Key. Not necessarily a primary Key but any key. Entity Framework will take any non-nullable, non-binary column and mark it as key. This is because entity framework does indexing to the view internally.

Consider for example a simple console application where we’re trying to add an EF model. I’ve created a sample table Users with 4 columns:

  1. ID (Primary Key)
  2. FirstName
  3. LastName
  4. IsActive (Bit Field)

I’ve also created 3 views with the following names. I hope the names are self-explainatory

  1. 1_UsersViewWithNoKey
     SELECT [FirstName], [LastName] FROM [dbo].[1_UsersViewWithNoKey]
  2. 2_UsersViewWithBitColumnIncluded
     SELECT [FirstName], [LastName], [IsActive] FROM [dbo].[2_UsersViewWithBitColumnIncluded]
  3. 3_UsersViewWithPrimaryKeyIncluded
     SELECT [Id], [FirstName], [LastName] FROM [dbo].[3_UsersViewWithPrimaryKeyIncluded]

Note that the first view just includes FirstName and last name. As we try to include this view in the .EDMX, we see that the view doesn’t get added to EDMX and the below statement appears in the output window

The model was generated with warnings or errors.SampleDataModel.edmx Please see the Error List for more details. These issues must be fixed before running your application.

In the errors window, we see the following error message

It shows that Entity Framework is expecting the view’s select list to contain some key column. Let’s try to add the second view now.

After clicking Finish, we see that View is added. However, take a note of the highlighted column and ALSO the error message that shows in the error list.

This shows that Entity Framework inferred the key to be the IsActive Column because it is of type bit (=boolean in C#) which won’t be null. Now let’s try to add the final view which has the primary key column included.

We can see that this view also gets added to EDMX and we can also see the Key symbol beside the Id column.

So make sure that when you’re adding views to the EDMX, the view needs to have some column which Entity Framework can consider as a key.

Hope this helps!!

 

 

Advertisements

About Amogh Natu

Technology enthusiast, Associate Consultant @ Microsoft, music lover, love my guitar, Microsoft.NET, ASP.NET, C#.NET Professional.
This entry was posted in .NET, General and tagged , , , , , , , , , . Bookmark the permalink.

Your comments or opinions.....

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s