12 Nov

Tips for ORM Data Access

In my last post I took a stab at Lazy Loading data access, and pointed out a concrete example of why I don?t like it. As with anything there are trade-offs. Here are a few practices I use for clean and fast data access. They are heavily influenced by some common patterns from PoEAA, and Domain Driven Design.

  • Retrieve data by aggregate roots, have a repository for each root. The root queries should return the entire aggregate. If you are going to defer loading to a part of the root graph, be explicit. Instead of lazy loading Foo.Bars, I like to say Foo.LoadBars() <- yes seriously
  • Learn how to identify the right aggregate
  • Respect your aggregate root boundaries. Aggregates cannot call into other aggregates children! You shouldn?t be adding a product from the order, really, this is bad: LineItem.Product.Calatalog.Add(new Product). The order AR just crossed into the Catalog AR. Its not the job of the Order to add products to the catalog
  • Do not try an overuse an aggregate, its ok to create a new one. Less is more does not apply. Fulfilling an order is different than placing one, and your data access may need to reflect that
  • Use a ?Read Model?. The most common approach here is using DTO or ViewModel, and projecting straight into it from the object model query or database
  • For complex queries that perform aggregations and calculations, consider using a store procedure. They really have their place, even with the best ORM?s (then project into Read Model)
  • Avoid putting query logic in mappings (Assembler?s, Translators, AutoMapper)
  • Use a tracing tool. I like the SQL trace profiler, but if you are not as comfortable with sql, there is a some good profilers around, like NHProf for nHiberante & soon EFProf for the Entity Framework
  • Know the SQL that is going to your database. Have your tests emit the generated sql out to the console. Live it, learn it, love it. You should know your ORM tendencies, especially if you are using a linq provider
  • Batch requests to your services and database, especially if you are in a web or distributed environment

13 thoughts on “Tips for ORM Data Access

  1. “Aggregates cannot call into other aggregates children”

    You mean only for updates and inserts, right? Customer > Products and Order > Products to me are both okay.

    “For complex queries that perform aggregations and calculations, consider using a store procedure. They really have their place, even with the best ORM’s (then project into Read Model)”

    I would suggest you implement command/query separation as suggested by Greg Young instead.

  2. @ng
    CQS is great, though not always applicable in the context in which I have heard Greg and Udi speak about it, which is usually large scale distributed messaging.

    I think the common concept here, which is not new to cqs, is that I may have one model that I use for complex domain logic, saving & updating, and another simplified one for reading. (or even seperate tiers)

    “Aggregates cannot call into other aggregates children”
    Yes, only for updates/inserts. If your doing CQS they would not be the same anyway right 🙂

  3. Wow! This article found me at just the right time. Funny how that works.

    I have been trying to wrap my head around the role of the DTO in DDD. My reading of Fowler and Evans seems to indicate that you ought to have your domain objects themselves mapping into your database, rather than dedicated function-less DTOs. Relying on DTOs that are then handled by Services seems to lead to what Fowler calls The Anemic Domain Anti-Pattern: http://martinfowler.com/bliki/AnemicDomainModel.html

    However, I have a tough time writing Entity classes that operate in that manner that don’t end up rather painful to change and extend.

    Since you recommend the practice of using DTOs, do you have any thoughts on the subject?


  4. Great post Jarod. Obviously this is something that we’ve talked about a great deal in the past…and in the future I’m sure 🙂

    A few observations specifically on NHibernate:
    1) For tracing of SQL statements NHibernate has excellent log4net integration.
    2) Projections can often be used instead of Stored Procedures and will often generate very efficient SQL.

  5. @Scott Parker
    You don’t need DTOs. They were for EJB1/2. It was for limiting network hits. You need something like a ViewModel or projection as he said. Or whatever you want to call it (a Report object?). Anyway, what it is is a READONLY object that contains attributes of one or more Domain/Entity objects. For [N]Hibernate you would do something like “select new PersonViewModel(p.firstName, p.lastName, a.city, ph.phoneNumber) from Person as p, Address as a, Phone as ph
    where a.person = p
    and a.primary = true
    and ph.person = p
    and ph.primary = true

  6. I don’t agree with avoiding lazy-loading. You can have your cake and eat it too by exposing explicit graph loads via things like Fetch.

    The problem with DTOs and the like is type transference where once you flatten an object you need to manage when that flattened entity needs to be re-retrieved as a “real” entity.

    For example I may pull a list of lazy-loaded objects, then when I go to the details page I may very well decide to fetch all “child” objects that make up the composition at once if I know I’m probably going to be hitting most of the data anyways. I don’t really want to be dealing with different objects that represent different views of the same data.

    As for situations where foo.name = a.b.c.d{.name} I don’t think there’s anything wrong with exposing commonly used “optimizations”. I.e. foo.name = a.dname, where dname = a.b.c.d.name. Nothing more than a simple assertion test is required to assert that a.dname doesn’t start to mean something different to a.b.c.d.name.

  7. Another approach I’ve tended to favor over the past few years is the following:

    Don’t use ORMs.

    Applying the ER model will make you a better OO designer.

    Modeling it yourself will encourage you to be sparing; you’ll gravitate towards 3rd normal form where appropriate.

    Modeling it yourself will reinforce your understanding of the underlying problem domain which will result in a leaner OO model.

    Visual DB design tools = good (no point futzing around with DDL all day).

    Object to Relational mappers = bad. They leak and they’re brittle. They’re an overapplication of the OO metaphor.

    SQL is good. SQL is easy. Specifying the what is easier than specifying the how.

  8. Your post is great, very appealing to me, have helped me the most, let a person can gain a lot of different things. Support for you.I have about sports, very cheap Nike shoes, and I hope to help you.[url=http://www.cheapnikeshoesebay.com/]cheap nike shoes[/url] [url=http://www.timberlandbootssales.com/]timberland men boots[/url]

Comments are closed.