Choosing Database and ORM for a .NET project

Question

I'm working on a .NET application using Silverlight on the client side. Now I've come to the point where I want to throw out my static dummy data on the server side and add a database instead.

For the database I'd love to use one of them ORM's where I can simply tag my model classes and the database tables are built for me. I did some tests with Groovy and Grails earlier, and thought GORM did a smooth job. What's the best way to set up a database in .Net?

The first thing that strikes me is to use nHibernate. I don't really know anything about nHibernate, but I've heard numerous people mention it with enthusiasm. But then I see that ADO .Net also is an ORM, which is built into the framework.. Does nHibernate outbeat ADO? And what's the deal with LINQ? I see that's listed as an ORM too, but I though LINQ was more for the query part? Can I "define" the database through LINQ?

Any comments and recommendations are welcome. I'd also love to hear if you have opinions on what database to use. I assume MS SQL Server is the easiest choice?

Answer 1

Have you looked at .NET RIA Services?

What is .NET RIA Services?

.NET RIA Services: From Vision to Architecture

.NET RIA Services Samples

Answer 2

For a system without an existing database, I think NHibernate + FluentNHibernate automappings would be worth a shot:

http://wiki.fluentnhibernate.org/show/AutoMapping

I haven't used automappings before (I'm given a schema to use), they would be my first idea to investigate.

The second choice, and probably more reliable / proven is ActiveRecord:

http://www.castleproject.org/activerecord/index.html

Again, haven't used this either, but I believe you mark your entities with attributes to specify the mapping.

The key to both of them is that NHibernate can export the mapping to the database creating the tables automatically.

I also have no idea how this impacts Silverlight.

---

In answer to comment (easier to type here than comment):

What NHibernate supports is the ability to take the mapping files defined in the project and export them to the database. This means that you define your schema once (your application).

Now, normally those mapping files are xml. This isn't a big deal, but obviously room for improvement.

FluentNHibernate lets you (a) define mapping files in code or (b) automatically create the mapping files from your entities via convention. I use method (a) because my db is terrible and there is no convention in this world for it, but for your use I would recommend (b) which is automapping. The wiki link above gives a quick example of how it can map Product and Shelf.

Active Record works by letting you define the schema not in XML but as attributes on your entity. It is much more than that though as it also implements the ActiveRecord pattern that RoR uses (Entity.Save, etc). I have never used this, but it is widely used.

One great thing about the NHibernate schema generation is unit testing. I can create a test database in SQL Lite in a few lines of code, export the schema, populate with test data, run the test, and tear it down. It makes integration testing easy, and also helps test that queries are working properly.

I don't know if these are the best solutions as I really only use NHibernate so can't comment on Linq2SQL or EF or other ORM.

Answer 3

NHibernate and Silverlight:

One of the NHibernate contributors, Ayende Rahien, recently posted a blog post about NHibernate and Silverlight:

I got a few questions about NHibernate and Silverlight. That is actually a very easy thing to answer.

Don’t even try. They don’t get along. In fact, they aren’t even going to get along.

Silverlight doesn’t have System.Data.IDbConnection, and you can safely assume that that it somewhat important to NHibernate.

So, running NHibernate inside a Silverlight application, presumably in order to access a local database is out. But I don’t think that this is what most people actually had in mind when they ask about NHibernate and Silverlight. They want to know about NHibernate on the server and Silverlight on the client.

And that is easy enough to answer as well, it is going to work just like any client / server system. All the same rules apply.

So NHibernate should work as long as you don't plan to use it directly from the Silverlight client.

NHibernate:

NHibernate is a great ORM but it has a quite steep learning curve, so you should be prepared to invest some time into learning the framework if you choose NHibernate. If you make that investment you will be rewarded by the flexibility and power that NHibernate provides.

Castle ActiveRecord:

Castle ActiveRecord is a framework that is build on top of NHibernate, and hence is quite similar to NHibernate. It reduces the learning curve a bit, since it adds some additional abstractions. As the name implies it is build for use with the ActiveRecord pattern, and includes a base class that gives you quite a lot of functionality if you don't mind using their ActiveRecord base class.

LINQ to SQL:

LINQ to SQL and the ADO.NET Entity Framework are two ORM:s that are included in the .NET Framework. LINQ to SQL is a smaller and simpler framework than Entity Framework, but it has some nice features, and is quite easy to get started with.

Entity Framework:

Entity Framework is quite easy to get started with as well, but it has a few quite big problems in the current version, since it is still in version 1. However, the next version of Entity Framework will improve and fix many of the current shortcomings.

LINQ and Schema Generation for the Frameworks:

All of these frameworks have support for using LINQ as the querying language. LINQ to SQL and NHibernate can generate a schema for you based on your domain classes and mapping. Entity Framework can not generate a schema in v1, but v2 will add that functionality.

Answer 4

If you have used CodeSmith before and would like to auto-generate a lot of your LINQ to SQL code you might want to take a look at PLINQO.

Secondly, there is also a tool called SubSonic that can also create a LINQ to SQL data access layer with minimal trouble.

Creating a data acess layer with either of these tools should give you assemblies that can be accessed by your Silverlight user interface.

Oh note, CodeSmith itself does cost money while the PLINQO template and SubSonic generator are free to use.

Good luck with your project.

Answer 5

SubSonic has already been mentioned, but with apologies to Chris, I think it's worth pointing out that SubSonic can generate a database from your model classes for you. Since you specifically asked about that feature.

Answer 6

I would absolutely use ECO. It is free up to 12 classes and have a lot of useful features to offer. It is centered around the model and use OCL to navigate in the model. OCL is a lot easier to use than SQL or LINQ in my opinion.

for example this constructed expression in OCL:

Person.allinstances.address->select(hasGeoPoint).streetname

In this example there are 2 classes, Person and Address. A Person can have an address and an Address can have a geoPoint (in this case this is indicated by a boolean flag) and a streetname. The expression above filter all streetnames that have a geopoint from all persons addresses.

Another important feature in ECO is derived attributes that let you calculated values from other attributes (that in turn may be derived). The value is then cached from the first query taht saves CPU time. ECO can also be used from an existing database.