Friday, April 17, 2009

Using views from EDM - Entity Framework

In one of my recent projects using Entity Framework/WPF I needed to have read-only data from 10+ different tables, and I decided to have a view in SQL Server, as opposed to have the joins generated from the EDM (Entity Data Model). The view exposed both nullable and non-nullable fields, including primary keys of the underlying tables. However, since I had outer joins, some of these primary keys exposed from the view could actually be null. In this scenario one must be careful and tweak the model, so that all data returned from the database can be materialized into entities.
If I used an indexed view I wouldn't have any issues, but the query in my view had multiple self-joins, which is a limitation of indexed-views, at least in SQL Server 2005 and 2008.

When a non-indexed view is added to the EDM, the framework tries to infer one of more entity keys from the database - it generates entity keys from the non-nullable fields. In the scenario described above, I had non-nullable fields (PKs) that could actually be null (because of outer joins in the view). In this case, during the enumeration of the items in the EntitySet, the data is retrieved from the database but it would not be able to be available as an Entity. In the middle of a foreach you could get an Object Null Reference exception.

To fix this problem you need to find one field in your view that won't be null, set it as the Entity Key, and remove the other keys the system automatically generates. You'd be tempted to right-click on the entity's field and uncheck "Entity Key", or to go to properties and set the Entity Key property to false. You can do these things, but you'd be only affecting the conceptual model - the CSDL. This is not enough. You need to view the EDM in its XML format, and remove the unwanted keys from the Storage Model (SSDL) as well.

Unfortunately, this works until you need to update the EDM from the database, which will regenerate the models and you'll lose your changes. The Entity Framework team knows about these sort of problems and will most likely try to address them shortly, whenever this may be.

I hope this post can help you prevent wasted debugging time.

1 comment:

Eisen said...

Another option (although equally crappy) is to create a table valued function and then select from that in your view. Then you can be explicit in what the column definitions are.