Entity Framework 4.1 for large number of tables (715)

Question

I'm developing a data access layer for a database with over 700 tables. I created the model including all the tables, which generated a huge model. I then changed the model to use DBContext from 4.1 which seemed to improve how it compiled and worked. The designer didnt seem to work at all.

I then created a test app which just added two records to the table, but the processor went 100% in the db.SaveChanges method. Being a black box it was difficult to accertain what went wrong.

So my questions are

1. Is the entity framework the best approach to a large database
2. If so, should the model be broken down into logical areas. I did note that you cant have the same sql table in multiple models
3. I have read that the code only approach is best in these large cases. What is that.

Any guidance would be truly appreciated

Thanks

Answer 1

Large database is always something special. Any technology has some pros and cons when working with a large database.

The problem you have encountered is the most probably related to building the model. When you start the application and use EF related stuff for the first time EF must build the model description and compile it - this is the most time consuming operation you can find in EF. Complexity of this operation grows with number of entities in the model. Once the model is compiled it is reused for the whole lifetime of the application (if you restart the application or unload application domain the model must be compiled again). You can avoid this by precompiling the model. It is done at design time where you use some tool to generate code from the model and you include that code into your project (it must be done again after each change in the model). For EDMX based models you can use EdmGen.exe to generate views and for code first based models you can use EF Power Tools CTP1.

EDMX (the designer) was improved in VS 2010 SP1 to be able to work with large models but I still think the large in this case is around 100 entities / tables. In the same time you rarely need 715 tables in the same model. I believe that these 715 tables indeed model several domains so you can divide them into multiple models.

The same is true when you are using DbContext and code first. If you model a class do you think that it is correct design when the class exposes 715 properties? I don't think so but that is exactly what your derived DbContext looks like - it has a public property for each exposed entity set (in the simplest mapping it means one property per table).

Same entity can be used in multiple models but you should try to avoid it as much as possible because it can introduce some complexities when loading entity in one context type and using it in other context type.

Code only = code first = Entity framework when you define mapping in the code without using EDMX.