Subsonic ORM experience

Question

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.

Loquitur

I've liked the look of Subsonic, but I've only used nHibernate which I understand is a superset of most ORMs.... and using the Fluent version of it, I found nHibernate very friendly to use. – Mallard

Answer 1

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.

Suspensory

There is some good info in your answer but there is a little subjectivity too. Rob hasn't stopped working on SubSonic. It may not be his focus, but he has said as much in posts and podcasts (though he could answer that better than I of course). I guess the second part of that is that he has always stated you can do whatever you want since you have the source code. I guess in that way we MS devs haven't embraced the typical open-source philosophy. Also, not sure if what version you used but you can always use ToList<T> and use LINQ pretty easily. – Guanajuato

I'll readily admit what I wrote contains some subjectivity, and I hope my wording make that evident. Look at the public repository for SubSonic and tell me the last time Rob (or anyone for that matter) made a commit (that's rhetorical). Lastly, saying use ToList<T> is very impractical. Very impractical. – Suspensory

I'm going to add some more info showing the lack of activity on the project. – Suspensory

+1 for detailing your usage of PetaPoco, PetaPoco is by far one of the most interesting projects to me in the DAL land other than EntityFramework 4.1. I have apps in production using PetaPoco also. I find the DSL of PetaPoco to just be the best of all the micro-ORMs. It's straight to the point, without limiting your functionality, and it lets you work with the least amount of handwritten sql that is humanly possibly basically. – Sunbathe

Very interesting post +1. So is the main pull of Micro ORMs support for rapid development—quick and to the point coding without the ORM ever getting in the way? Why would someone want to use this instead of, say, EF 4.1 POCO? More simple? – Nicotiana

@qes I also have to agree with your remarks towards nHibernate. Even after years of invested effort into learning it, and even getting a fairly high level of success with it I find no desire to ever use it again. All of my recent development has used Linq2Sql, PetaPoco, and EF 4.1 code first. If I would've had PetaPoco back when I started regressing to L2S I probably would've used that instead. For a full fledged application built on Sql server EF 4.1 code first really does the job. But it suffers from some of the configuration hell to do more elaborate queries / database optimization. – Sunbathe

I think, @Chris Marisic answered my question. I can see where a certain type of application—one that requires extremely performant queries—would benefit from something like PetaPoco. Controlling the SQL from EF is tricky—still though, EF does give you (I think) ExecuteStoreQuery<TEntity> which lets you load entities from pure SQL. – Nicotiana

@Adam: I would also say EF is still in a state of churn. It also clearly misses the mark on implementation in some regards compared to nHibernate (persistence ignorance, mainly). And not least of which, it is a much heavier dependency to carry than any of the micro-ORMs - and this really should not be underestimated. – Suspensory

@qes I did not mean to offend, so I hope that I did not. The last checkin was mid-March. That isn't necessarily "active", but it isn't "dead" either. Limbo maybe? :) I don't recall what podcast it was on, but something fairly recent - Rob said that while there was not activity in the repo, it did not mean it was dead. Just sharing what I heard... – Guanajuato

@qes you're wrong about persistence ignorance, EF 4.1 code first. The release of EF 4.1 is basically the first version I've ever truly recommended people to use. EF 3+ has been alright if you wanted an experience that was like datasets but less garbage. Now with 4.1 you have full choices, PI models, database to code model generation, and model to database generation. – Sunbathe

@jesus.tesh: no offense at all. I just felt like I've said it many times but never compiled the evidence, so I thought I should here. @Chris Marisic: You are probably more correct than I regarding EF - I haven't put very much time into it at all, particularly the most recent versions. – Suspensory

@jesus.tesh: Also, regarding ToList, that can "work" but often the performance implications make it a completely unacceptable solution. – Suspensory

Answer 2

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...

Junto

Answer 3

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.

Bet