What are your favorite .NET Object Relational Mappers (ORM)?

Question

What sort of Object Relational Mapping (ORM) systems do you use on various projects?

What scale is the project?

What features do you use and look for in an ORM?

Answer 1

I started using EntitySpaces about two years ago on a small project. It is extensible, generated code that maps objects to your db schema. It's cheap, easy to use, and their support forum is a great source of help from newbie to expert.

http://www.entityspaces.net

Answer 2

Small\Medium Projects: SubSonic.

It's really really quick to get up and running with it, and has well developed querying built into it.

Enterprise Projects: NHibernate.

It makes it much easier to separate your domain model from your data model.

Ultimately though I still find that there are many occasions where you still have to bend to the will of the database, even using NHibernate.

In my opinion the only true way to totally separate your domain Model from your database model is to write your own DTOS (essentially POCOs for passing data around), and then map them back to your ORM of choice in your data layer.

Answer 3

+1 for NHibernate. Great support, great resources (the reference guide is good, the new manning book is good). Proven. Flexible. Transparent.

Answer 4

We tried to use Gentle.Net for small, medium projects using Oracle soon we realized that it was too slow, that could be maybe because of the architecture of the company that we develop for, so we developed our own small ORM, it worked really well compared to the Gentle speed and since our projects were not that sophisticated it worked with the most simple features:

• No Configuration files: I think one of the most important features in a .Net ORM is that you can use Reflection instead of configuration files, I find it more straight forward and clean.
• MultiDB: With a couple of plugins you should be able to extend the ORM to any DB.
• Primary Keys, Not Null fields: I think these are the most important tags a field has to have, of course primary keys should support autonumber fields.

It took us (meaning me) like two or three days to develop, and a couple of more to substitute our ORM for Gentle in one of the projects.

That's as far as I can go now, maybe I will be updating this after refreshing the sources of our ORM, but let me know which other features do you think are serious or dead important.

Answer 5

We started to look at DiamondBinding (www.dunnchurchill.com). It's based on a number of open source projects, including NHibernate, but it has a nice VisualStudio add-in which helps with the code generation. On the few occasions that I had to e-mail them, technical support was also pretty good.

The main problem I found was the lack of documentation / sample code, etc. I think it's a relatively young product, though, and they are improving the situation.

More recently we've been looking into Microsoft's LINQ-to-SQL, and I must say I'm quite impressed. Not only do you get the advantage of being able to write queries directly in the source code (even if it is some crazy variation of SQL), but it also handles all of the ORM, too. Because it's integrated with the language, you get strongly typed queries which should cut down on runtime error possibilities.

There are a good series of tutorials on LINQ-to-SQL here.

Microsoft also have another offering which is currently in beta - the Microsoft Entity Framework. This seems (in some ways) to be LINQ-to-SQL's big brother. I haven't had a chance to play with this much, but it looks quite promising.

All in all, for our next few projects, we're going to be using LINQ-to-SQL!

Good luck!

Answer 6

LightSpeed is mine - but I could be considered biased.

Answer 7

Another vote for EntitySpaces here. Started with their free dOOdads after a recomendation and eventual took the decision to upscale to EntitySpaces for the latest project. Well worth the money.

Answer 8

I've used CastleProject's ActiveRecord.

It is built on top of NHibernate, but it handles the complexity of all of the mapping configuration that comes with NHibernate.

Answer 9

We're about to deploy a rather large app on NHibernate. Was my first time using such a thing as an ORM and am happy that I have had the chance to use it. I thought it was going to be a performance nightmare, but we're up to 5000 CCU with some intensive queries. We've had to do some tuning and work out some kinks to get our 2nd level cache right, but it seems to be doing well.

I would like to give DLINQ a try on my next project. I had read some cool stuff about it over at Scott Gu's blog. Too bad my shop doesn't want to give VS2K8 along with SQL Server 2K8 or LINQ.

Answer 10

NHibernate +1.

Just remember to keep a clean divide between your object model and the underlying ORM implementation. Ensure your object model doesn't know about the ORM layer in case you ever need to switch ORM in the future and you'll go far.

Answer 11

We have been using IdeaBlade for the past 2 years. It creates Business objects from a Database. One nice feature, we use, is creating relationships across databases. They also have their own query language, which was strongly typed but a little confusing. This is going to be obsolete in the near future as they are moving towards supporting LINQ as their query language, which is a good choice on their part. I have not used any of the other ORM so I cannot compare it against them.

Answer 12

If you are going to buy one, EntitySpaces is wonderful. NHibernate is ok, but it's got those rediculous XML config files that drive me nuts. There is no reason to use XML when you could generate compilable code instead, thus removing the relection cost and runtime errors with XML syntax/sync issues. Still, I would vote for NHibernate if you don't care about the XML files.

