Entity Framework with large systems – how to divide models?

I’m working with a SQL Server database with 1000+ tables, another few hundred views, and several thousand stored procedures. We are looking to start using Entity Framework for our newer projects, and we are working on our strategy for doing so. The thing I’m hung up on is how best to split the tables into different models (EDMX or DbContext if we go code first). I can think of a few strategies right off the bat:

Split by schema
We have our tables split across probably a dozen schemas. We could do one model per schema. This isn’t perfect, though, because dbo still ends up being very large, with 500+ tables / views. Another problem is that certain units of work will end up having to do transactions that span multiple models, which adds to complexity, although I assume EF makes this fairly straightforward.
Split by intent
Instead of worrying about schemas, split the models by intent. So we’ll have different models for each application, or project, or module, or screen, depending on how granular we want to get. The problem I see with this is that there are certain tables that inevitably have to be used in every case, such as User or AuditHistory. Do we add those to every model (violates DRY I think), or are those in a separate model that is used by every project?
Don’t split at all – one giant model
This is obviously simple from a development perspective but from my research and my intuition this seems like it could perform terribly, both at design time, compile time, and possibly run time.

What is the best practice for using EF against such a large database? Specifically what strategies do people use in designing models against this volume of DB objects? Are there options that I’m not thinking of that work better than what I have above?

Also, is this a problem in other ORMs such as NHibernate? If so have they come up with any better solutions than EF?

Personally, I’ve tried making one huge schema for all my entities on a fairly complex but small project(~300 tables) . We had an extremely normalized database (5th form normalization (I say that loosely)) with many “many to many” relationships and extreme referential integrity enforcement.

We also used a “single instance per request” strategy which I’m not convinced helped either.

When doing simple, reasonably flat “explicitly defined” listings, lookups and saves the performance was generally acceptable. But when we started digging into deep relationships the performance seemed to take drastic dips. Compared to a stored proc in this instance, there was no comparison (of course). I’m sure we could’ve tweaked the code base here and there to get the performance improved, however, in this case we just needed performance boost without analysis due to time constraints, and we fell back to the stored proc (still mapped it through EF, because EF provided strongly typed results), we only needed that as a fall back in a few area’s. When we had to traverse all over the database to create a collection (using .include() unsparingly), the performance was noticeably degrading, but maybe we were asking too much..

So based on my experience, i would recommend creating a separate .edmx per intent. Only generate what you’ll be using based on the scope of that need. You may have some smaller scoped .edmx files for purposed tasks, and then some large ones where you need to traverse complex relationships to build objects. I’m not sure where that magic spot is, but I’m sure there is one… lol…

Honestly though, aside from a few pitfalls which we kind of saw coming (complex traversing), the huge .edmx worked fine from a “working” perspective. But you’ll have to watch out for the “fixup” magic that the context does behind the scene’s if you don’t explicitly disable it. As well as keeping the .edmx in sync when changes to the database are made.. it was sometimes easier to wipe the entire surface and re-create the entities, which took like 3 minutes so it wasn’t a big deal.

This was all with EntityFramework 4.1. I’d be really interested in hearing about your end choice and experience as well.

And regarding you’re question on nHibernate, that’s a can of worms question in my opinion, you’ll get barking on both sides of the fence… I hear a lot of people bashing EF for the sake of bashing without working through the challenges and understanding the nuances unique to EF itself.. and although I’ve never used nHibernate in production, generally, if you have to manually and explicitly create things like mappings, you’re going to get more finite control, however, if you can drag n’ drop , generate, and start CRUD’ing and querying using LINQ, I could give a crap about granularity.

I hope this helps.

Let me start by simple clarification: I don’t have experience with such large database so the rest of my answer is not based on the real world example.

So you have a BIG database and you want to use it with ORM / EF. I would go with the second choice. Here is my simple explanation why:

Mapping adds complexity. There is no need to add complexity with entities your current application / project / module never needs but don’t make granularity too low level. Having separate mapping set per screen will not help you as well.
You want to achieve unit of work. You should be able to specify what tables module needs in the most cases (not necessary in all cases). If you put these tables into single mapping set you will be able to handle reading and data modification by single context instance – that is what should be your ultimate target.
I’m not sure what exactly you mean by model but even with different mapping sets you can share classes between mapping sets using same entity types. So if you use User table in two modules you don’t need two User classes to represent the same. You can still use single table and in case of code mapping (aka code-first) you can even define mapping once and load it to multiple mapping sets so the DRY principle is not violated but the code-first approach has more limitations when it comes to views and stored procedures. EDMX makes this harder. You can still reuse classes but reusing mapping impossible.
What about cross module queries? These queries may happen but to be honest not everything must be handled by EF. You can take advantage of EF for common cases to simplify regular data access but if you have somewhere need for special query which joins tables belonging to 5 different modules you can simply execute it directly or wrap it in stored procedure. 100% replacement of native data access can be hard, complex and contra-productive.
The last point is simply practical: I don’t believe that VS tooling is ready to work with such large set of objects – not in designer, not even with importing tool. I used to work on very large database with traditional data access and SQL Database project in VS2008 – the user experience with a complex project was very bad. You must keep the number of used tables low – the cap for designer should be somewhere between 100-200 but even 100 tables handled by single context (mapping set) sounds like too much responsibility for one class (suppose that you will have 100 set properties exposed on the context – it doesn’t look like a good design).

I would say you can’t decide this kind of question from a technical perspective. I would recommend that you build your architecture based on your use cases (user stories, etc.). First find your business objects. An entity object is not per default a business object. Typical you will have a business object in front of the entity objects. Then you can incrementally decide what you really need, based on the user requirements.

“A good architect maximizes the number of decisions not made.” Robert C. Martin

http://cleancoder.posterous.com/architecture-deference

I use a hybrid approach — OLTP stuff is handled by EF while heavy operations such as batch inserts, mass updates, report queries, etc are handled by Stored Procs. It also makes the migration path easier if you’re not doing a full re-write of your data layer all at once.

Filed under: softwareengineering - @ 06:29

Thẻ: enterprise-architecture, entity-framework, orm

Thiết kế website giá rẻ

Danh mục

Entity Framework with large systems – how to divide models?