52

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?

Improve this question
3
  • "having to do transactions that span multiple models, which adds to complexity" Just a note here that you will need to enable Microsoft Distributed Transaction Coordinator. Once you have that up and running it should be simple to accomplish what you speak of.
    – Tjaart
    Commented Sep 10, 2012 at 12:10
  • @Tjaart thanks. I have used MS DTC before and while it is pretty simple, it does add complexity beyond a simple DB txn so I want to avoid it whenever possible.
    – RationalGeek
    Commented Sep 10, 2012 at 13:02
  • 2
    4 yrs later, what did you decide and what would you now recommend?
    – Rory
    Commented Nov 20, 2016 at 13:35

4 Answers 4

Reset to default
33
+150

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.

Improve this answer
3
  • 1
    FYI - There's an NHibernate Mapping Utility that makes these mappings VERY easy, and automated.
    – ganders
    Commented Dec 10, 2014 at 19:34
  • @ganders - Does it have a UI and how's it's IDE integration? I'm assuming you point it to a data source and it respects referential integrity and object traversal and creates the mapping objects?
    – hanzolo
    Commented Dec 10, 2014 at 21:30
  • 1
    Yes it does (GUI). I've had zero issues with it so far. Used it on 4 or 5 different projects/websites. Note: I use it with the Fluent NHibernate, which does the mapping in c# code, not in config/xml files. Here's a link: nmg.codeplex.com
    – ganders
    Commented Dec 11, 2014 at 13:30
13

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).
Improve this answer
4

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

Improve this answer
3

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.

Improve this answer
6
  • This seems like a good strategy, but doesn't really address the question of how to divide entities across different EF models. Do you have all entities in one model or do you divide and conquer in some way?
    – RationalGeek
    Commented Sep 11, 2012 at 20:00
  • 1
    If the OLTP performance is sufficient with the full-model approach, go with that. You can always break it up later if you have to, but the quickest and most agile way is to load the whole thing. You may never need the performance gains you get by breaking it up, so you'd by wasting time and making your system more complicated for no reason. Then there is the question of which model would you stick a new table/entity to when you decide to expand. And what happens when you need to run an update across multiple models. Save yourself the headache unless you really don't have an alternative.
    – Nik
    Commented Sep 11, 2012 at 20:04
  • Forgot to mention that you can always tweak your performance when accessing your data. Look at lazy/eager loading options and which child entities you're bringing in. I see no reason why a full model would behave worse than a smaller one if you're not loading massive object trees.
    – Nik
    Commented Sep 11, 2012 at 20:06
  • i would say massive object trees and a normalized data structure go hand hand when dealing with large schema's
    – hanzolo
    Commented Sep 11, 2012 at 22:30
  • You control how little or how much you want to saturate the object graph.
    – Nik
    Commented Sep 12, 2012 at 0:43

Not the answer you're looking for? Browse other questions tagged or ask your own question.