Choosing Database and ORM for a .NET project?

Nbernate and Silverlight: One of the N Ayende Rahien, recently posted a blog post about Nbernate and Silverlight: I got a few questions about Nbernate 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 N So, running Nbernate 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 Nbernate and Silverlight. They want to know about Nbernate 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 Nbernate should work as long as you don't plan to use it directly from the Silverlight client.

Nbernate: Nbernate 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 N If you make that investment you will be rewarded by the flexibility and power that N Castle ActiveRecord: Castle ActiveRecord is a framework that is build on top of N and hence is quite similar to N 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 Nbernate 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.

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. " -- this to me sounds like his Silverlight application won't need to be changed at all and he just has a server side returning POCO. I guess that could be clarified because I'm not really sure how Silverlight would impact the decision of the server side ORM (it may or may not?).

– eyston Aug 6 '09 at 15:29 Yes, I agree, but it might be worth mentioning for completeness when talking about Nbernate and Silverlight. – Erik Öjebo Aug 6 '09 at 15:34 agreed, and nice answer. – eyston Aug 6 '09 at 15:54 Yes, let me clarify.. Mentioning Silverlight was probably irrelevant in this question.

My system will be like you mention. Silverlight on the client side - C# behind the silverlight-code - communicating with the server side through WCF - typically returning POCOs. The database and ORM will be on the server side only, and hence not being touched by the Silverlight stuff.

So; no, Silverlight shouldn't affect my choice of ORM. – stiank81 Aug 6 '09 at 17:46 From your answer it sounds like I'd like to use n I'm a little frighten by the mentioned learning curve though.. Would like to use LINQ to do the queries towards the DB, but if I've understood correctly nbernate and LINQ go well together.So; I can tag the code with some nbernate stuff and it will create my database schema automagically? But what about DB?

Should I use MS SQL Server, or does n) support other databases out of the box?E.g. DB2? – stiank81 Aug 6 '09 at 17:51.

Have you looked at . NET RIA Services? What is .

NET RIA Services? . NET RIA Services: From Vision to Architecture .

NET RIA Services Samples.

No I haven't.. Actually never heard about it. Will check it out - thanks! – stiank81 Aug 6 '09 at 17:40 I might have misunderstood, but from what I can see .

NET RIA Services is not an ORM and hence not something I can use instead of e.g. N It seems to be replacing the WCF part in my project, binding the client and server side together. Correct? That being said - .

NET RIA Services certainly looks like something we should look further into! – stiank81 Aug 7 '09 at 12:57.

For a system without an existing database, I think Nbernate + FluentNbernate automappings would be worth a shot: 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: 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 Nbernate 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 Nbernate 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. FluentNbernate 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 Nbernate 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 Nbernate so can't comment on Linq2SQL or EF or other ORM.

As commented on one of the other answers I don't think this choice makes any impact on Silverlight.. Thanks for mentioning ActiveRecord and Fluent - will check it out. But I though Nbernate supported automapping? No?

How do you define the db-schema with N You need to create it yourself manually? – stiank81 Aug 6 '09 at 17:56 edited post to answer comment ... too long. – eyston Aug 6 '09 at 22:41.

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.

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.

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.

I cant really gove you an answer,but what I can give you is a way to a solution, that is you have to find the anglde that you relate to or peaks your interest. A good paper is one that people get drawn into because it reaches them ln some way.As for me WW11 to me, I think of the holocaust and the effect it had on the survivors, their families and those who stood by and did nothing until it was too late.