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

Fabrianne

VladV, please contact me via e-mail through my Capprime website. I have a non-public alpha quality solution that may meet your needs. – Holst

Answer 1

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.

Pascal

Thanks, I haven't yet experimented much with manipulating the templates. T4 processing is also invoked on project loading. I'm not sure it's 100% reproducable, but I once saw Subsonic template fail because of server unavailability just after existing CS file was cleared, so class definitions were erased and not re-created. – Fabrianne

Are you sure it runs on project load - i've never experienced this. Maybe i've just always had a database there...? – Weslee

I was wrong, it doesn't normally run on project load. I thought that I saw it before, but wasn't able to reproduce. – Fabrianne

Glad to hear that, it didn't make sense to me that it did that. Could you edit your question to reflect this so others reading it won't be confused? – Pascal

Answer 2

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)

Emmott

Does FNH auto-mapping mean that I have to maintain domain model and database separately, or will it alter the database for me? I feel uneasy about automatic database modification, as I don't see how it could be safely supported (say, if there are several configurations - dev, test, production - with different versions of the app, and the app is allowed to change the db schema, after some time there will probably be a mess in the database). – Fabrianne

Thanks for the link, but most generators there are producing XML mappings, that I'm trying to avoid. – Fabrianne

Added information to my answer, and yes all NH generators will produce HBM (XML) mapping files. FNH is a construct that sits ontop of HBM to avoid having to write the HBM yourself. To my knowledge currently there is no tool for direct mapping of HBM to FNH mapping – Emmott

@Chris: ActiveWriter is an NHibernate generator that produces Castle ActiveRecord classes without HBM.XML files. VladV mentioned ActiveWriter in his question. VladV has a database and he wants to produce classes. Your answer mostly talks about going from classes to a database, which is basically the opposite direction. – Holst

@Michael numerous products on the link I posted say they support reverse mapping – Emmott

Answer 3

• 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

Vestal

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.

Owain

Unfortunately we don't have budget for CodeSmith right now. I'll have a look at CSLA, though it looks as a rather heavyweight thing; thanks for the link. – Fabrianne

I have heard that MyGeneration is also very good, its free (I havent not used it tho) – Owain

CSLA isn't really, nor does it try to server as, an ORM. – Holst

Answer 5

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

Hispanicize

Thanks for the idea, I'll try it. Though, SubSonic developer recommends using 3.0 for new projects - personally, I feel that LINQ support is too nice a feature to turn away. – Fabrianne

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

Reparation