Statically-Typed Hybrid Data Access with Dapper & Entity Framework

The choice between raw SQL and generated SQL isn't mutually exclusive.
There's pros, cons and a use case for both. Like all my POC what-if ideas, this started out by reading a StackOverflow post on dynamically creating enums.

What-if, I could pull the schema of a database and dynamically create a model so that I could statically-type my raw SQL. In v0.001 I did, and it worked!

Going forward I'd like to extend my mapping to stored procedures and views.
My reasoning for this is simple, I want a separation of my application code and database code.
For example, store procedures for use in my application, should be managed in my application, I want it tracked in my source control.
The next dev, shouldn't go directly to the database to find out what query is executing in the procedure, they should have the full history of changes to better understand the context behind the decisions made in the logic.


Before I dive into code, I want to explain my reasoning for both Dapper & Entity Framework:

Dapper, it's simple, everything is an extension method, it can query and map results back to my model fast! Somebody will be that guy and say you're using EF wrong and if you do xyz and use tabs instead of spaces your queries will be faster.
No, just no! I don't want to put in 80% effort to get a 20% gain, that ROI is flawed. It's one the reasons I stay away from Fluent API, I code for the future LioneL 6 months down the line who has to fix something that broke at the very wrong time.

Entity Framework, I'm not a db admin. I still want to manage my database through code, track migrations and for simple queries EF is great.


The reference source code for this post is on GitHub.

To start you're going to need create a console app, also make sure in your solution build order the console app is first. In the Properties of your console app project, inside the Build Events tab that the Post-build event command line is $(TargetPath). This means after building the app is run which will generate the dll.

Create Dynamic Database Model Module Method
This method basically takes the schema for the database tables, creates the class files and then saves all of this to a dll module.

Below is what the dll contains. The method does this all through reflection. I'm not a Intermediate language expert, so I recommend trying this manually and using dotPeek to compare it to what you generate.
Dynamic Mapping Module

After the dll is created. You can reference it in your other projects but we still want all of this done dynamically. Open the .csproj file for all the projects that reference the dll, you're going to make some adjustments. It should look similar to below.

You can now go ahead and statically type SQL. It is up to you if you want to create a new dll on every build, and if a table or its columns change which affects your statically-typed SQL. You'll know at compile time.

$"INSERT INTO {DatabaseMapping.People} ({DatabaseMapping.People.Id},  {DatabaseMapping.People.FirstName}, {DatabaseMapping.People.LastName},  {DatabaseMapping.People.Email}, {DatabaseMapping.People.UserName},  {DatabaseMapping.People.Phone})"