...Say what now? Why not just Entity Framework for my data access layer like normal?
There are several reasons you might find yourself in this position. Perhaps the DBA does not want to allow you to write records at the table level for added security or you might be batch processing a lot of rows at once... or perhaps the database is in such a state that accessing views might be more preferable. For this project, that requirement meant eliminating Entity Framework as an ORM option for the application - as it requires direct access to the schema in order to reflect the data objects properly.
Translation: Now I must think about database work a little more.
Kind of a pain in the easy world of plug and play EF. There are always trade offs when determining which architecture piece is the priority: ease of development or additional security approaches. In this case, I needed to put my DBA hat on. The solution involved utilizing a Micro ORM that would execute a stored procedure a layer above the individual tables in the aforementioned SQL database. For this approach a micro ORM was selected: Dapper - the lightweight, quick and nifty framework that allowed you to handle CRUD in a variety of ways with anonymous objects (important if your ORM will have no true knowledge of the database schema).
Re-thinking Repositories
I had several rows that needed to be inserted in bulk. In other words - I didn't want to insert the rows one by one. The performance would be nasty. I had an average of 50 rows to insert all at once that a stored procedure would need to handle. So how do I hand those off to the SPROC within my data access layer in code?
This is starting to sound complicated. Can I give a stored procedure a collection of objects?
Why yes, yes you can. With a table-valued parameter, utilizing user-defined table types.
Great!...What does that mean and how is it done?
It means you decide what the object properties are at the SQL level in the User-Defined Table Types folder in the database (see screen shot below) and that is the data type the stored procedure will expect to receive as a parameter - the entire data table. You just model it after the object you are sending.
What does a database-level data type have to do with Entity Framework's capabilities?
At the time of this project (early 2015), EF did not support user-defined table types. It does not import them from the database and convert to a virtual object that you can access in code and then hand off to an ExecuteStoreProcedure() operation. So, I would loop through my objects and painfully, individually insert them one by one... but this post isn't about the ORM choice (which are ever evolving and add features all the time) - it's about understanding and harnessing type tables to handle your bulk lists of objects and insert them in ONE TRANSACTION.
Why do I care about inserting rows in one transaction? What's the big deal?
Depending on the amount of data, bandwidth. Give it all to the SQL database and let it do the lifting - that's what it's made for, handling large amounts of data. It's important to realize what is going on when you call an Insert() in an ORM. When it comes to scaling up larger data sets, sometimes you need to get your hands in deeper to get more control over the end result.
A Little More Effort for a Lot More Efficiency
This approach is a bit more hands-on but I'd argue that until I did a project like this I never really thought much about how to make data insertion more efficient. Now this is something I can really utilize for shuttling a list of objects to a stored procedure. Table-defined parameters are a great option.
Editor's note: See this post for an excellent how-to with a NuGet package that you can now add onto EF that will handle part of this for you.
No comments:
Post a Comment