Persistence framework?

Question

I'm trying to decide on the best strategy for accessing the database. I understand that this is a generic question and there's no a single good answer, but I will provide some guidelines on what I'm looking for. The last year we have been using our own persistence framework, that although limited has server as well. However it needs some major improvements and I'm wondering if I should go that way or use one of the existing frameworks. The criteria that I'm looking for, in order of importance are:

1. Client code should work with clean objects, width no database knowledge. When using our custom framework the client code looks like: SessionManager session = new SessionManager(); Order order = session.CreateEntity(); order.Date = DateTime.Now; // Set other properties OrderDetail detail = order.AddOrderDetail(); detail.Product = product; // Other properties

   // Commit all changes now session.Commit();

2. Should as simple as possible and not flexible. We need a single way to do most things.
3. Should have good support for object-oriented programming. Should handle one-to-many and many-to-many relations, should handle inheritance, support for lazy loading.
4. Configuration is preferred to be XML based.

With my current knowledge I see this options:

1. Improve our current framework - Problem is that it needs a good deal of effort
2. ADO.NET Entity Framework - Don't have a good understanding, but seems to complicated and has bad reviews.
3. LINQ to SQL - Does not have good handling of object-oriented practices
4. nHibernate - Seems a good option, but some users report too many archaic errors.
5. SubSonic - From a short introduction, it seems too flexible. I do not want that.

What will you suggest?

Thank you

Answer 1

LLBLGen make very good ORM tool which will do almost all of what you need.

Answer 2

iBATIS is my favourite because you get a better grain of control over the SQL

Answer 3

Developer Express Persistence Objects or XPO as it is most known. I use it for 3 years. It provides everything you need, except that it is commercial and you tie yourself with another (single company) for your development. Other than that, Developer Express is one of the best component and framework providers for the .NET platform.

An example of XPO code would be:

using (UnitOfWork uow = new UnitOfWork())
{
  Order order = new Order(uow);
  order.Date = DateTime.Now();
  uow.CommitChanges();
}

Answer 4

Improve our current framework - Problem is that it needs a good deal of effort

In your question, you have not given a reason why you should rewrite functionality which is available from so many other places. I would suggest that reinventing an ORM is not a good use of your time, unless you have unique needs for the ORM which you have not specified in your question.

ADO.NET Entity Framework

We are using the Entity Framework in the real world, production software. Complicated? No more so than most other ORMs as far as I can tell, which is to say, "fairly complicated." However, it is relatively new, and as such there is less community experience and documentation than something like NHibernate. So the lack of documentation may well make it seem more complicated.

The Entity Framework and NHibernate take distinctly different approaches to the problem of bridging the object-relational divide. I've written about that in a good bit more detail in this blog post. You should consider which approach makes the most sense to you.

There has been a great deal of commentary about the Entity Framework, both positive and negative. Some of it is well-founded, and some of the seems to come from people who are pushing other solutions. The well-founded criticisms include

• Lack of POCO support. This is not an issue for some applications, it is an issue for others. POCO support will likely be added in a future release, but today, the best the Entity Framework can offer is IPOCO.
• A monolithic mapping file. This hasn't been a big issue for us, since our metadata is not in constant flux.

However, some of the criticisms seem to me to miss the forest for the trees. That is, they talk about features other than the essential functionality of object relational mapping, which the Entity Framework has proven to us to do very well.

LINQ to SQL - Does not have good handling of object-oriented practices

I agree. I also don't like the SQL Server focus.

nHibernate - Seems a good option, but some users report too many archaic errors.

Well, the nice thing about NHibernate is that there is a very vibrant community around it, and when you do encounter those esoteric errors (and believe me, the Entity Framework also has its share of esoteric errors; it seems to come with the territory) you can often find solutions very easily. That said, I don't have a lot of personal experience with NHibernate beyond the evaluation we did which led to us choosing the Entity Framework, so I'm going to let other people with more direct experience comment on this.

SubSonic - From a short introduction, it seems too flexible. I do not want that.

SubSonic is, of course, much more than just an ORM, and SubSonic users have the option of choosing a different ORM implementation instead of using SubSonic's ActiveRecord. As a web application framework, I would consider it. However, its ORM feature is not its raison d'être, and I think it's reasonable to suspect that the ORM portion of SubSonic will get less attention than the dedicated ORM frameworks do.

Answer 5

Thank you Craig for the elaborate answer. I think it will help more if I give more details about our custom framework. I'm looking for something similar. This is how our custom framework works:

1. It is based on DataSets, so the first thing you do is configure the DataSets and write queries you need there.
2. You create a xml configuration file that specify how DataSet tables map to object and also specify associations between them (support for all types of associations).
3. A custom tool parse the xml configuration and generate the necessary code.
4. Generated classes inherit from a common base class.

To be compatible with our framework the database must meet these criteria:

1. Each table should have a single column as primary key.
2. All tables must have a primary key of the same data type generated on the client.
3. To handle inheritance only single table inheritance is supported.

Also the xml file, almost always offer a single way to achieve something. What we want to support now is:

1. Remove the dependency from DataSets. SQL code should be generated automatically but the framework should NOT generate the schema. I want to manually control the DB schema.
2. More robust support for inheritance hierarchies.
3. Optional integration with LINQ.

I hope it is clearer now what I'm looking for.

Answer 6

I suggest taking a look at the ActiveRecord from Castle I don't have production experience with it, I've just played around with their sample app. It seems really easy to work with, but I don't know it well enough to know if it fits all your requirements