How can I solve this NHibernate Querying in an n-tier architecture?

Question

I've hit a wall with trying to decouple NHibernate from my services layer. My architecture looks like this:

web -> services -> repositories -> nhibernate -> db

I want to be able to spawn nhibernate queries from my services layer and possibly my web layer without those layers knowing what orm they are dealing with. Currently, I have a find method on all of my repositories that takes in IList<object[]> criteria. This allows me to pass in a list of criteria such as new object() {"Username", usernameVariable}; from anywhere in my architecture. NHibernate takes this in and creates a new Criteria object and adds in the passed in criteria. This works fine for basic searches from my service layer, but I would like to have the ability to pass in a query object that my repository translates into an NHibernate Criteria.

Really, I would love to implement something like what is described in this question: Is there value in abstracting nhibernate criterion. I'm just not finding any good resources on how to implement something like this. Is the method described in that question a good approach? If so, could anyone provide some pointers on how to implement such a solution?

Gasworks

Why are you trying to abstract and hide NHibernate? You should read ayende.com/blog/4567/… and ayende.com/blog/3955/repository-is-the-new-singleton then re-consider your design – Hypochlorite

i would add ayende.com/blog/4784/… – Galbreath

Answer 1

abstracting away the ORM will:

• bring a lot of work of redefining it's API
• make it impossible to optimise/batch database access
• make it a lot harder to understand what queries are executed
• will lead to tons of SELECT N+1

and all for very little value: the vague option to exchange the ORM framework which will most probably have a lot of other problems

• missing features
• subtle difference in implementation
• learning curve

Update: experience

I was once involved in implementing a new provider of an existing DAL abstraction. It ended up performing badly, introduced a lot of bugs, Errorhandling was a mess and sometimes used stale data because the application assumed the default implementation. Reasons:

• Caching does not know context
• Cacheimlementation had different semantics
• batching APIs too different to be abstracted
• Errors are specific to implementation (e.g. FileNotFound -> FilesearchDialog is uselesss for a tcp/ip based databases)
• Error recovery is different (each implementation has it's own set of errors it can recover from)
• locking mechanism was different
• no consistent change event in SQL-Databases
• nested transactions
• default implementation bleeded in Model classes
• reimplementing all abstracted Queryies was a lot of work and introduced a lot of copy paste bugs
• querying without explicitly stating the order will return different ordered results in different implementations

It took a lot of refactoring of the application:

• strip out features only one implementation provides
• Cachemanagement for each implementation
• problem of Identity of Wrappers because of transient data
• implement Queries over two datastores very hard

Additional points:

• Migration of Data through the abstract DAL is slow as hell
• implementing yet another implementation will never occur because of the above stated problems it is too expensive (In the mentioned scenario we began to slowly reimplement the whole project)
• it was extreme difficult to implement the correct semantics of the DAL API because there is no context of use in the pure API

Porting (of business tasks) would have been a lot less painfull IMO as we did that for a few because of performance.

Update2: experience2: RoadBlocks while trying to port from NHibernate to EntityFramework (impl with NH but couldn't with EF 4 in reasonable time)

• nested Transactions
• Enum support
• references with compositeId (how to get rid of referenceIds)
• references in Components
• read batching (Futures) which is handy for page + count in one go
• mapping CultureInfo (IUserType support)

Galbreath

To make this answer complete you really should provide some cons to NOT abstracting the ORM away, like PITA testing – Fortuitism

there is no big difference between testlist.ForEach(e => session.Save(e));session.Flush();session.Clear(); and var mock = new Moq<SomeRepository>().Call(s => s.GetSomeThing(...)).Returns(testlist).Object; – Galbreath

Does the first one hit the database? Not trying to be a PITA myself but curious how you deal with this... – Fortuitism

No PITA here, I'm glad to learn from others. I'm using SqliteConfiguration.Standard.InMemory() and each test gets its own session. So this hits the database but since all tests have their own inmemory one, i don't have a problem with speed and isolation. QueryOnly tests could share the same session. Default/test data could be injected by a method you'll write once or for each test individual data. – Galbreath

Thanks, I have seen this approach but can never seem to export the schema correctly on test setup and end up having to hand massage the generated sql. – Fortuitism

This is the first thing i do when mapping a legecy database: make sure schemaexport will generate the correct schema (use IAuxiliaryObject if needed). Benefits: 1) i can go back in time, generate the old schema with default data and be up and running 2) unit testing is easier 3) Switching the database is much less work 4) no scripts must be kept up to date for installing – Galbreath

Thanks for your concise point of view do you have any links for IAuxiliaryObject? What actually is this? – Fortuitism

see nhforge.org/doc/nh/en/index.html#mapping-database-object or the simple in code equivalent new SimpleAuxiliaryDatabaseObject("Create ...", "DROP ...", /*supportedDialects*/ new HashedSet<string> { typeof(...Dialect).FullName }); – Galbreath

The SqlLite solution still requires that you setup a lot of data not germane to your test in order to satisfy FK's. Since ISession.Query<T> is implemented as an extension method and is therefore unstubbable, I prefer to use a Generic Repository implementation coupled with a LINQ-aware Specification Pattern. (I wrote a specification library here: link. In the case where I need the advanced functionality of an ORM, I'll settle for integration tests. Generic Repository + Specification handles 95% of my production and testing needs though. – Balsa

setting up complex data for tests is a simple import of a test or production database. Transactions handle isolation. Wrapping linq (which is specification already) into a specification is often additional work for little benefit. I never felt the urge to stub session.Query<T> because i want the query to be executed on the system i plan to work with (NH) – Galbreath

Answer 2

Thank you for the responses! I understand what you are saying, but these answers do not solve my problem. Because of the state of the system I'm writing, I am unable to change my architecture. Instead, I am just going to keep all of my sql/hql/criteria api queries inside of the repository layer instead of trying to expose some sort of a complex query class to my services. This approach should work just fine. For my next architectural approach though, I will consider the points made in the other answers.

Gasworks