What is a good lightweight ORM for our needs?

Question

Ok I know "what X should I use" is very broad so let me narrow down our usage scenario. Basically, we should have been using an ORM a long time ago. Now though there is no way we can go through and rewrite every line of generated SQL Queries in our C# code. But we want to at least take a few steps in the right direction. So when we write new code(and in free time with refactoring) we want to convert over to some sort of ORM.

What would be a good ORM for this purpose? We are using .Net 3.5 and ASP.Net(webforms). There is nothing insanely complex about our database except for a few tables have a "dynamic" schema. We have a numerous amount of views(not used by the web application much though) and we have/use very few stored procedures. Our database is SQL Server 2005. Our price range is as cheap as possible and open source is preferred. This is a proprietary project however so we can't use a GPL library and such.

About the dynamic schema: Basically in some instances a column(of varying types) can be added or removed to certain tables.

And our view usage is almost non-existent and we could always take out the stored procedure code.. I believe there is only 2 or 3 in the database.

Answer 1

I would say that for .NET 3.5 you could use LINQ to SQL which is free, supports Views and Stored Procedures, and has a small learning curve. The only thing I think you'd run in to trouble trying to use would be your "dynamic schema" tables (which you might want to elaborate on).

Another Open Source alternative would be nHibernate which is a great ORM, but has a much steeper learning curve in my opinion.

Answer 2

I would second Justin on suggesting Linq-to-SQL - it's really not dead!

If that's not your choice, or if you need to go against something like SQLite, you should definitely also check out Subsonic 3.0 - lightweight, easy to use, free, with source - you name it.