The Basics of Entity Framework, Part I

The Entity Framework is Microsoft’s data access technology that’s been under development since 2008.  It’s very powerful, very complete, and yet competing technologies thrive (nHibernate and LLBLGen comes to mind).  Part of that lies in the difficulty of understanding the Entity Framework, and how it works.  As a prime example of that difficulty, consider that the Entity Framework – much like MVC – is code by convention.  That means that when something isn’t working you can’t walk through the code with a debugger and diagnose the issue, or see the malfunction occur.  Instead, what’s missing is a particular word or attribute or something that the Entity Framework engine is looking for.  You don’t know what that is unless you’ve gotten experience with EF.

Eventually I’d like to see this be a series that spans the complete use of Entity Framework.  I’m going to try and take it very slow, especially when introducing new aspects of EF and how to work with them.  While Microsoft has several ebooks on the use of Entity Framework in different scenarios, the introduction of EF by itself is rarely approached.  More often, it’s combined with MVC because the two can be tightly coupled.

My approach will be a console application.  This simple approach will allow us to concentrate on the Entity Framework rather than trying to balance two technologies.  Also, my approach will be code first.  What that means is that we write the code representing the objects we want to persist first, rather than attempt to disassemble a database into objects.  The Entity Framework supports both models.  I found it easier to learn when using code first, and I liked how much more in control I felt during this process.

In terms of the database, which we will eventually involve in the process, we’re going to work with SQL Server.  Any version should do, although I’ll be using SQL Server 2016.  We could use LocalDB as many Entity Framework tutorials do, but LocalDB is not for Production usage, or high usage in general.  It should also be stressed that LocalDB looks and feels like SQL Server when using Visual Studio to talk to it.

So, as you might imagine, with the code first option we start with Visual Studio and a new Console Application.  We don’t need a database of any sort; this will be created when it needs to be.  Once you have the project open, open a NuGet console (Tools -> NuGet Package Manager -> Package Manager Console) and issue the command

The version installed from NuGet as of the writing of this post was 6.1.3.

Next, we’re going to start with the creation of our POCOs, or plain old C# objects.  The POCOs should represent the data you wish to persist to the database.  We’re going to concentrate on two objects with a many to one relationship.  We’re going to work with students and teachers to begin with.  This model also gives us plenty of room to expand the schema as we need to make things more complicated.

We can see that we have a multiple (Student) to one (Teacher) relationship here that we’d expect to see modeled in any database we created.  We can see a variety of data types, and a definitive lack of any markup code – attributes, specifically.  By convention, Entity Framework is going to pick up the Id columns and use those as auto incrementing, primary keys.  (It looks for Id or ClassNameId.)

How do we move this into a database, complete with CRUD functionality?  We use a DbContext class to handle that.  A DbContext class is just a proprietary class written to convention to help notify the Entity Framework of objects which should be made available.  The DbContext class is a bridge between your POCOs and the database they’ll be stored in.

OK, first, the name of this class matters.  It determines the name of the connection string that’s read from the app.config (or web.config) for your application.  By calling the base constructor as we have here, we’ve overridden that value – albeit to the same value it was.  You’ll also want to note the full namespace and name of this class for Entity Framework configuration coming up.

Second, the DbSet effectively marks a class as being part of the database, and marks the related data as being queryable.  For each view or table you want to query from this context, you need to add a DbSet declaration.

To finish this off we need to add a connection string to the config file for our project.  The LocalDB connection string is a bit odd (side note:  I heartily recommend for all your connection string questions).

Some notes:

  • If you receive any exception look to the InnerException for more complete details.  Many EF exceptions are raised as “Framework initialization failed.” and the only clue as to what actually happened is in the InnerException.
  • If you receive an exception that an EntityType has no key check that you used properties and not variables when setting up the details of your POCO. A variable is going to be ignored; it must be a property.

Finally we need to exercise the code in order for it to be generated.

We won’t have any results, of course, because we have no data – but we’ll cover that next.  This will create the database for us and allow us to take a look at what gets generated.


We can see some unfortunate decisions being made but Entity Framework is in the end just a tool.  And without more information from us, it’s going to make poor decisions.

First, there’s no relationship between Student and Teacher.  Second, all of the strings were mapped to NVARCHAR(MAX).  Third, it pluralized the table names, from Student to Students.  (OK, that last is up for debate.)

So we explored how to define a POCO object for Entity Framework, and how to configure Entity Framework to translate our POCOs, via DbContext, into tables inside SQL Server.  Next up we’ll examine how we can manually specify attributes to control the generation of tables and specify a foreign key relationship.

Design Pattern: Prototype


The Prototype pattern specifies a single instance of a class, initialized with desired data.  This Prototype is then copied as needed to construct additional objects.


Use the Prototype pattern to create objects independent of each other, how they’re composed, and represented.

  • when the classes are dynamically loaded at runtime
  • to avoid building a hierarchy of factories which parallel the object hierarchy.
  • when the variation of the possible state of the objects consist of only a few possibilities.


  • Hides the concrete implementation from the client.


Prototypes are especially useful when the determination of initialization requirements have a high cost.  They’re also useful in situations where the user configures an object and that object is then replicated, factory style, until the configuration changes.




As a sample implementation we’re going to configure a factory which products military units in a game.  The prototype will be used to spawn the units in the configuration the user chooses.

We start with the MiilitaryUnit class and Stance enumeration.  These are the units which we will spawn from our factory bearing the user configuration.

You can see that I’ve overridden the ToString() method to show the differences between the objects being produced.  I apologize; I didn’t feel like whipping up a 2D graphical interface to represent the MilitaryUnits.

Next up is the Factory which uses the Prototype to spawn others.

We have only one toggle we can activate on the Factory; the stance on the unit.  OffLeash probably means more to you as Aggressive; OnLeash would classically be the same as Defensive.  Static would be stand still.

Finally we have the code to execute the pattern.


Useful T-SQL Statements

This snippet will give you the first day of the current month of the given date.


This snippet will give you the Monday of the current week when the first day of the week is a Sunday.


This snippet will give you the Monday of the current week when the first day of the week is something other than Sunday.


This snippet will give you the first day of the year for the given date.


This snippet will give you the first day of the quarter for the given date (standard year).


This snippet will give you midnight for the provided date.


This snippet will give you the last day of the current month.


This snippet will give you the last day of the prior month for the given date.


This snippet will give you the last day of the year for the given date.


This snippet will give you the first Monday of the month.