Entity Framework: Creating a model using views instead of tables

A few days back, I created an entity model using views instead of tables. Since my physical model consisted of Views instead of tables so Entity Designer was not able to infer primary keys/relations etc and I had to create them manually. In this post, I will highlight the steps I took to create a working entity model by hand. Please read at your own risk and feel free to contribute your ideas using the comments section.

Assume we have a JobWBS and a ResourceAssignment view with a One-Many relationship as depicted below:

If we try to create an “ADO.NET Entity Data Model” for the above views, Visual Studio will generate a model similar to this one.

Notice that the designer marked all fields as primary keys. We need to manually edit the generated edmx file by opening it with “XML editor”.

You will also notice the following warnings inside the edmx xml:

Errors Found During Generation:
warning 6002: The table/view 'testdb.dbo.vw_JobWBS' does not have a primary key defined. The key has been inferred and the definition was created as a read-only table/view.

Entity Framework: TimeStamp fields not recognized for optimistic concurrency model

Today, while working with Entity Framework, I noticed through SQL Profiler that my update queries were not using timestamp field. I looked into my model and found that my timestamp fields have Concurrency Mode property set to None. It was quite strange; The entity model was auto generated and I wonder why the EF designer could not identify my timestamp field (I remember Linq To SQL designer automatically took care of such fields). Anyway, I changed Concurrency Mode to Fixed and it worked great. So, as a final note, when generating entity model from database, don’t forget to set appropriate concurrency modes for your timestamp fields as it is not done by the designer itself.

RIA Services: Working with Foreign Keys/Associations in Entity Framework

Yesterday, I got an strange observation regarding RIA Services. I needed to created an Entity Model from views instead of tables so it had no foreign key associations defined. I manually created the associations and was finally able to get a working model. I then added a RIA Domain Service and got the following error upon build:

“Unable to retrieve AssociationType for association ‘JobWBS_ResourceAssignment’”

Strange. But after some effort I came to know that the current(July 2009) preview of RIA services expects all the associations in the default foreign key naming convention, i.e., FK_ForeignKeyTable_PrimaryKeyTable. In my case, I had a 1-Many relationship between JobWBS and ResourceAssignment so I changed the association name to FK_ResourceAssignment_JobWBS and the project built successfully.

Update: If you are still not able to get rid of the error. Its probably because your SSDL schema does not have the proper associations defined. Have a look at this post on how to manually define associations in the physical (storage) model.