Subsonic ORM experience?

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

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 and use LINQ pretty easily.

– jesus. Tesh Jun 8 at 21:45 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 is very impractical. Very impractical. – qes Jun 8 at 21:58 I'm going to add some more info showing the lack of activity on the project.

– qes Jun 8 at 22:10 +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. – Chris Marisic Jun 8 at 22:14 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?

– Adam Rackis Jun 8 at 22:20.

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 nbernate 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 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 ormeter.net/. Take a look it, you won't be sorry.

I cant really gove you an answer,but what I can give you is a way to a solution, that is you have to find the anglde that you relate to or peaks your interest. A good paper is one that people get drawn into because it reaches them ln some way.As for me WW11 to me, I think of the holocaust and the effect it had on the survivors, their families and those who stood by and did nothing until it was too late.