Please recommend .NET ORM for N-tier development

Question

I need to choose carefully .NET ORM for N-tier application. That means, the I will have the server (WCF service), which exposes the data, and client, which displays it. The ORM should support all the related serialization issues smoothly - the objects or collections of objects, or whatever must travel across process boundaries. Ideally, the usage in multiprocess environment should be the same as in single process.

The criteria are:

1. Flexibility of db schema mapping to objects (preferred)
2. Ease of use
3. Free, open source (preferred)
4. Must be suitable for N-tier (multi-process multi-domain applications)
5. Performance
6. Tools to integrate with Visual Studio (preferred)
7. Testability
8. Adoption, availability of documentation
9. Wide range of RDBMS supported (preferred; we are using MSSQL, but I wouldn't like to be tied to it)
10. DB agnostic - different DBs, same API

Answer 1

Why not try NHibernate?

Answer 2

I would recommend Entity Framework v4. It has improved beyond dramatically since v1, and supports everything you require except being open source:

1. EF supports a very wide variety of mappings, including TPH, TPT, and TPC. Supports POCO mapping, allowing you to keep your persistence logic separate from your domain.
2. EF has extensive and excellent support for LINQ, providing easy to use, compile-time checked querying of your model. EF Futures components such as Code-Only simplify working with EF even more, providing a pure code, compile-time checked, fluent API for defining your model. By opting for convention over configuration, Code-Only can radically reduce your model design time, allowing you to get down to business without all the hassle of tinkering with a visual model and multiple XML mapping files.
3. It is free as part of .NET 4. (Sorry, Open Source preference can't be met here.)
4. EF provides an excellent N-Tier solution OOB via self-tracking entities
   • Self-tracking information uses an open xml format to transfer tracking data, so tracking support could be added to non-.NET platforms
5. Performance of EF v4 is very good, as extensive work was done on the query generator
   • See the ADO.NET Blog entry on the subject
6. EF provides extremely rich visual design tools, and allows extensive customization of code generation via custom T4 templates and workflows
7. EF v4 introduced numerous interfaces, including the IObjectSet<T> and IDbSet<T> interfaces, which greatly improve the unit testability of your custom contexts
8. EF v4 is an integral part of .NET 4 and a central component of all of Microsofts current and future data initiatives. As a part of .NET, its documentation is quite extensive: MSDN, EFDesign Blog, ADO.NET Blog, dozens of .NET and Programming sites and blogs provide a tremendous amount of documentation and support for the platform.

Answer 3

Another vote for EF here.

• Very easily unit testable. You can write your own Domain Entities and have them be reasonably free of persistence awareness using POCO approach. You can then mock the database interface and test the application logic without actual database.

• Supports LINQ so that if you write your LINQ properly, it will only translate a single SQL statement being sent to the server.

Answer 4

I've been using a product called LightSpeed, it works very well and seamlessly integrates into Visual Studio 2010 & 2008. I have been using it with Sqlite, however it supports numerous rdbms. It also has a very nice feature that allows you to create POCO objects that can be used with WCF, great time saver! At first I was using the free Express Edition but soon upgraded.

LightSpeed is the best high performance .NET domain modeling and O/R mapping framework available. First class LINQ support, Visual Studio 2008 & 2010 designer integration and our famous high performance core framework means you can create rich domain-driven models more quickly and easily than ever before.

Answer 5

Having worked with the following:

• NHibernate

• LLBLGen

• Entity Framework

• LINQ to SQL

• DataObjects.Net

• OpenAccess

• DataTables

I can most certainly say that DataTables are superior...no just kidding. All of them do have their strengths and weaknesses.

Mainly, I have found that these strengths and wekanesses are associated with the general type of ORM, which falls into the following two categories

• Heavy-weight

  • LLBLGen, OpenAccess, Entity Framework (pre 4.0), DataObjects all fall into this category. Heavy weight ORMs typically have entities and collections that inherit from a base class specific to the ORM (ie. EntityBase). These ORMs often offer rich design time support, code generation and in depth runtime features (such as state tracking, transaction tracking, association maintanance, etc.).

  • The Pro: Easier, faster development upfront leveraging the built in API for interacting with entities themselves at runtime (ie. from LLBLGen entity.Fields["MyField"].IsChanged or entity.IsNew or entity.Fields["MyField"].DbValue

  • The Con: Heaviness and dependencies. With these ORMs, your business objects are now tied directly into the ORM API. What if you want to change to another ORM? And what's to prevent junior developers from abusing advanced features of the ORM API to fix a simple problem with a complex solution (I've seen this a ton)? Memory usage is also a big problem with these ORMs. A collection of 5,000+ entities can easily take up 100MB of RAM with some of the above ORMs. Serialization is another problem. With the heaviness of the objects, serialization can be very slow...and it probably won't work correctly deserializing on the other side of the wire (WCF or .NET remoting, etc.). Associations may end up not re-associated correctly or certain fields may not be preserved. Several of the ORMs above have built in serialization mechanisms to improve support and speed...but none that I've seen offer full support for different formats (ie. you get binary, but not json or xml serialization support).

• Light-weight

  • LINQ to SQL, Entity Framework POCO, NHibernate (sort of) fall into this cateogry. Light-weight ORMs typically use POCOs that you can design yourself in a file in VS (of course you can use a T4 template or a code generator too).

  • The Pro: Lightweight. Keeps it simple. ORM agnostic business objects.

  • The Con: Less features, such as entity graph maintance, state tracking, etc.

Regardless of what ORM you choose, my personal preference is to stick to LINQ, and ORM independent retrieval syntax (not the ORM's own API for fetching, if it has one).

With regard to the specific ones mentioned, here are my brief thoughts: - NHibernate: Behind the times tech wise. Lots of maintainence of xml mapping files (though Fluent NHibernate does alleviate this).

• LLBLGen: Most mature ORM I've worked with. Easy to start up a new project and get going. Best designer. VERY heavy weight. Rich VERY powerful API. Best speed I've encountered. Because it's not the new kid on the block, some of the newer features leveraging newer technology aren't implemented as well as they should be (LINQ specifically).

• Entity Framework: POCO class in 4.0 looks promising. 3.5 doesn't have this and I wouldn't even consider it. Even in 4.0, doesn't have great LINQ support and generates poor SQL (can makes hundreds of DB queries for a single LINQ query). Designer support is poor when it comes to larger projects.

• LINQ to SQL: Great LINQ support (better than any other except DataObjects.Net). Mediocre persistence (save/update) support. Very lightweight (POCO). Designer support is poor all around (no refresh from DB). Poor performance on advanced LINQ queries (can make hundreds of DB queries)

• DataObjects.Net: Really great LINQ support and performance. Offers the closest thing I've seen to POCO in a heavy-weight ORM. Really new, powerful, promising technology. Very flexible.

• OpenAccess: Haven't worked with it a ton, but it reminds me somewhat of LLBLGen, but not as feature rich or mature.

• DataTables: No comment

Answer 6

Having used OpenAccess on several projects, I must say that it meets all the above criteria. One system I worked on was based on WCF services talking to several client types (smart, web, other WCF services, etc.) Through a layered architecture the WCF services used OpenAccess as persistence mechanism. I am especially fond of the scaling that OpenAccess performs.. The intelligent Level 2 cache (L2 cache) does a perfect job there and it is of cause distributable. Actually I wouldn't call OA heavy weight... You don't even inherit from a base class. Also it is a big plus that there are tools to perform the day-to-day developer tasks (create a new DB schema, merge schemas and so on) integrated into visual studio.