Which ORM should I use together with ServiceStack and an existing database

Question

I am currently developing a web service which provides basic CRUD operations on business objects. The service will be used by legacy applications which currently use direct database access.

I decided to use ServiceStack instead of WCF due to ServiceStacks great architecture.

However know I am trying to decide wether to use OrmLite, nHibernate or Entity Framework to access the existing legacy database.

Requirements for the ORM are as follows

• Support for joins
• Support for stored procedures

I already tried OrmLite (as it's fast and already included with ServiceStack). The only way I managed to join two tables was by using SQL (not an option). Is there any better way?

// @stackoverflow: This is my POCO DTO
public class Country
{
    public long Id { get; set; }

    public string Alpha2 { get; set; }

    public string Alpha3 { get; set; }

    public string ShortText { get; set; }

    public string LongText { get; set; }
}

public class CountryRepository : ICountryRepository
{
    // @stackoverflow: This is the query to join countries with translated names stored in another table 
    private const string CountriesSql =
        @"SELECT C.Id, C.Alpha2, C.Alpha3, L.ShortText, L.LongText FROM COUNTRY AS C INNER JOIN LOCALIZATION AS L ON C.LocId = L.Id WHERE (L.Lang_Id = {0})";

    private const string CountrySql = CountriesSql + " AND C.Id={2}";

    private IDbConnection db;
    public IDbConnectionFactory DbFactory { get; set; }

    private IDbConnection Db
    {
        get { return db ?? (db = DbFactory.Open()); }
    }

    public List<Country> GetAll()
    {
        return Db.Select<Country>(CountriesSql, 0);
    }

    public Country GetById(long id)
    {
        return Db.SingleOrDefault<Country>(CountrySql, 0, id);
    }
}

The example above shows one of the simple business objects. Most others require Insert, Update, Delete, multiple Joins, and Read with many filters.

Answer 1

If all you need are joins (lazy-loading or eager loading) and stored procedure support and want to get setup quickly then Entity Framework and nHibernate are great options. Here is a cool link about EntityFramework and the repository and unit of work pattern. http://blogs.msdn.com/b/adonet/archive/2009/06/16/using-repository-and-unit-of-work-patterns-with-entity-framework-4-0.aspx

If you are very concerned with performance and want more control over how your classes will look (ie POCOs) and behave then you can try something more lightweight like ORMLite or Dapper. These two are just thin wrappers with less features but they will give you the best performance and most flexibility -- even if that means writing some SQL every once in a while.

You can also use hybrid approaches. Don't be afraid to mix and match. This will be easiest when using POCOs.

I think the important thing is to code for your current database and current needs. However, to do so using proper interfaces so if the time came to switch to a different database or storage mechanism then you simply have to create a new data provider and plug it in.