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

Corcyra

What about the ADO.NET Entity Framework? – Sapotaceous

You forgot Entity Framework. With EF you can either do DB first or class first development. You work with the database through the EntitySet. You also have the ability to work with multiple databases using Entity Framework. – Ninebark

I just haven't gotten around to looking into EF. From what I read the 3.5 version of EF wasn't really ready. Sounds like the 4.0 version has improvements, but I just installed VS 2010 at the end of last week. I'd definitely like to hear any answers comparing how the workflow with EF compares to the other ORMS listed. – Corcyra

There are 3rd party tools around that add model first and improve db-first, and enable incremental changes to either the db, the model, or both for some of these ORMs. One such tool is my add-in for VS, it adds incremental DB->model and model->DB changes for both L2S and EFv4 - you can download it from huagati.com/dbmltools if you want to take it for a test spin... – Midis

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.

Kurtz

Sounds like NH and EF are fairly similar now? – Corcyra

I don't think I'm the best one to comment on that but yes, I think they are. NH may handle some things better, like some entity inheritance scenarios, and has more extensions/plugins etc. because it's open source. Don't know about other aspects of the comparison though. – Kurtz

Your co-workers classified LLBLGen Pro as not that great? Could you elaborate on that? – Winburn

Well, now I feel a bit uncomfortable :) It's just the general feel I got from off-topic discussions - and with only one co-worker actually. Since I'm not familiar with LLBLGen I didn't go into much detail in these discussions, but I'll try to when I get the chance. The comment in the answer is highly subjective and uninformed, so I'll try to edit it to be more to the point if I get more information, but that's the reason I made it CW - so that more knowledgeable persons can add to it; please, feel free to list the features in the post and even remove that line if you feel it's unfair. – Kurtz

I've had a positive experience with LLBLGen using a database-first approach (haven't tried code first). @FransBouma is the author of LLBLGen, and I recommend his blog posts. – Choe

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.

Vasectomy

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

Pimpernel