ORM Comparison: Which comes first the database or classes?

Question

Lately I've been looking into the .NET based ORMs that are available. I've noticed that each ends up siting in one or two camps. In one camp the database is created first, and the ORM provides an easier way to access the database in an application. In the second camp the object model exists first and the ORM facilitates persisting the object model in a database.

Now I'm not asking or asserting whether one camp is better then the other. I can certainly see reasons for each design methodology. What's frustrating me is out of all the tutorials and "getting started" documents I've read lately none just come out and say at the very beginning "this tool assumes you are starting with an existing database/object model". To me this is pretty fundamental to whether you would use one ORM vs another.

So after doing a bunch of reading, and created a couple "Hello World" projects I've put together a series of bullet points on the workflows supported by the ORMs I've looked into. Can anyone with experience with these tools tell me if I've made any incorrect statements, or completely missed any really important points. Specifically I'd really like to know if my assumptions about whether the database schema, or object model should come first with each tool are correct.

Linq To SQL

• Database should exist first
• Only works with SQL Server
• DataContext Class is used to read/write between classes and database
• DataContext can retun real physical classes, or dynamic types can be used to automatically create types based on database schema.
• Mapping defaults to mapping table names to class names, and property names to column names
• Mapping can be customized through attributes embedded in each class

Subsonic (Active Record)

• Database should be created first
• Works with a number of database technologies
• Classes are generated automatically from existing database schema using T4 templates
• Connection to database is totally transparent once classes are generated
  • Calling class constructors automagicaly create records in database
  • Changing property values automaticly updates database.

Subsonic (Simple Repository)

• Class structure should come first
• Works with a number of database technologies
• Repository class is created and connected to a database
• Database schema is created and updated automaticaly when classes are added to the repository
  • repo.Add<MyClass>(instance);
  • Repository uses reflection to create/update database schema
  • Create a table for each time, and a column for each property

NHibernate

• Either database or class structure can be created first
  • Mapping may be created to match a new class structure to an existing database
  • Mapping can be used to automatically generate the database schema
• Works with a number of database technologies
• Classes inside final assembly are attributed with NHibernate mapping settings which map classes and properties to tables and columns
• There are two methods to add mapping configuration
  • XML files embeded in the binary, <classname>.hbm.xml
  • Attributes added to the code
• Supports advanced mapping configuration including one to one, one to may, many to one, many to many, inheritance etc..l etc...

Answer 1

Continuing from the comments which brought up Entity Framework:

Entity Framework (wikipedia also has some nicely structured information)

• supports both code first, model first and database first development. The difference between code first and model first is that in code first you write the entity classes first, while in model first you design the data model first and entities are generated based on it.
• based on a metadata model (EDMX) (although it is arguably absent from code first) which also defines mappings; the EDMX is an XML model containing the database structure, the entity structures and the mappings between them and is supported by a designer built into Visual Studio. In code-first, mappings are defined in code rather than in EDMX.
• supports multiple database technologies (I've used MySql and Oracle).
• based on T4 code generation (in v4), which besides allowing for interesting extensibility scenarios, can generate:
  • entities derived from a base class specific to Entity Framework (EntityObject)
  • POCO entities which don't depend on Entity Framework at all
  • self-tracking entities.
• works well with RIA services (Silverlight).
• supports pretty much all relationship types I think, and inheritance with multiple strategies (although there may be some problems with some of them).
• very good support for Linq (Linq to Entities).

There's also LLBLGen, which I haven't used, but from the comments of one of my co-workers it doesn't stand up as great.

I've used NHibernate before, albeit briefly, and the impression was good; even though back then it wasn't as mature as it is now, it was still a very good library. Not sure if I had to choose between NH and EF now... I think I'd go with EF because that's what I've been using for the last year or so and development would go faster (for me alone), but feature-wise NH may be slightly better off.

Answer 2

Honestly, any semi-decent ORM can handle both database first and code first design.

All of the ORMs included in your question (including EF 4 and LLBLGen Pro 3) can do either, but there can be different amounts of pain. For example, doing code first for LinqToSql is not really what it was intended to do, but I believe there are open source projects where that feature was "bolted on". That said, there is basically no good reason to recommend LinqToSql given that Microsoft is pushing everyone toward Entity Framework instead.

Right now, NHibernate probably has the best overall code first story. It's hard to venture an opinion on which ORM has the best overall database first story, given that they all pretty much support it, and that particular use case is not really what you should be basing your decision on.

Pick an ORM based on whether it's a good ORM. The good ORMs support both database first and code first well.

Answer 3

I am also evaluating couple of ORM tools. I wrote few blogs about my experiences. May be it will help you

http://nileshgule.blogspot.com/2010/09/entity-framework-part-2-comparison.html

http://nileshgule.blogspot.com/2010/09/nhibernate-code-first-approach-with.html

http://nileshgule.blogspot.com/2010/08/entity-framework-hello-world.html