TGIMBA .NET Core API – The New Data Layer using Entity Framework

Git  Code Commit (all commits between last post and this commit (link))

Welcome back!

I am officially kicking off the Application Programmatic Interface (API) blog series.  To catch everyone up, in the last couple of years (or so) I have have updated the .NET 4.6.x Classic version of The Globe In My Bucketlist Application (TGIMBA) to .NET Core 2.1.  More specifically, I created a .NET Core website and placed TGIMBA within it.

Inside the website, I have created Vanilla JavaScript, React Redux JS and Angular Typescript 6 clients.  Each client is fully functional with a shared Selenium set of integration tests.  However, hosting them together has proved difficult.  So, I am only hosting the Vanilla JavaScript client at   This is the only client with Cascading Style Sheet (CSS) skinning as well.  I hope to host the other two client (eventually) on separate Azure sites.  However, before doing that, I want a common API that all three and the eventual mobile application(s) can share.

For this website’s temporary API, I have abstracted the old embedded API back to its service layer which is called directly from the website.  These calls are within a web client, but its just a shell for now.  As I work through the API, I am going to replace the static service calls in the web client with Hyper Text Transfer Protocol (HTTP) calls to the separately hosted API.

The first part of this will be the data layer.  Without a data store, the API would be…well, kinda pointless 🙂  I am going to use the Entity Framework which will connect with the existing table structures.

But, I encountered a bug. Specifically:

Severity Code Description Project File Line Suppression State
Error NU1202 Package Microsoft.EntityFrameworkCore.SqlServer 3.0.0-preview7.19362.6 is not compatible with netcoreapp2.1

I did a little reading and it seems like this was experienced by others as well.  I did not read further because since all I needed was the classes, I thought I could generate them using the Full Stack sample project I had done earlier. To do this, I ran this command in the package manager console for the Full Stack Developer solution:

Scaffold-DbContext “Server=<local db name here>\SQLEXPRESS;Database=<db name here>;Trusted_Connection=True;” Microsoft.EntityFrameworkCore.SqlServer -OutputDir Models

Doing so allowed me to generate the classes I needed…or so I thought.  When I looked at the context class (i.e. the context class that contains the connection object and other Create Read Update Delete (CRUD) functions to interact with the SQL Server Database), it had errors.  More specifically, the generated context class had compile issues.  So, I searched some more.  What I eventually found that worked was the process in reference #2.    The command I used to generated my models and context was this:

dotnet ef dbcontext scaffold “Server=<local server name here>\SQLEXPRESS;Database=<database name here>;Trusted_Connection=True;” Microsoft.EntityFrameworkCore.SqlServer -o Models

Something to note was it did not work at first.  However, running the commands as directed inside the Package Manager Console (I had initially done so through the developer command prompt) fixed this. My generated classes/context look like this:


The rest of this effort was fairly uneventful as I just added a bunch of tests. I re-labeled my old interface and added a new one:


I then implemented them by injecting the db context in through the constructor. Injecting the db context and using it directly in my implementation is normal.  However (I talk more about this in the high lights section below), I did not mock it in my DAL tests. Specifically, it looks like this:

2As for the tests, I structured them around function groups – User, Bucket List Items and Miscellaneous.


Highlights include

  • To do unit or integration tests?  Normally, unit tests are appropriate at this point,  BUT (big but), how does one do that with a tool like Entity Framework?  The ‘point’ of a useful test (my opinion) is to test your application’s logic in isolation.  You mock any dependencies to put your code into different ‘states’ to verify the outcome is as expected.  However, with the entity framework (at least my understanding of it), the ‘logic’ is queries using the frameworks IQueryable functions.  I do not want to test entity framework as it is already tested by those developers.  So, I ended up writing integration tests against my local database.  I felt better about this approach after reading reference #7.
  • In the past, I would normally just thrown ‘Exception’ with a message.  However, after reading a couple articles (see reference #8), I got the impression this was a no-no.  So, I had a couple of cases where if a record in the database does not exist, I wanted to throw an exception that will be handled in the service layer.  Specifically:


I am throwing it in a couple of places.  One example is here:


Next up will be the service layer.  I am thinking the ultimate layout will be something like this:

  • HTTP API Layer (TODO)
    • Service Layer (TODO)
      • Data Layer (IBucketListData/BucketListData + Entity Framework 6)
        • Database (SQL Server)

Stay tuned!



Leave a Reply

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

You are commenting using your 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