Which ORM to use?

Question

I'm developing an application which will have these classes:

class Shortcut
{
    public string Name { get; }
    public IList<Trigger> Triggers { get; }
    public IList<Action> Actions { get; }
}

class Trigger
{
    public string Name { get; }
}

class Action
{
    public string Name { get; }
}

And I will have 20+ more classes, which will derive from Trigger or Action, so in the end, I will have one Shortcut class, 15 Action-derived classes and 5 Trigger-derived classes.

My question is, which ORM will best suit this application? EF, NH, SubSonic, or maybe something else (Linq2SQL)?

I will be periodically releasing new application versions, adding more triggers and actions (or changing current triggers/actions), so I will have to update database schema as well. I don't know if EF or NH provides any good methods to easily update the schema. Or if they do, is there any tutorial how to do that?

I've already found this article about NH schema updating, quoting:

Fortunately NHibernate provides us the possibility to update an existing schema, that is NHibernate creates an update script which can the be applied to the database.

I've never found how to actually generate the update script, so I can't tell NH to update the schema. Maybe I've misread something, I just didn't found it.

Note: If you suggest EF, will be EF 1.0 suitable as well? I would rather use some older .NET than 4.0.
Note 2: The ORM framework should be free for commercial usage.
Note 3: I will also use code obfuscation, randomly renaming all symbols etc... so to ORM should support this.

Metrology

Are you tied to using a relational database? You might consider if an object or document database would be more suited to what you're doing. – Dunois

How to generate update script? new SchemaUpdate(_cfg).Execute(true, false); – Kimmi

@Erik Forbes: I would use ODBMS if there would be any usable free object database (something like SQLite for RDBMS). Do you know any? – Metrology

@dotjoe: I will use FluentNHibernate. Is there any way to generate the update script using FNH? – Metrology

Yes, you are using the NHibernate.Tool.hbm2ddl namespace. All you need is access to the Configuration object. The tool simply compares the configuration to the current state of the database. – Kimmi

@Erik Forbes: Yeah I already know db4o, but it is not free for commercial usage. – Metrology

@dotjoe: Thanks, but I would probably need some newbie tutorial for this SchemeUpdate tool, since I can't imagine how it could work in real-life applications. Eg. if user is running app version 1.01, I release versions 1.02, 1.03, ... 1.15, and then he installs 1.15, skipping all the previous versions. How could hbm2ddl handle all those changes in database since the 1.01 version? – Metrology

I suppose you would use the tool as part of the update process. You give them the new exe and have them run your database schema updater. In all honesty, I've never seen a 3rd party database application that seamlessly updates the schema. Usually they provide scripts based on your current version. – Kimmi

@dotjoe: I want to develop desktop application, which uses NHibernate for persistence. I MUST seamlessly update the schema, since it is unacceptable to force the users to run some scripts. All they should have to do is to download new version and install it. Everything else should be done by the installer/app itself. – Metrology

Ok, then your installer would have to compare database versions on startup and use the tool to update schema. I don't see what the problem is? – Kimmi

@dotjoe: I just don't know how SchemaUpdate works, if it removes unused tables, automatically changes column types, removes columns, renames tables, renames columns, etc etc. And I just can't find any tutorial for all of this. – Metrology

Yes, I feel that pain as well. I've found it doesn't work very well for renames. As that is a difficult thing to account for. It's good for adding additional columns, keys, or tables though. This is open source...you can always look at the Configuration.GenerateSchemaUpdateScript method which does all the work. It definitely doesn't remove unmapped tables as that would be very bad. – Kimmi

I will probably use Fluent-Migrator, as mxmissile pointed out. Although it will not automatically do everything for me, I will have full control over the migration, at least. – Metrology

Answer 1

Before .NET 4, Entity Framework was just not mature enough for my tastes. Furthermore, it does not support POCOs.

With EF out, I would select NHibernate. To make configuration code-based and somewhat easier, I would also use Fluent NHibernate. NHibernate is very mature and has lots of community support. It has an excellent facility for updating the database schema from the latest code. Highly recommended.

