O/RM Choice (not reliant on linq)

Question

Hey Everyone, I like using SQL, and I think I am pretty good with it.

And I tried to use Linq (with c# and Linq2Sql, looks like it feels more SQL-like with VB.Net), I feel it is very frustrating, you cannot just run your query without compiling and executing the code. I am not so great with IQueryable, IEnumarable, IList etc. And I have my doubts about why would you use Linq when you are a pro SQL user.

But I need some kind of O/RM so I can write my test codes, and I like using POCOs rather than datasets.

So considering everyone in my team is also some degree of SQL Expert (mostly MS SQL and hence T-SQL), and enjoying database programming, which O/RM you would recommmend. (Commercial or OpenSource does not matter, good support, tutorials and documentation is a must)

Our Development Environment will be C#, ASP.Net MVC along with ExtJS.

We have a little more than 1000 tables on our application that we are going to migrate. You would not believe how much code is generated on it by ADO.Net Entity Framework. I tried a bit Castle ActiveRecord also, (I dont know anything about (N)Hibernate and there was not enough ActiveRecord documentation to keep me going)

Answer 1

But I need some kind of O/RM so I can write my test codes, and I like using POCOs rather than datasets.

Then I would strongly suggest SubSonic [as if otherwise I'll not :)].

Answer 2

Not really an answer, but try LINQPad for a week before dismissing LINQ as a query language ^^

The rules are simple:

• Locate the shortcut for SQL Management Studio on your Start Menu and move it some place else.
• In its place, insert a shortcut to LINQPad.
• For the next week, perform all your ad-hoc SQL queries using only LINQPad.

Answer 3

Take a look at iBatis, my understanding is that it is a much thinner layer around SQL than most OR/M solutions.

Answer 4

Here's a list of Tools: OR Mapping

Answer 5

In my Java days I used Hibernate, and since moving to C# a few years back, I've been using nHibernate. Like iBatis, its a port from a Java ORM and therefore gets benefits from features developed on both sides. The documentation is pretty good too. You can even find a few books out there.

Answer 6

The best ORMs support Linq, so that won't really be an issue either way. LinqPad will definitely help you up the learning curve. Linq has significant advantages, so you really should try to leave your T-SQL comfort zone and embrace it.

With 1000 tables, I would definitely stay away from LinqToSql and EntityFramework, they just don't scale effectively. You are also going to want something with decent code generation if possible.

There are code generation options for NHibernate, but they are generally either lacking or commercial from what I've seen. There may be some great free product out there and I just haven't found it yet. I don't know if Castle ActiveRecord is exactly what you want, but it's definitely one way to get started and you can always transition to other patterns on top of NHibernate later.

I don't know that SubSonic has enough features to support the large applications written on top of a 1000 table database, but it is likely a good fit otherwise.

LLBLGen Pro is a commercial product that I think most closely meets your needs.

There has been a lot of other discussion on SO and elsewhere about choosing a .NET ORM:

http://stackoverflow.com/questions/1377236/nhibernate-entity-framework-active-records-or-linq2sql

Answer 7

Check out Worm. It generates sql as professional DBAs do. In additional it supports caching.