.NET ORM solution with class auto-generation: Subsonic, Castle AR, ...?

Question

I used to work with a custom data mapping library, and curently I'm trying to switch to a more widespread ORM solution.

After some experimentation, I refined my requirements to the following:

• able to generate usable classes from database schema (SQL Server support is enough),
• support for ActiveRecord pattern,
• programmaticaly configurable (via code or attributes, no HBM files),
• free.

Could you please recommend one?

---

So far I have tried:

Subsonic 3.0

The one I currently like most, as it feels like a good balance between functionality and simplicity.

What I don't like:

• uses IQueryable<> and plural names for both ends of one-to-many relationship - that seems rather counter-intuitive to me;
• generates one file for all classes - like a thousand lines per class, I have a bad feeling about code files being that large;
• T4 processing is invoked automatically, so a database with up-to-date schema must be available all the time.

Castle ActiveRecord

CastleAR with ActiveWriter add-in is almost what I need, but ActiveWriter's code isn't perfect (it seems, Nullable<> properties are not supported, and default one-to-many implementation doesn't work) and I couldn't find how to fix this code manually.

Darkside GeneratorStudio produces better code - correct definitions, one file per entity - though it uses cryptic names like RefclassIdRefclass. Personally I like Studio addin more than standalone app, but this certainly is a minor issue.

There're about twenty files bundled in the CastleAR distribution; though not a problem by itself, it feels like overweight. Won't I need too much esoteric knowledge about NHibernate and other stuff?

Fluent NHibernate

Widely recommended, but I wasn't able to find a code generator for it.

Have I missed something important?

Answer 1

NHibernate does not follow the active record pattern, it follows the repository pattern.

FluentNHibernate's goal is the end of needing to maintain mapping files, code generation etc. It supports Fluently defined C# equivalents of NHibernate HBM files which is very nice and alot more maintainable than XML however it's much stronger aspect is its auto mapping capabilities. If your database is designed with strongly defined conventions FNH can be configured that it will generate the correct mapping of all of your domain objects correctly to your database structure.

If you're more interested in designing the database and having that dictate your domain model (this would make me feel very dirty) there is a question here devoted to NHibernate Generators

Answer to your comment: NHibernate itself does not alter the database itself however there is a utility class included called SchemaExport that provides a factory method called Create. You can use this to generate the SQL statements that it would take to create your database the way NHibernate sees your relationship structure. And can be optionally either directly run against the database or write to the console.

This utility is very useful for forward generation of your domain to your database, it's currently how I am writing my database for all my new application development. I am still working on figuring out the best way to maintain versions of the database. Worst case scenario would to be just SC the sql output and require comments for each line that changes the schema to have it's specific update/delete statements to achieve the result. This would make it easy to roll forward/backwards by version or just execute the entire script to make the db from scratch.

If you go purely for reverse mapping, there should be tools available to generate the corresponding HBM your database needs to map to the equivalent classes (ie if you would have written those classes originally in your domain model it would export a matching schema of your current database)

Answer 2

A few thoughts on the things you don't like about SubSonic:

• uses IQueryable<> and plural names for both ends of one-to-many relationship - that seems rather counter-intuitive to me;

True, I agree this is very counter intuitive and I've never got to the bottom of why it is that way, but you can pretty easily modify the templates to fix this.

• generates one file for all classes - like a thousand lines per class, I have a bad feeling about code files being that large;

You'd think this might cause problems but I've yet to come across any and I've yet to see one reported on stackoverflow.

• T4 processing is invoked automatically, so a database with up-to-date schema must be available all the time.

It's only invoked automatically if you edit the t4 files so really you only need an up-to-date schema available when you're making changes to the templates.

Answer 3

I wonder whether the older SubSonic 2.2 may be a better fit for your requirements? It does generate individual classes per table, only regenerates when you tell it to, and supports the ActiveRecord pattern. See more details in the docs at http://subsonicproject.com/docs/Main_Page

Answer 4

We use CodeSmith for Code Generation and can highly recommend it as a generation tool.

If you look at CodeSmith it supports various ORM / Business Object Templates.

We implemented our own CSLA (see http://www.lhotka.net/) templates a few years ago to get to grips with CSLA which is more than just ORM. CSLA is great and works very well and its highly scalable. We have now created our own framework and dropped the core CSLA framework as we didnt need all the features and made things a bit more lightweight.

Answer 5

• able to generate usable classes from database schema (SQL Server support is enough),
• support for ActiveRecord pattern,
• programmaticaly configurable (via code or attributes, no HBM files), free.

1. Linq-to-SQL autogenerates from the database schema. One big problem is the property names are lowercase so you need a tool like this one to rename them. It isn't restricted to SQL Server.

2. It sort of supports the ActiveRecord pattern - it's very close.

3. The classes are configurable via the dbml file which is simply an XML file. Visual Studio generates a designer.cs file that allows you to add to the entities it produces as they're produced as partial classes.

A few others to have a look at besides NH and Subsonic:

• Lightspeed - free for under 8 entities, the link shows some example code. You have to define the mappings from tables to classes via an interface though so it doesn't meet 1)
• LLBlgen - it's not free

Answer 6

Regarding previous Posts CSLA is not an ORM (You do the mapping from the DB to your Business Objects manually). CSLA, NHibernate, subsonic etc do not support the active records pattern.

If you are considering Frameworks that do not support the Active Records pattern then I would consider Habanero (http://www.habanerolabs.com/) this is open source and allows you to build your Business Objects either via an XML configuration or via code. Habanero is far more than an ORM and has a rich domain (Business Object) support with a powerfull object model for representing your Domain objects (Business Objects).