Home Forums SQL Server 2005 SQL Server 2005 Strategies nHibernate RE: nHibernate

  • September 4, 2009 at 9:29 am

    #1049466

    I can see the argument against table level access whether the danger is real or perceived (I think it varies from shop-to-shop). That being the case, it may be helpful to know that NHibernate will allow you to use stored procs. Using stored procs for selects effectively negates much of NHibernate's flexibility. However, the best of all worlds may be achieved by going this route:

  • Use NHibernate generated queries to do selects off of views. This gives the dba an abstraction that allows for table reorganization, but allows the devs to write a fetch strategy that is ideal for each context via HQL, ICriteria, Linq2Nhibernate, or native SQL. Having NHibernate generate the queries will cut down on a lot of multiple round trip/ n + 1 select scenarios that occur when using one size fits all read procs.
  • Use procs for CUD. This negates the need for table level access.

    • The only major item left now is the performance of the select queries. It is imperative to use a profiling tool such as NHProf[/url], or at least a SQL profiler. As with SQL, NHibernate queries have to be composed properly or else you'll get nightmarish queries (n + 1 is always something to look out for).

    This isn't my idea, but rather something one of the NHibernate devs suggests. I have not attempted this implementation, so I'm not sure what other downsides it has for the application developers. I'd have to guess that some of the popular usage patterns may not work. However, even if that is the case, I'm sure most developers would rather make this concession over writing a painful, bug filled, inconsistent, home grown mapping layer. I hope to give this implementation a try on a project in the near future.