A combination of Entity Framework, Dapper and SSDT?

c# dapper entity-framework ssdt

Question

I'm in the early stages of setting up a new development project and I'm unsure how to set up my database access strategy. I'll be using Visual Studio 2012 and I'll target .NET 4.5 and SQL Server 2008 or 2012.

What I'm unsure about is whether or not to use Entity Framework, and if so, to what degree. As reading data from the database and then processing it will be the main job for this application, query performance will be important. I know that EF5 is a lot better than EF4.x in that respect, but it's not the inherent EF overhead that I'm most worried about (though something like Dapper is still at least twice as fast) but more the laziness it affords you as a developer, because querying too much is so easy through LINQ. So I want pure SQL queries to be the main way to fetch data.

However, what I'll miss most about EF is:

  1. Compile time query checking.
  2. Change-tracking.
  3. Code first development.
  4. Unit of work pattern.

I can live without the change tracking, it's usually not that hard to determine what's new or updated.

What I do want is that the developers on this project will not have to mess around with table designers, but that they can simply write POCOs. So for that, I really appreciate EF's code first approach. With that, a developer can clone the source code, call update-database and have a working local database. It's something that has worked nicely for me in the past.

Another thing that's very important to me is a unit of work pattern, or the atomicity of inserts and updates. I want to queue up all changes and have a single point where I call SaveChanges. With libraries like DapperExtensions, you get a Insert method, but it'll immediately do the database call. You can make it atomic by wrapping a transaction around it, but that's not the same as queuing it up. So I'd have to roll some sort of queuing mechanism myself for this.

For the compile time query checking I consider using SQL Server Data Tools (SSDT). Queries would be stored procedures (to avoid big query string blobs in the C# code) and using SSDT these can be checked at build time. Another advantage of SSDT is that you can deploy the stored procedures from Visual Studio to a target database. And most importantly, the SQL scripts for these would live in source control.

So with that, my solution would basically consist of three data access technologies:

Entity Framework

  • Would be responsible for creating the database from POCO datamodels.
  • Would be used to insert/update data through its unit of work pattern. One caveat would be that you would first have to Attach entities you fetched through SQL to the context.

SSDT

  • Would be used to verify SQL scripts at compile time.
  • Allows the scripts to live in Git.
  • Would deploy the things EF can't deploy to your database.

Dapper/other Micro ORM

  • Would be used to fetch data

I can't help but feel this is a bit of a Frankenstein solution where I use various bits and pieces of everything. I'm also not certain yet that SSDT and EF will work together in a nice way. This quick example seems to work fine though:

// Combo of Dapper, EF and a stored proc that was published through SSDT
static void Main(string[] args)
{
  var connectionString = ConfigurationManager
    .ConnectionStrings["DbDataContext"].ConnectionString;

  using (var conn = new SqlConnection(connectionString))
  using (var ctx = new DbDataContext())
  {
    conn.Open();

    var product = conn.Query<Product>("GetProduct", 
      commandType: CommandType.StoredProcedure).First();

    ctx.Products.Attach(product);

    var order = new Order
    {
      Product = product
    };

    ctx.Orders.Add(order);

    ctx.SaveChanges();

  }
}

This approach seems like it would work, but it's messy too. But if I give up SSDT, I'll miss out on compile time checking of SQL, if I give up Entity Framework, I'll miss out on code-first and easier inserts and if I give up on straight SQL I'll miss out on a big chunk of performance.

Is there an alternative that I'm overlooking? If not, what's the best approach here?

Popular Answer

You should really check out ServiceStack.Orm.

https://github.com/ServiceStack/ServiceStack.OrmLite

It has a TON of features, including model gen using tt files, and it can create db tables as well.

And it supports LINQ.

And its crazy lightning fast.




Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why