Answer 13

Another vote here for EntitySpaces. I've done a couple of projects using the framework, and have enjoyed good performance, good maintainability, and ease of coding.

Answer 14

I have been using LlblGen Pro since 2003. Great Linq provider. Great technical support.

Answer 15

There is a fundamental difference between SubSonic and Castle ActiveRecord.

They are both ORM's that use the ActiveRecord pattern, but SubSonic takes a buttom-up approach generating classes based on the database schema, while Castle is top-down generating the schema from attributes in the classes.

I like the attribute approach best. One can also depart from the ActiveRecord pattern and use NHibernate directly and generate the bindings with attributes.

Answer 16

HI!! I use EntitySpaces , the last release 2009 is wonderfull. Nhibernate I don´t like it work with xml mapping-files and have slow performance.

Answer 17

Microsoft's Entity Framework has to come into the equation when evaluating ORMs these days. Even if its not right for you, it would well be worth considering EF, given that Microsoft themselves are backing this approach as the "final solution" to the object/relational mismatch problem.

Unfortunately EF wont be the right choice for many, so the ORM discussion is far from over. EF uses XML mappings, and has a steep learning curve. On the plus side, its a comprehensive solution, and having no dependencies outside the core .NET Framework is nice.

If you need to ship code quickly, the best ORM is the one your team is most familiar with.

Answer 18

I like the LINQ to SQL, in which, the data model of a relational database is mapped to an object model expressed in the programming language of the developer.
When the application runs, LINQ to SQL translates into SQL the language-integrated queries in the object model and sends them to the database for execution.
When the database returns the results, LINQ to SQL translates them back to objects that you can work with in your own programming language.

Answer 19

+1 for EntitySpaces. It handles the boring stuff (CRUD) fine but has a pretty solid model for joins as well.

Answer 20

Another vote for Entity Spaces. It is awesome in terms of productivity. I had not heard of it until I started my last job, and every day I congratulated the engineering manager for having found and decided to use it. For a really complicated system, the way that NHibernate or some other system might allow you to create business objects that do not map directly to the database schema might be preferable, but if productivity is important (small team needs to implement a gazillion use cases), it would be unbeatable.

Answer 21

The answer is.... it all depends!

I run my own business and literally go hungry if I don't get a project delivered on time... and I've found that the choice of ORMs can have a significant impact on the duration of a project...

The size of the project (and it's requirements) should drive the granularity of the code design, whereas the type of project (rich client / web / application server) should influence the choice of ORM.

All of the ORMs have their advantages and drawbacks - especially if you just want to use them "out-of-the-box". e.g. Linq2Sql (assuming MSSQL backend) has incomplete support for collection change notifications and doesn't eager load entity relationships in both directions, so it's not a great choice for WinForms / WPF apps. ...Yet it certainly holds it's own in the stateless world of ASP.NET web applications.

Entity Framework doesn't support lazy loading out of the box (some would say that's a good thing), but offers it's own object query language, making it a better choice for middle-ware / application server scenarios. You can safely bet on EF being pushed-for-gold-standard, given how heavily Microsoft are investing in it.

Mindscape's Lightspeed offers a more comprehensive entity model, where relationships, collection change notifications, lazy loading and entity attaching work as you'd expect them to - which makes it a superb choice for desktop applications that need to display reasonably small volumes of interactive "master-detail-more detail" style data. In the down-side, they haven't implemented caching or projections for LINQ correclty, and their entity model makes primary keys readonly, making it a particularly undesirable choice for web applications and stateless services.

NHibernate, OpenAccess, EntitySpaces and Subsonic all have their pros and cons too, each varying on degrees of support for performance, projections, LINQ, true-POCOs and [worthwhile] caching.

One word of warning: I'm a fan of Ormbattle.net, but don't take those stats as the final word of which ORM to go for. Evaluate an ORM for yourself and stress test it!!!. I forked out my own money for a Lightspeed license, only to find out it chokes when materializing > 2000 records in a simple inner join query. When the database query takes < 1 second, but the ORM takes > 60 seconds to materialize the entities, then that's a problem. OrmBattle.NET shows Lightspeed as a top contender on object materialization - which goes to show that, as I said, it all depends!

Answer 22

I use XmlDataMapper a simple free ORM.

I use it for small/medium projects.

Features I look for:
1. Ease of use
2. Easy to understand (by Developers)
3. Support easy templating using t4
4. Which leaves low memory footprint.
5. Do normal Data Access operation out of the defined ORM standards operations when necessary.

Answer 23

I use this: http://valueinjecter.codeplex.com/wikipage?title=Data%20access%20layer%20%28ORM%29%20with%20the%20Value%20Injecter&amp;referringTitle=Home