Dynamic SQL using Entity Framework is a really cool capability. You can use dynamic SQL for any number of reasons. I have used it for custom filters, custom ordering, and other SQL features when you are able to work in Pure SQL while still having the benefit of SQL Injection protection through the user of execution parameters.
There are other techniques for integrating your SQL with Entity Framework that are beyond the scope of what I’m talking about today. What I’m trying to share here is that you don’t necessarily have to walk away from Entity Framework to have custom SQL that integrates into the EF context and unit of work, allowing an integrated Repository through auto generated methods or custom methods. In short, you can use the Entity Framework plumbing to jump start getting your database connections, executing your queries, and retrieving results. You don’t have to go directly to something like dapper.db or a custom ORM framework using ADO.Net to handle your database execution.
I haven’t done any performance comparisons for using EF to pass SQL queries directly to the database. I do know that I can outperform some EF implemented tasks with custom SQL using only EF as the query pipeline. I don’t know if using some other database connectivity would perform any better, or by how much. I do know that when you use EF as your pipeline for SQL queries, it still uses your EF Configured data models, and evaluates then when you call an EF Repository method. So, that may have an impact on performance because the data you pass in is always evaluated against the configuration of the Data Object Model. Using a different database connectivity tool would allow you to pass anything to your implementation, without evaluation.
In summary, if you use EF to get a project off the ground, you may be able to extend the capabilities y using your own custom SQL, executing it through the EF repository. There’s some food for thought.
Cheers,
Ben