Help me choose between linq to sql and nhibernate based on the following

Question

Struggling between choosing linq2sql and nhibernate.

Let me give you some insight in the application in point form:

• this is a asp.net mvc application

• it will have lots of tables, maybe 50-60 (sql server 2008)

• i would want all the basic crud logic done for me (which I think nhiberate + repository pattern can give me)

• i don't have too complicated mappings, my tables will look something like:

User(userID, username) UserProfile(userID, ...) Content(contentID, title, body, date) Content_User(contentID, userID)

So in general, I will have a PK table, then lots of other tables that reference that PK (i.e. FK tables). I will also have lots of mapping tables, that will contain PK, FK pairs.

Entity wise, I want User.cs, UserProfile.cs and then a way to load each object. I am not looking for a User class that has a UserProfile property, and a Content Collection property (there will be maybe 10-20 tables that will related to the user, I just like to keep things linear if that makes sense).

The one thing that makes me learn towards nhibernate is: cross db potential, and the repository pattern that will give me basic db operations accross all my main tables almost instantly!

Answer 1

linq2sql really wants you to work with 1 table per class mapping. So if you have a UserMaster and a UserDetail table, you are looking at two objects when using default linq object generation. You can get around that by mapping linq entities to business entities (see Rob Conery's storefront screencasts), but then you are back to writing object mapping code or using something like Automapper.

If you want to be able to split your classes across multiple tables, then I'd say go with NHibernate. If not, then linq has a lower learning curve.

Answer 2

The only way I'd ever use nHibernate in through Castle Project's ActiveRecord library. Otherwise, nHibernate becomes its own little infrastructure project. Check out some questions in the nHibernate tag to see what I'm talking about.

The only thing I might change about AR is to return results of SELECT operations as List instead of T[]. Of course, with the source code in C# I can do that if I want.

With ActiveRecord, the mapping information is saved in attributes you decorate your classes with. It's genius and I am a huge proponent of the pattern and this particular library.

Answer 3

Since you seem to have a quite straight forward mapping from class to table in mind Linq to SQL should do the trick, without any difficulties. That would let you get started very quickly, without the initial work of mapping the domain manually to the database.

An alternative could be using NHibernate + Fluent NHibernate and its AutoMapping feature, but keep in mind that the Fluent NHibernate AutoMapping is still quite young.

I'm not quite sure I understand what you want your entities to look like, but with Linq to SQL you will get a big generated mess, which you then could extend by using partial classes. NHibernate lets you design you classes however you want and doesn't generate anything for you out of the box. You could kind of use POCO classes with Linq to SQL but that would take away all the benefits of using Linq to SQL rather than NHibernate.

Concerning the repository pattern, and the use of a generic repository, that can be done quite nicely with Linq to SQL as well, and not only with NHibernate. In my opinion that is one of the nice things about Linq to SQL.

If you probably will need support for other databases than SQL Server, NHibernate is the only choice. However, if it probably won't be an issue I would recommend not using that as the primary factor when choosing. The other factors will probably influence your project more.

Conclusion:

All in all, I would recomment Linq to SQL, in this case, since it would let you get started quickly and is sufficient for your needs. The precondition for that is that you don't have a problem with the thought of having generated, messy code in your domain, and that you are quite sure there will not be any need to support other databases in the future. Otherwise I would recommend NHibernate, since it is truly an awesome ORM.