Full Stack Developer – Part 2 – The Data Layer

Git  Code

If you review the current The Globe In My Bucket List Application (TGIMBA) or the .NET Core 2 version I am creating, you will notice that I create a fairly fat data layer.  I use the traditional System.Data.SqlClient classes because I like the depth and control.  My experience with the Entity Framework in the past has been a bit of an acquired taste.  I have inherited someone else’s implementation and it involved a bunch of code placed into the DbContext (a class the Entity Framework gives you to access models and other parts of the data store).  This by itself is not necissarily bad, but it is really hard to debug.  Additionally, past Entity Framework versions have been a bit buggy. (my opinion).  So, let’s dig in and see if it has gotten better 🙂

Using the process from reference #1, I was able to install the required nuget packages.  I installed them in this order:

  1. Microsoft.EntityFrameworkCore.SqlServer
  2. Microsoft.EntityFrameworkCore.Tools
  3. Microsoft.VisualStudio.Web.CodeGeneration.Design

NOTE: I did not use the package manager console for these steps.  I used the Nuget Wizard.

If you continue on with this tutorial, you will be building your database from code.  This is totally fine, but not my personal preference.  I think the Entity Framework (like Linq) is geared towards developers who do not like writing SQL and/or dealing with a database.  Most code is iterative and the database is really meant to be used in a batch mode.  That can be a difficult bridge to cross.  For better or worse, I have always done my database ‘stuff’ in the database, so that I why I created it the way I did in the last blog post.

So, how can we create the Entity Framework layer for my existing awesome database?  There is a cool feature (always been there as far as I am aware) that will build your models from an existing database.  In the past, there was a wizard like tool where you selected your database and added any tables and/or stored procedures and that the wizard built everything for you.  That seems to have changed.  The first reference I found (#2) refers to a scaffold builder.  So, using the relevant steps from that tutorial, I created the models by running this command in the package manager console:

Scaffold-DbContext “Server=DESKTOP-JMAB4BG\SQLEXPRESS;Database=MyAwesomeDatabase;Trusted_Connection=True;” Microsoft.EntityFrameworkCore.SqlServer -OutputDir Models

Note: I had to select the ‘Data’ project from the project drop down in the Package Manager Console because I kept getting ‘Project Default not found’.

packageconsoledefaultproject

First time through, it created the location table, but not the detail. Reviewing the error in more detail, I realized that I had not set the primary key on the detail table. Once this was done (just like I did for the location table), I ran it again and it succeeded (or more specifically, create the models and dbContext).

The ‘components’ are as follows:

  • Location Model

locationtable

  • LocationDetails Model

locationdetailtable

  • DbContext

dbcontext

As I stated earlier, the entity framework has not been my preference in the past because many implementations just stuffed any custom code inside this DbContext and it was a bit of a mess. However, I am going to see if that was just a previous developer being lazy and put any custom code I need in an extension class or something.  The beauty of the Entity framework is that basic Create Read Update Delete (CRUD) functionality is provided for you.  I have always had to create all of this by hand in my past projects.  Ideally, the only custom code I will need to create is anything involving the join between the location and location detail tables.

Next on this track will be the Web Application Programmable Interface (API) that the front end will call.

Stay tuned!

References

  1. https://docs.microsoft.com/en-us/ef/core/get-started/aspnetcore/new-db
  2. https://docs.microsoft.com/en-us/ef/core/get-started/aspnetcore/existing-db
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s