Best Practice - Mixing Table-Entities with View-Entities in EntityFramework?

Question

I have a legacy database that I'd like to interact with Entity Framework.

The database is highly normalised for storing information about flights. In order to make it easier to work with some of the data, a number of SQL Views have been written to flatten data and to pivot certain multi-table joins into more logical information.

After quickly looking over this I see two problems with using Views in EF.

1. The Views contains lots and lots of Keys. Some quick googling seems to indicate I will need to manually edit the EDMX file to remove this info.

2. The Views don't have any relationships to the other table entities. These associations need to be manually added in order to link a View -> Table.

Both of these seem like major pain points when it comes to refreshing the Model from the DB, when teh DBA team make changes.

Is this just something you need to "put up with" when working with EF or are there any suggested patterns/practices to deal with these.

Answer 1

Mixing Table-Entities with View-Entities is ok and largely depends on your requirements.

My experience has been these are things you are going to have to deal with.

When I first started using Entity, I used views a lot because I was told I needed to use them. As I became more familiar with Entity I began to prefer the use of table-entities over view-entities; mainly because I felt I had more control. Views are ok when you are presenting read-only info, or as you described (flattend data, pivots, joins etc.); however, when your requirements change and you now have to add CRUD, you are going to have to use stored procedures or change your model to use table-entites anyway, so you might as well use table-entities from the start.

The Views contains lots and lots of Keys. Some quick googling seems to indicate I will need to manually edit the EDMX file to remove this info.

This wasn't ever really a problem for me. You can undo keys of the view-entity in the designer. If your talking about doing this for the view in the storage layer, then yes, you can, to make it work, but as soon as you update your model from the database, you are going to have to do this over again -- I wouldn't recommend doing this. You are better off working with your DBA to adjust the key constraints in the database.

The Views don't have any relationships to the other table entities. These associations need to be manually added in order to link a View -> Table.

This was often a problem for me. Sometimes you are able to add keys and create relationships without any problems, but often times you may have to change the keys and/or relationships in the db to make it work -- this depends on your requirements; you may have to deal with this even when using table-entities.

Hope this helps.

Answer 2

I've been in a similar situation as we transitioned into using Entity Framework.

The first step was to start with a blank EF model and add the tables when we created the domain service calls. This at least meant that the model wasn't crazy to start with! Then the plan was to try and not use views as much as possible and move that kind of logic into the domain service, where at least it could be tested, and slowly deprecate the CRUD stored procedures. It's worked fine and there haven't really been any major problems.

In practice there are still some views, mainly used for situations that need to be performant, Fortunately these views can be considered in isolation (for read only grids) and have been left as such in the model with no associations. Adding the keys in would I'm sure be annoying.

Editing the EDMX file is okay, but sometimes on a model refresh these changes can get lost. This has happened to me particularly when EF thinks a table is a view. And yes it's a pain and something that has just been put up with.