Best Practice - Mixing table objects with views in EntityFramework?

Question

I have an outdated database with which I would like to interact with the Entity Framework.

The database is fully normalized for storing flight information. To simplify work with some of the data, several SQL representations were written to smooth the data and to turn certain joins with several tables into more logical information.

After a quick look at this question, I see two problems using Views in EF.

• Views contain many and many keys. Some quick search queries seem to indicate that I will need to manually edit the EDMX file to remove this information.

• Views have no links to other table elements. These associations must be added manually to link the View → table.

Both of them seem to be the main causes of pain when it comes to updating the model from the database, when the DBA team makes changes.

It’s just that you need to “put up” when working with EF or there are any suggested templates / methods to solve these problems.

Answer 1

Mixing table objects with View-ok objects is okay and largely depends on your requirements.

My experience is what you have to face.

When I first started using Entity, I often used views because I was told that I needed to use them. As I became more familiar with Entity, I began to prefer using table entities over view objects; mainly because I felt that I had more control. The views are in order when you present read-only information or as you have described (flattend data, pivots, join, etc.); however, when your requirements change, and now you need to add CRUD, you still have to use stored procedures or change the model to use table permissions, so you could also use table objects from the beginning.

Views contain many and many keys. Looks like specify that I will need to manually edit the EDMX file to delete this Information.

This was not a problem for me. You can cancel the keys of the view object in the designer. If you talk about it for a repository-level view, then yes, you can make it work, but as soon as you update your model from the database, you will have to do it again - I would not recommend it. You are better off working with your DBA to configure key restrictions in the database.

Views have no links to other table elements. These associations must be added manually to link View → Table.

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

Hope this helps.

Answer 2

I was in a similar situation when we switched to using the Entity Framework.

The first step was to start with an empty EF model and add the tables when we created the domain service calls. This, at least, meant that the model was not crazy to begin with! Then the plan was to try not to use the representations as much as possible and move this logic to the domain service, where at least it could be tested, and slowly abandon the CRUD stored procedures. It worked perfectly, and in fact there were no serious problems.

In practice, there are still some views, mainly used for situations that must be fulfilled. Fortunately, these representations can be viewed in isolation (for read-only networks) and are left as such in the model without associations. By adding keys, I am sure that you will be annoying.

Editing the EDMX file is fine, but sometimes when updating the model, these changes may be lost. This happened to me, especially when EF thinks the table is a point of view. And yes, it's a pain and something that has just come to terms.