I don't think any of the others are serious options. Entity Framework is going to gain mind share rapidly because it is built-in and seriously marketed by MS. NHibernate will remain a viable competitor because it has plenty of popularity and maturity. The rest will slowly fall by the wayside until they are only used by small numbers of ardent supporters.

Prehistory

I totally agree.. I'd use NHibernate. It maps perfectly to your requirements and makes database updates very easy. – Licentious

1) And what about EF 4.0? Although I said I don't want .NET 4.0, I'm just curious if EF 4.0 is as usable for my application as NHibernation. 2) Is there any good tutorial for database scheme updating in Fluent NHibernation? I'm not able to find anything. – Metrology

+1 for NHibernate along with Fluent-NHibernate. However, I would add Fluent-Migrations for the schema updates. It makes it painless, just like the rails guys have it. – Blayne

Fluent NHibernate is great help , but sometimes I find I still need to look at the xml files FNH generates though, so I can tweak my mapping files. I was hoping I would never have to learn about NH xml mapping files, but understanding how the xml works is quite essential. – Firecrest

EF4 is still behind NHibernate though it is now quite usable. No doubt, it will end up in more applications than NHibernate thanks to the MS endorsement and the fact that it comes in the box. I know we have had to use EF4 on a couple of custom projects lately because some clients prefer the commercially supported option to open source. Code-based schema updates are not available in EF4, but I expect it to get in a future release. VS2010 has some very nice deployment technology including a facility to do database schema updates. – Prehistory

I guess the best option will be to use non-EF right now, and switch to EF5 in a few years, when .NET 5.0 is released and installed on majority of computers. – Metrology

Answer 2

I think you're comparing two separate classes of systems here:

• SubSonic and Linq-to-SQL are fairly simple, thin layer on top of a database. They provide basically only a 1:1 mapping between a database table and a domain object. If that's good enough for your case, then these tools are the simplest, the easiest to use, and the best performing ORM's, too

• EF in .NET 4 and NHibernate are in a quite different class - they provide enterprise-level features, they support multiple databases, they support mapping and morphing your database structure into a different looking domain structure, and they're good at that. EF4 does also support POCO and all the issues that have been used against EF before are pretty much moot with the .NET 4 version of Entity Framework.

  But both NHibernate and EF4 are more complex, they require more setup, more learning curve until you "get it", they're a bit heavier on their feet, they have additional mapping layers that enable those advanced features, but they also cost performance and they make things more complicated in general.

So I guess it really boils down to what your requirements are: if you need a fairly simple and easy to use system, go with Linq-to-SQL or Subsonic. If you need more enterprise-level features and don't mind the extra work needed to setup those ORM's, pick EF4 or NHibernate.

Kenji

Well I don't need some complex features, I just want so store my objects easily, so I don't need to do ADO.NET. And I need to be able to customize anything in the future, so the framework should enable easy customizing without too many hacks. For example, if I change column type, it must not require too much refactoring. And database schema will be updated quite often, so I also need framework which is designed for this. – Metrology

@Paja: in that case, if you use SQL Server exclusively, use Linq-to-SQL, if you need more database support, use Subsonic. – Kenji

I think Linq2SQL is good enough. However I've read that MS is abandoning it. Is it worth to write an application in it? And what if I would also like the system to be an database-abstraction-layer, what would you recommend? – Metrology

@Paja: like Mark Twain said: "Rumors of my death have been greatly exaggerated". MS said they won't be investing their bulk of time into Linq-to-SQL - but it's still here, in .NET 4 - so changes are quite good, it'll also be in .NET 5. Why abandon something just for "fear" that it might not be supported sometime way in the future...... it's here, USE IT! And don't worry - be happy! – Kenji

Does Linq2SQL provide any simple method to do database schema updates? Lets say I have the Shortcut class, and I add property "Location", and change type of the property "Name" from string to Int64, and remove third property "Items". Is there any simple method to update the database schema using Linq2SQL according to these changes? Or do I need to do this manually using ADO.NET? – Metrology

Answer 3

See DataObjects.Net: although it's commercial, there are features you need (e.g. schema upgrade).

Barthol

Thanks, but I would prefer some free solution. Should have written it in question, sorry. – Metrology