I'm in the early stages of creating a new development project, and I'm not sure how to set up my database access strategy. I will use Visual Studio 2012, and I will configure for .NET 4.5 and SQL Server 2008 or 2012.
I am not sure whether to use the Entity Framework, and if so, to what extent. Since reading data from the database and subsequent processing will be the main task for this application, query performance will be important. I know that EF5 is much better than EF4.x in this regard, but this is not the inherent EF overheads that concern me the most (although something like Dapper is still at least twice as fast), but more this gives you laziness as a developer, because there are too many queries using LINQ. Therefore, I want pure SQL queries to be the primary way to retrieve data.
However, what I will miss the most in EF:
- Check compilation time.
- Change tracking.
- First code development.
- Unit of work template.
I can live without tracking changes, it’s usually not that difficult to determine what’s new or updated.
I want the developers of this project not to have to interfere with the work of table designers, but they could just write POCOs. Therefore, for this I really appreciate the first approach to the EF code. In this case, the developer can clone the source code, call update-database and have a working local database. This is what worked well for me in the past.
Another thing that is very important to me is a single work pattern or atomicity of inserts and updates. I want to queue all changes and have one point where I call SaveChanges . In libraries like DapperExtensions , you get the Insert method, but it immediately makes a database call. You can make it atomic by wrapping a transaction around it, but it's not the same as in the queue. Therefore, for this, I would need to start some kind of queuing mechanism.
To check the compile time, I use SQL Server Data Tools (SSDT). Requests will be stored procedures (to avoid large blocks of the query string in C # code), and this can be verified using SSDT during build. Another advantage of SSDT is that you can deploy stored procedures from Visual Studio to the target database. And most importantly, SQL scripts for them will live in source control.
Thus, my solution will consist mainly of three data access technologies:
Entity Framework
- He will be responsible for creating the database from Datamodels POCO.
- It will be used to insert / update data through the template of its unit of work. One caveat would be that you would first have to
Attach entities that you selected through SQL in context.
SSDT
- Will be used to test SQL scripts at compile time.
- Allows scripting to live in Git.
- It will deploy what EF cannot deploy to your database.
Dapper / other Micro ORM
- Will be used to retrieve data
I cannot help but feel that this is a bit of a Frankenstein solution, where I use different pieces. I'm also not sure that SSDT and EF will work together beautifully. This quick example seems to work fine:
// 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 to work, but it is also confusing. But if I refuse SSDT, I will miss checking SQL compilation time, if I refuse Entity Framework, I will skip code and lighter inserts, and if I refuse direct SQL, I will miss a big chunk of performance.
Is there an alternative I'm missing out on? If not, what is the best approach here?