Perhaps your best bet is using NHibernate. It's arguably the best "industry standard" when it comes to both commercial and open source ORMs. It has been around a long while to become really stable, is used in many enterprise companies, is based on the even better known Hibernate (java), but has fully been rewritten to make the best use of .NET features.
NHibernate drawbacks
This sounds like I'm an advocate of NHibernate. Perhaps I am. But NHibernate has a drawback: it has a steep learning curve and getting used to the many possibilities and choosing the right or "best" practice for your situation can be daunting, even for experienced developers. But that's the prize to pay for an enterprise-level ORM that's capable of virtually anything.
NHibernate with FluentNHibernate rocks
Many of these drawbacks and setup problems vaporize the minute you start using Fluent Nhibernate, personally, I hardly do without it anymore as it removes all the tediousness of NHibernate at once (almost).
It makes working with NHibernate a breeze: just write your entities as POCOs and load them fully automatically to create your database, the associations etc (or don't create the schema if it's there already). Configure your database using the Fluent syntax. A very simple setup can look as basic as this:
// part of a default abstract setup class I use
public ISessionFactory CreateSessionFactory()
{
return Fluently.Configure()
.Database(
MsSqlConfiguration.MsSql2008
.ConnectionString(c =>
c.Server(this.ServerName)
.Database(this.DatabaseName)
.Username(this.Username)
.Password(this.Password)
)
)
.Mappings(m =>
m.AutoMappings.Add(AutoMap.AssemblyOf<User>() // loads all POCOse
.Where(t => t.Namespace == this.Namespace))
// here go the associations and constraints,
// (or you can annotate them, or add them later)
)
.ExposeConfiguration(CreateOrUpdateSchema)
.BuildSessionFactory();
}
// example of an entity
// It _can_ be as simple as this, which generates the schema, the mappings ets
// but you still have the flexibility to expand and to map using more complex
// scenarios. It is not limited to just tables, you can map views, stored procedures
// create triggers, associations, unique keys, constraints etc.
// The Fluent docs help you step by step
public class User
{
public virtual int Id { get; private set; } // autogens PK
public virtual string Name { get; set; } // augogens Name col
public virtual byte[] Picture { get; set; } // autogens Picture BLOB col
public virtual List<UserSettings> Settings { get; set; } // autogens to many-to-one
}
public class UserSettings
{
public virtual int Id { get; private set: } // PK again
public virtual int UserId { get; set; } // autogens FK
public virtual User { get; set; } // autogens OO-mapping to User table
}
which takes all POCO entities and automatically maps them, creates the configuration for the ORM and builds the schema in the database, provided the user has sufficient rights. One very powerful ability of Fluent (and NH to a lesser extend) is to update a database schema when you make any changes.
Other aids to NHibernate
Also on the upside: many auto generation tools exist (including the open source MyGeneration) that can take your DB schema(s) from a simple ODBC or other connection and turn them into the correct entity classes, associations and HBM configuration files. Many of these tools are (partially) graphical design aids.
Use S#arp for enforcing MVC + NH + NUnit best practices
Make sure to read NHibernate best practices. It brings generics and DAO to the next level. You can also skip to the chase and dive deep with S#arp (download), which is a framework that imposes all these best practices and adds NUnit to the mixture.
Before I start using a new technology I usually want it well covered. NHibernate and Hibernate don't come short here. Many books explain (N)Hibernate from starter to professional, white papers are abundant and tool documentation is meanwhile rather excellent.
About LINQ and NH
LINQ and NHibernate have always gone well together through all types of ICollection<> which are used in the many-to-X mappings and other associations, but requires the data to be retrieved first which requires a good design (the cache helps here), otherwise it'll perform badly. This has been considered a sore point of NH ever since LINQ came about.
Luckily, there's now a new kid in town: NHibernate-LINQ, which maps LINQ queries to ICriteria queries prior to submitting. ICriteria queries are well cached and this combination with LINQ is both very powerful and very performant. NH-LINQ is now part of the standard distribution.
Disclaimer
I've used NHibernate for a almost decade (first Java, later .NET). I've flirted with other ORM's both commercial and open source, but in the end always returned to NH (unless company policy demanded different, but that was rare). This story may sound a bit biased but the space here is too short to go into excruciating detail about how NHibernate compares to other technologies.
It's very well possible that other ORM's better fit your need, especially if you never plan to use it in complex multi-database, multi-db-server or hard-to-map-to-OO legacy situations. For me, NH shines because it doesn't limit me in any which way and supports full roundtrip engineering, but your choice might turn out different if features of lighter ORM's that are discussed here weigh heavier for you.
Update: added code sample
Update: expanded code sample, fixed typos and wording
Update: little chapters, added LINQ part, added Disclaimer part
