Subsonic ORM experience

I'm looking for new ORM for a important开发者_运维知识库 project, im used to nHibernate with ActiveRecord and I already have a very bad experiencia with EF4, performance and crashing GUI.

So search on web I found the Subsonic, i liked what I read in the documentation.

So, I would like to know if anyone already used the Subsonic and if the experience was good.

Hmm ... well ... how should I put it....

I am currently (as in right now) expending effort to replace SubSonic with PetaPoco. I suppose that says something.

It's not that SubSonic was bad exactly, but it didn't fit my way of developing very well. And for people looking to adopt it at this point, it seems very important to note the absolute lack of activity on the project.

First, the biggest reason SubSonic didn't fit me was LINQ.

There is allure in having compiler checking of all property use, to be sure. However, in practice, it simply was not well suited to querying.

If you stick very closely to class-per-table & ActiveRecord use, I suppose it is ok. But whenever we had to make any query beyond that (anything involving multiple tables or anything beyond the simplest where clauses), it was a nightmare. Associations cannot be used directly in a SubSonic LINQ query, like they can in EF or nHibernate, which was probably the largest pain point.

For example, a query like this will not work in SubSonic, but it would in EF:

db.Accounts.Where(a => a.OwningUser.Email != null);

Where I ended up was either making many round trips to the database to assemble a result, or using SubSonic's CodingHorror class to query directly with SQL, and being unable to simply materialize them as a POCO (again, when going beyond simple class-per-table).

I also found that every LINQ provider supports different sets of operations, and sometimes the same logical operation will have slightly different syntax and use between providers. This made writing most queries very time consuming and error prone. SubSonic's LINQ provider is no shortage of quirky and under-featured. It doesn't come anywhere close to Linq-2-SQL, Entity Framework, or LINQ to nHibernate it terms of supported operations, usability, or speed of execution (be ready to learn new ways of writing joins in LINQ just for SubSonic - and be ready to have some common operations simply not be possible with SubSonic's LINQ provider, despite being known bugs for a year).

In addition to the drag on productivity, it is easy to forget that the LINQ code you are writing is very provider specific. ANSI SQL is far more standard and cross-compatible than LINQ.

LINQ also seduced me with the possibilities of reusing code with techniques like Specifications, but fleshing these out was far from easy, and the end result was not even close to worth the effort. The roadblocks I encountered here were largely due to the fact that SubSonic's LINQ provider had no support for associations.

SubSonic's facilities outside of LINQ I felt were mediocre at best (in my opinion).

Second, it is important to know that by all measures SubSonic is not an active project.

The initial creator of SubSonic, Rob Conery, no longer works on the project. The last commit Rob made was in July 2010.

The last commit to the project at all was 3 months ago, despite nearly 100 outstanding issues. And as far as I can tell there hasn't been any release, not even a minor point release, since Rob ceased working on SubSonic (though the folks still hanging around the project have been talking about a release for more than half a year).

The Google Group for SubSonic used to be active, but these days not so much. And also the official website for the SubSonic project has been yellow-screening-of-death for a while (The site no longer yellow screens).

The new hotness in data access is micro-ORM's. SubSonic's creator, actually, kind of kicked this trend off with Massive, followed soon after by the StackExchange crew releasing Dapper, and later PetaPoco came out. There's a couple more, too. And while we're giving up a little compiler checking by having SQL snippets in our code base, I find the micro-ORM fits my development style much better than SubSonic did.

My experience (albeit limited) with nHibernate was that it is overly complicated for most scenarios, and even when it is appropriate it absolutely murdered my application start up times. There was also a high learning curve (which you may be past), but also there is several ways to do .. basically everything .. so it just adds that many more decisions into my process (slowing me down).

With PetaPoco, I can write familiar SQL - I am quick and reasonably good with that - and materialize them into POCO's, which I know what the heck to do with immediately. A little sprinkling of architecture and organization and automated integration testing and I don't at all feel dirty about embedding bits of SQL.

Oh, and I suppose last thing - SubSonic is far from the fastest way to get data. May not be important, but it turned out to be for us.

In conclusion (sorry for the wall of text):

It's not that SubSonic is bad in any absolute sense. It just didn't seem to fit the ways I tried to use it well at all - and a large part of that is because LINQ is still a leaky abstraction, and it is leaky in different ways than I am used to.

The fact that development efforts are nearly non-existent is good and bad. Good, it is stable and considered "finished" in a sense. Bad, it lacks features, possibly has some bugs, and isn't the best performer - and there's no one working to improve that.

Some time ago, i was looking for a simple ORM for a small application and SubSonic was just what i needed. The setup is easy and i didn't need much time to add some persistence to my domain classes. What i liked about it, was the option to automigrate the database model based on the domain classes.

The downside of it, is that the feature set is rather limited. The things i missed the most was the option to fetch complete object graphs and the support of additional indexes. SubSonic has it's use as a persistence tool for small apps, but i for important or big apps i would rather use nHibernate or a commercial ORM like LLBLGen.

Before choosing an ORM, you should decide on the basic data access requirements. Do you want to use the Active Record pattern or the Adapter pattern? What about concurrency, performance, inheritance, etc...

I used Supersonic, it's good as long as you are using simple queries. When I started to have more complex queries I saw that it lacks LINQ features. After googling a little I switched to http://bltoolkit.net, and from that time (about 2 years now) I'm very happy with it. Plus is one of the fastest ORM as per http://ormeter.net/. Take a look it, you won't be sorry.