Simple Demo Entity Framework - the beast unleashed

Rahul Pawar LogicMonk

4.96/5 (29 votes)

15 Nov 2015CPOL40 min read

40.3K

Simple Demo Entity Framework - a tutorial explaining Code First!

Content

Introduction
Entity Framework Introduction
Code First Driven Approach
The Project that we will be Developing
The Project - Quick Look at the Domain Classes
Creating DbContext Class
Interacting With the DataBase
Explaining the Database created by Code First
Lets understand CodeFirst Migrations
Interacting with Database - Inserting records in Discussion Database
Interacting with Database - Updating and Querying Discussion Database
Interacting with Database - Deleting Records in Discussion Database
Interacting with Database - Retrieving Graphs of Data Records from Discussion Database
Final Thoughts

Introduction

In this article we will be looking into understanding a Entity Framework 6. especially the Code First Approach.

About EF 6

"Entity Framework" basically comes in 3 flavours:

Database First
Model First
and lastly, the jewel in the EF crown...Code First - the one we will be covering in this article

The goal is to demonstrate every aspect of EF 6 Code First that I can recall. Rest assured I will try my best to cover every detail so as to make your journey along this article as comfortable as possible.

Hopefully, we will learn to appreciate Microsoft a little more than we do now once we are done here :)

On a side note - I have written a couple of articles regarding WCF web services, please feel free to explore them at your convenience.

I will be following the KISS concept: "Keep it Simple Stupid."

So lets get the ball rolling...

Before you proceed, assumption is that: you have Visual Studio 2013 (or prior version) and SQL Server 2012 installed, configured and working in complete harmony. If not it would be a good time to google for Visual Studio Community Edition to start with.

Entity Framework Introduction

Overview

Entity Framework is an Object Relational Mapper ("ORM"). ORM provides benefits including improved Developer Productivity (EF takes good care of pulling and feeding data to Databases) - EF can produce basic commands for reading and writing data to databases thus improving life's of many developers.

The queries are written using the Domain Objects you have declared in you app using something called " LINQ to Entities " and the retrieved results from the database are automatically converted into the Domain objects that we have defined in our software app. Basically, life becomes a lot more easier.

In theory, ORM like Entity Framework let's us focus more on the business side of things rather than worrying about the database too much. In EF we focus on something called the Conceptual Model (of your domain)
Now this is a Model of OBJECTS in your application and NOT the model of your database that you use to persist your applications data.

Now here is the kicker, depending on cases we might have a Conceptual Model that aligns with our database model and on the other hand we can also end up having an conceptual model that does not aligns with our database model.

Yes EF is that flexible. and easy going.

Once, we have our Conceptual Model we can than use a feature of Entity Framework that is called Code First than can create the database tables among other things for us in the database of our choosing.

The bigger picture is Entity Framework is able to map between our conceptual model and the database of what it needs to do when it comes to persisting data with ease-of-use and elegance. So we write queries against our Conceptual Model and Entity Framework makes sure to compose the necessary queries to read/write and update data in database.

How EF does what it does you ask?

You write your queries with LINQ to Entities - Entity Framework breaks them down into Command Trees then the provider transforms these Command Trees into appropriate sequel queries for the targeted database.

Entity Framework Work-flow

Note* In this article we will only cover the Code-First approach only.

We start with defining the Conceptual Model - the Domain Classes.
Write queries against the models we defined in above step (LINQ to Entities)
Entity Framework breaks the queries(defined in above step) down into Command Trees
Then the EF provider transforms these Command Trees into appropriate sequel queries for the targeted database
EF determines and executes SQL
EF transforms results into objects of Domain classes that we defined in the very first step.

Here are some more details about EF that you might find interesting:

Entity Framework is an Open Source Initiative.
Entity Framework is independent of .NET release cycle
Entity Framework can be used with a variety of databases (many 3party providers available)
Entity Framework can create parametrized queries. Depends on how you have specified your LINQ queries.
Entity Framework is able to track in-memory changes made to our conceptual models. Once you call SaveChanges() method EF will execute the appropriate Insert/Update or Delete queries to reflect the in-memory changes that have been made.
Entity Framework is flexible and provides for granular control, if need be.
Entity Framework provides for better Query Performance via Auto Cached Queries

So, before we begin keep this in mind - " If you do what is easy your life will be hard, if you do what is hard your life will be easy. "

Time to roll...

Code First Driven Approach

Things to remember before we begin are...

We do not have any Visual Designer
We do not have any automatic Code Generation
The Database - May or May NOT Exists. (we will look into this soon enough)
Final thing, we "Love Code" and hate the other two approaches - Database First and Model First

So, what comes in Code First approach:

The Domain Classes
These Domain Classes have nothing to do with Entity Framework. They just represent your Business Domain items.

The Entity Framework
Then we declare the Context Class. This Context Class manages the interaction between our Domain Classes and the Database. Now, it is worth mentioning that Context Class is not specific to Code First approach, and is an Entity Framework feature and is also employed in Model First and Database First approaches for orchestrating Database interaction.

The special thing is that - Code First adds and uses DbContext Class as an Model Builder which inspects the Context Class and the Domain Classes that it is managing along with a set of rules that we define via - Code First Data Annotations or Code First Fluent API or using both mentioned to determine how our Domain Classes and their relationships describe the database model and how it should be exactly mapped to the database.

We also get the option to Initialise the database and/or use Code First Migrations Initialisation will help us in seeding the database with some data to begin with. Migrations will help us to update our Database schema if any changes happen to our Domain Classes Model.

So lets start, things we will do in this article are mentioned below

We will create Code First default model and create a database from it
Manage and understand how Domain Model changes reflect/affect on the database
Manage Configurations - Declaratively with Data Annotations and Imperatively with Fluent API
We will end with reversing an existing database for Code First

The Project that we will be Developing

We will be developing database model for a - Discussion Board.

I am given to understand Discussions are essential part to facilitate an evolving progressive society. So, now would be a good time to have one with the 2016 US elections not so far off.

Let us build the back-end to facilitate discussions as the one pointed above.

Our proposed application "Discussion Board" will need the following tables - next to them I have specified the purpose to have those tables.

Table Name	Purpose to have the Table
Category	The Category that the discussion falls into. Some valid examples are - Technology, Politics, Science, Sports among countless others.
Topic	The Topic that the discussion is about. Some valid examples are - Say under category "Politics" Why is "Elizabeth Warren" not contesting in 2016 elections? Note* a Topic can also be linked to more than 1 Category. Example - Will Mr Gates ever buy a Basketball Team? or will he continiue making wise investements that will benefit humanity.? The above topic may very well qualify under two Categories - 1. Technology and 2. Sports.
Comment	The Comment that is about a topic. Some valid example are - say under the above mentioned Topic: I have no clue why "Elizabeth Warren" is not running for Presidency. She would be a delight to vote for.
Member	The table that will store Member details. Like Member First Name, Last Name, Preferred Name etc
ContactDetail	The table that will store Contact Details of the Member. Like Twitter Alias, Facebook Profile id etc. Don't worry we are all for anonymity and if we were developing a full fledged application we would allow for complete anonymity. These details are here just to flesh up our mock database.
Interest	The table that will store Interests details of the Member. Like this if a member interests includes Science, Sports etc We will record that information in this table.
	Special Note for you to consider... Category and Topic table will share a *Many-to-Many relationship**.

The Project - Quick Look at the Domain Classes

Creating a class library Project which will house the definition of all of our Domain CLasses mentioned above. Note* I have already created a Visual Studio Solution named: "CodeFirst.DiscussionBoard" We will be adding projects to this solution.

The project created

Note* Delete the following class, we do not need it.

Now we will start building the real project. Add the following class files to the project.

We will begin with adding a class name "Category"

Step 1

Step 2

the class added...

Now add the following classes - repeating the above two steps mentioned.

"Topic"
"Comment"
"Member"
"ContactDetail"
"Interest"

The classes have been added...

Now we will add content to the above defined classes. We will add properties to the above classes which will later map to database table columns. So as an example - Category Class will map to an Category Table and any properties in it will map to the columns that the Category Table will have.

I will go ahead and add the required properties to the above classes. There are fairly simple properties and I will add comments in code to explain anything that might come across as a bit twisty.

Screen-Shots after the details(Properties) have been added to the above created classes:

Category Class
Topic Class
Comment Class
Member Class
ContactDetail Class
Interest Class

One thing to note is that our Library Project: "DiscussionBoard.DomainClasses" will have no reference to Entity Framework whatsoever. This will just be plain classes representing our Business Domain.

We will start with adding Enum Type that will be used in Category and Interest Classes. Below is the new Enum and the modified Category and Interest Classes.

Category Class modified property

Interest Class modified property

A final Note - There are Navigation Properties in the above classes - these properties facilitate to manage relationships better in Entity Framework. They also help with something called Lazy Loading (which we will see shortly) virtual keyword* helps us here.

Example of Navigation Property

// Snippet from Comment.cs

// The Topic this Comment is related too
public int TopicId { get; set; }

// Navigation Property to Topic
public virtual Topic Topic { get; set; }

Note* we have TopicId but still we have a virtual property of Type Topic named Topic.

Similar to Comment class other classes also have Navigation Properties. We have this navigation properties for reasons mentioed above.

Now we have our simple domain model ready and so next up.. We will create the DbContext Class.

Creating DbContext Class

So, in the above setion we created our simple POCO classes representing our business domain.

Our Domain classes had no reference to Entity Framework whatsoever.

Of course the next question that follows is how will we engage Entity Framework? We will do this by Introducing a Data Layer that will have reference to Entity Framework. We will also point our Data Layer to the above created business Domain CLasses.

Needless to add - The Data Layer will manage our business domain classes with respect to Entity Framework. This includes - but is not limited to - managing change tracking, fetching data from the database among other things.

Creating a New Project in our Solution:

Note* Delete Class1.cs and add a new class called "DiscussionBoardContext"

Project added -

Now lets add Entity Framework to the Solution and point our newly created project DiscussionBoard.DataLayer to use it.

Steps to add EF to the solution:

Uncheck - the Domain Classes Project. And accept the licence terms & conditions when prompeted.

Now we have the latest stable version of EF (which is version 6 as off writing) in our solution.

The project structure now. We have app.config and package.config files added to our Data Layer project. Also, our Data Layer project now has Entity Framework DLL under its reference.

The app.config SAMPLE file (The one in my Project is diffrent - I have provided that below) It has some default configuration. Note* LocalDbConnectionFactory is part of SQL Server 2012 and is the default developement database that you will use instead of SQL Express and it gets installed with Visual Studio 2012 (and above) editions. If you are running a prior version of VS than it will automatically default to SQL Express. so no worries at all.

However, I am running SQL Server 2014 and for me it defaults to SQL Express. I have not installed LocalDb and so it defaults to SQL Express. My App.config file has the following configuration settings:

The package.config file - Nothing complicated here.

Now it is time to roll in the DbContext.

We will inherit EF's DbContext class in our Data Layer's DiscussionBoardContext class.

DbContext added, now our DiscussionBoardContext class will be able to do all the Entity Framework related tasks because it now inherits from DbContext.

Now we want DiscussionBoardContext to manage our business domain classes. We will achive this by using DbSet Note* I will leave it to you to google what DbSet is? it is fairly straight forward. Here are some pointers regarding DbSet though(this should cover the most important bits):

DbSet: Manages Operations (Set Operations) like: Add, Attach, Remove, Find
DbSet: is a Collection of a Single Entity Type (usually maps to one Business Domain Class)
DbSet: is employed alongside DbContext to query the database
DbSet: is a member of System.Data.Entity

An example:

public DbSet<Student> Students { get; set; }

The DbSet of Students will be employed to manage instances, queries and updates to the database of the Student(s) Object.

You will get a better idea of DbSet once we employ it in our Data Layer Project.

Before we introduce DbSets we need to first add reference to our Domain Classes Project in our Data Layer. (Follow Screenshots below.)

Time to add DbSets of our Business Domain Classes. It is a simple process follow the screenshot below.

One interesting detail that has emerged is that I have not created a DbSet for ContactDetail Domain Class. And this is because I know for a fact that I will not be intercating with ContactDetail on a one on one basis. That is to say I will not be querying for ContactDetail explicitly. Since, ContactDetail share a One-To-One or One-To-Zero relationship with Member I would only like to retrieve them when fetching a Member record. EF Code First will support this because it know of this relationship through our Business Domain Model. We will still be able to fetch ContactDetail of a Member even though we do not have a DbSet for it.

Side Note* we have our complete Domain in just one Context Class. It will work for us becuase the application we have here is straight forward and simple. For complex application you might want to look up at something called Domain Driven Design.

Interacting With the DataBase

Entity Framework relies on Meta-Data describing the Conceptual Model Schema(CMS), the Database Structure Schema(DSS) and the Mapping (between CMS and DSS) to interact with the Database.

In regards to Code-First Entity Framework builds Model Meta-Data on the fly at run-time. It infers this data based on the DbSets the Context is managing along with the relationships and properties defined in the Domain Classes themselves (which the DbSets manage).

We can use a Tool (a power tool) perhaps to create a read-only visual model to give us a better understanding of how Entity Framework has interpreted our Domain Model.

This will help us to build the models that we want and will remove the guess work from model meta-data generated by Code-First on the fly. Since we would already have a sneak-peak of how it will translate our doamin model.

Let us look at this Power Tool next.

Installing the proposed Power Tool (Follow the screenshots below):

Now that we have the tool installed let us generate the Database Model from Context Class.

Right click on DiscussionBoardContext.cs and follow the screenshot below:

wops an Error has occured. looking at the bottom of VS... we see the following

Looking at the Output window we see the following error The error is regarding: System.IO.FileNotFoundException

Now, we get the above error because the current Start-Up Project is: DiscussionBoard.DomainClasses and the DiscussionBoard.DomainClasses project does not have an App.config file.

So, let us set DiscussionBoard.DataLayer Project to be the Start-Up Project and DiscussionBoard.DataLayer project has an App.config file (which has the necessary DB Connection string etc)

DiscussionBoard.DataLayer Project has been set as Start-Up Project and has App.config file

Now lets try again to generate the DB Model...

ahh.. more errors.

I must add that this tool also validates our model and is good to have it verify our model befor ewe build our database - well it makes identifying errors/issues easier and in early stages - so it is much cheaper to fix issues as well.

This time the tool is complaining about the following: Error Type is: System.Reflection.TargetInvocationException

Well to cut long story short the validation failed becuase our Domain Class ContactDetail does not have a Primary Key declared.

Needless, to add Entity Framework has a rule that every entity must have an Key and so the validation error was thrown, since the tool failed to find one for ContactDetail. The convention that EF follows while looking for KEY is that every entity must have a property named Option 1:Id or Option 2: property named entityName suffixed by Id like EntityNameId Valid Example for ContactDetail entity would have been the following...

Option 1 example:

// In ContactDetail Class
public class ContactDetail
{
    // Key Property Option 1
    public int Id { get; set; } 
}

OR Option 2 example:

// In ContactDetail Class
public class ContactDetail
{
    // Key Property Option 2 - entityName suffixed by Id
    public int ContactDetailId { get; set; } 
}

Flashback. - ContactDetail Domain Class:

Since, we have not decalred a KEY in ContactDetail using the above specified conventions (demonstrated via Option 1 and Option 2 examples) We get the validation error.

Now Code First gives us to ways to provide configuration information for when EF cannot implicitly infer things properly using conventions. (as in our case with ContactDetail)

We can fix this using 2 approaches:

Data Annotations
Fluent API

for now we will fix this using Data Annotations.

Employing Data Annotations in ContactDetail Class to declare Primary Key.

All we need to do is Flag a property as KEY using the KEY annotation...

the RED Squiggly Lines (under [Key] annotation) indicate all is not well We will need to add the required namespace to our ContactDetail class

Follow the steps below as demonstrated in screen-shots:

Under Framework.. select System.ComponentModel.DataAnnotations

Once done... lets go back to ContactDetail and add the required Namespace

Namespace added and all is good.

Now lets run the tool again...

damn still more errors.

We are getting close though...

Now this time validation failed beacuse of - one confusing convention of Entity Framework.

The complain now is because of this confusing convention: Well to put it simply: when we have a

One-To-One relationship
One-To-ZeroOrOne relationship

Code First needs to understand which is the Principal End and which is the Dependent End of the relationship. This will help Code First determine which table in the Database will have the Foreign Key pointing to the other table.

So, lets use Data Annotations again to clearly specify this One-to-One or One-to-ZeroOrOne relationship between Member Table and ContactDetail Table.

We can do this by specifying the Foreign Key annotation.

Follow the screenshots below: (Screenshots of ContactDetail Class)

again Foreign Key Annotation lives in System.ComponentModel.DataAnnotations.Schema namespace Lets add the required namespace

ContactDetail Class now

Note* One important thing to note is that just specifying Foreign Key attribute will not do the trick here. We will need to specify Foreign Key to what? and by employing Navigation Property we can do this. Hence, the attribute is pointing to Member Navigation Property (it is not the Property Type but the Property Name as highlighted in the above screen-shot in Yellow).

woha.. finally lets run the Tool again...

and volla we have our (read-only) Model Displayed. Hard work payed off...

Now, that we have a validated model... lets move on to the next step...

Database Initialisation

Code-First doesnt really care about the database until upto runtime... but when it gets to that point it does the following two things:

Step 1 - Locate the Database (the connection String from App.config is put to use here)
Step 2 - Create the Database, if necessary

Here is how this works, the very first time a DbContext class tries to interact with the Database (at application run-time) Code First will perform "DataBase Initialisation "

It starts with looking for a Connection String, once that is figured out (App.Config helps out here) It looks for the Database - by default the database name that Code First looks for will be the fully qualified name of the DbContext Class

In our case this would be: The Namespace followed by the DbContext Class Name - highlighted below

The next step Code First will do is check whether the Database of Datbase Name: "DiscussionBoard.DataLayer.DiscussionBoardContext" exists or not Of course the next logical step that Code First does is - If the Database does not exist than it will create it Needless to add Code First will use the Meta-Data that the Model builder created to determine the Schema of the database. Once the database is in existance Code First will go ahead and do whatever the Context was instructed to do example - Insert a record etc.

Of course, I know you are now curious to see this Default Behaviour in Action.

Well your wait is over - lets roll...

Please Note* In this article we will rely on a Console Application Project for Database Interaction.
I will complie an article on MVC in the future which will use our DiscussionBoard Database
in it. Once, this article is online - it should take me another 45 days or so to get the proposed MVC
article up and running (I only do this in evenings after work-hours, so this takes time. hope you will understand).

To do so, we would need to create a Console Application Project and than reference our Data Layer Project in it...
Follow the steps below...

Now we will reference Entity Framework and DiscussionBoard.DataLayer Project in your newly created Console Application Project.

Referencing DiscussionBoard.DataLayer Project

Reference has been added

Referencing Entity Framework

Press Ok - after selecting Project in - Selected Projects Dialog Box

We have the reference now

We have App.Config (with required Connection String and other information) in DiscussionBoard.ConsoleApplication and we can delete App.Config from DiscussionBoard.DataLayer project since we will be doing all the interaction from our newly created Console Application Project: DiscussionBoard.ConsoleApplication

Select Ok when the SCccARY Dialog Box Promts.

After Deletion of App.Config from DiscussionBoard.DataLayer project

Note* Please also add reference to project DiscussionBoard.DomainClasses in our Console Application.

Now lets add some code to Program.cs class in DiscussionBoard.ConsoleApplication Project. I have added Comments in Code to explain what the code is upto...

One thing to note is at this point in runtime -

// Snippet from Program.cs Class
var members = context.Members.ToList();

Code First relaises that it has to do something it will go and find the Connection String and check whether the database exists or not. So, important thing to note here is that Code First does not jumps to action when DiscussionBoardContext class is initialsied.

Now, needless to add that when we run our Console Application at this point nothing will be returned because, the database does not exist as of yet.

Let run it anyway... Make sure Start-Up Project is set to DiscussionBoard.ConsoleApplication Press F5

All we will get is a Blank Black Screen.

Lets look under the hood and understand what really happened Open up SQL Server Object Explorer

Note* If you do not have SQL Server Object Explorer, than you would need to install SQL Server Data Tools which can be downloaded from this LINK Please select the correct version and install it.

Once SQL Server Object Explorer is located, go under the SQL Express Database

If your SQL Express Database is missing you can add it by doing the following

Select the database and Connect to it

After connecting - locate the Database created by Code First as below

Now, in the next section - I will try to explain the Database that Code First created for us.

Explaining the Database created by Code First

Open up the database - see below We will start with the NAME of the Database - which is: "DiscussionBoard.DataLayer.DiscussionBoardContext" As explained in the above section - The default database name will be the fully qualified name for the Context Class.

Lets look at the tables, there is something interesting there

It is important to mention here that it is the MODEL of our CODE that has dictated the Structure of the Database. Take a closer look at the database SCHEMA

So, our database just looks like the Model that we created in our Data Layer Project (Remember the Model we generated using Power Tools.)

Needless, to add the Classes (in Domain Class Project) are represented here as the Tables and their Properties as Columns.

Relationships were defined using Data Annotaions in Domain Classes Project.

Interesting tables to look at are ContactDetail and TopicsCategory.

Lets look at ContactDetail first

MemberId Column is Primary Key and also a Foreign Key in the Table. This was the result of the Data Anotations that we specified in ContactDetail Class. Have a look at ContactDetail Class for a quick refresher, if you like.

Lets look at TopicsCategory table You might already be wondering that we do NOT have any Class defined by this table name than how does this table came into existance.

Well, we did define a Many-to-Many* relationship between Category and Topic Classes in our Domain Classes Project.

And the way Entity Framework and the Database work that type of relationship out is by having a JOIN Table. in the Database. TopicsCategory is that JOIN Table.

So, in our application whenever we will use the Many-to-Many* relationship specified between Category and Topic our dear all powerful Entity Framework, will translate that into SEQUEL STATEMENTS that will employ this JOIN Table. Making it easier for our database to understand what is it that we are trying to do - example Fetching/Inserting/Updating/Deleting etc.

Lets understand CodeFirst Migrations

Now, that we have our Model and Database what will happen if we decide to change it?

That is to say, if we decide to add a new column to Comment Table.

To do so, we would need to add a new column to COMMENT Class in our Domain Class Project(DiscussionBoard.DomainClasses)

Lets add new column named - NumberOfLikes to Comment Class.

Build the Projects and run Console Application again - we will check what will happen - Remember we have changed our MODEL.

WoopSss... adding a new column has broken our project. Self-Explaining Exception - The highlighted bit explains what is the Problem.

We got this exception because the default behaviour of the Database Initialisation does NOT ALLOW for Model changes.

Time to Fix this issue that has crept up becaue we have updated the Model after the Database was created In REAL WORLD this is a very possible scenario - we will have to update our database schema. it is important that this exception be addressed.

DRUM ROLLS - Walks in Code First Migrations. to solve the above Problem.

We need to tell Code First that instead of using Database Initialisation it should use its Migrations feature.

Entity Frameworks Database Migrations API- takes care of managing and keeping in-line the Database Schema as our Domain Model changes/evolves. Migrations - accomplish managing this by keeping track of changes between the model and database (they do so by managing a hash of database schema.)

What we will start with is First: We will let Code First know that we would like to use MigrationsSecond: We will create our initial database using Migrations (So, feel free to delete you database of this project if it already exists. This can be done from SQL Server Management Studio.Also, Commnet out the NumberOfLikes Property in Comment Class in DiscussionBoard.DomainClasses Project.)

So, let us get to work and apprise Code First of our intentions of using Migrations

Open up Package Manager Console

Make sure you have selected Default Project as "DiscussionBoard.DataLayer"

Time to play with some simple commands In Package Manager console type in "enable-migrations" , we can pass on some parameters to this command but we will stick to the defaults

So, this command enable-migrations will first check if the database exists and than it will go and enable migrations. shown in the screen-shot below...

Running enable-migrations command has made some changes to our DiscussionBoard.DataLayer Project We have a New Folder created called: Migrations and this folder has a class created called: Configuration.cs

The Folder:

Configuration Class Default Code This class is specific to Migrations (inheriting from DbMigrationsConfiguration) also, it is setting up this configuration for our context class: DiscussionBoardContext (highlighted below). We will leave the parameter "AutomaticMigrationsEnabled" as false in Configuration.cs and the importance of Seed Method is that it is executed prior to every Migration run. So, if one wants to push some default data into the database every time Migration takes place they will leverage Seed Method. Notice - some sample code has already been provided in Seed Method to add default initial data.

The Code First Database Migrations Process is basically a 3 step process:

Define OR make Changes to a Model (Define (initial Database Creation)/Change (modifying existing database schema) both employ the code model that we have in DiscussionBoard.DomainClasses project)

Creating a Migration File: This is the file that represents the current state of our Model. This file is employed to identify the changes that are to be made to the database, in order to allign it with our Model.

Applying the above created Migration File to the database. No further explanation required here :P

Now, before moving on please make sure you have deleted (if it still exists) your alredy created Database for the Project and Make sure the Property NumberOfLikes is commented out in Comment.cs class.

So, here we go.

Open Package Manager Console and select DiscussionBoard.DataLayer Project

Type in Command: add-migration InitialMigration20151014

Follow the screen-shots below:

Here, in command add-migration - migration is the class that EF creates to describe the changes that will be needed in the database. The parameter that follows add-migration command is just the name of the migration. I have choosen the name InitialMigration20151014. But you can use more descriptive names etc.

First the above command checked that did I had any migrations before of which there were none. So, it will build up the migration files/classes based on this fact. that this is the very initial migration and even the database doesnt exist yet. So, what EF is migrating from is NOTHING, therefore it needs to create everything from scrath..

Have a look at the classes added by the executing the above command to understand better.

The Files:

Have a look at the Class - InitialMigration20151014.cs under Migrations Folder.

Since, this is the very first migration, class InitialMigration20151014.cs has been populated with create table and other relevant methods. Note* This code will be translated to appropriate SQL Code... Yes Migration API is that well built. :)

So, thus far we have completed Step 1 and Step 2 Step 1 was define the Model which we already did in previous sections Step 2 was Creating a Migration File which we did with the Help of command: add-migration InitialMigration20151014

Now, it is time to execute Step 3 - Apply the Migration File.

The command update-database will update our database model to the latest migration, by DEFAULT. You might have noiced the TimeStamp on migration files - that how Entity Frameworkj identifies which migration is the latest.

Now, thet ar emany parameter that can be used with update-database command but I want you to go through these two commands, as I think they are very important in a developers day-to-day chores.

NOTE* Before continuing please move the App.Config file from DiscussionBoard.ConsoleApplication Project to DiscussionBoard.DataLayer Project for the time being. Since we will be excuting Migration command in Package Manager Console for our DiscussionBoard.DataLayer Project.

update-database command with the -script Parameter This will generate an SQL Script that can be passed to a DBA so that the model changes could be applied to the database by him/her.

Usage: In Package Manager Console type - update-database -script

A Script File named: SqlQuery_1.sql has been generated. Now, this file can be passed on to the DBA for further proceeds.

I will, skip this file as I will be using the second parameter for this project. Running low on resources and I am understaffed so cant afford a DBA for this CP Project :P

**Please make sure you have re-added App.config file to DiscussionBoard.DataLayer Project for this command to work successfully.**
update-database command with the -verbose Parameter This will update the database and will also provide us with a blow-by-blow detail of the update process in the Package Manager Console.

For us it will create the database as well, as we have deleted our database before starting this migrations article section.

Usage: In Package Manager Console type - update-database -verbose

-verbose in action Part 1

-verbose in action Part 2 - Running the Seed Method as we do not have a database.

**Please make sure you have re-added App.config file to DiscussionBoard.DataLayer Project for this command to work successfully.**

and now our database has been created this time with the power of Migrations on our side.

Now, we will uncomment/re-add NumberOfLikes Property in Comment.cs Class (DiscussionBoard.DomainClasses Project)

So, we have a MODEL CHANGE this time around. How do we go by updating the Database to our latest Code Data Model... Next steps will discuss that in detail.

Tapping into the power of Migrations to pass on our domain model changes - the addintion of NumberOfLikes Property to database. This will reflect as - a New Column Named NumberOfLikes will be added to the Comment Table.

Before we start as of yet, NumberOfLikes Column does not exist on Comment Table:

NOTE*: Also, worth mentioning is the fact that the table dbo.MigrationHistory in our database is the table that Entity Framework uses to store a hash in and each time we Add a migration Entity Framework works out the difference between the database schema and the code model basd on that stored hash in dbo.MigrationHistory.

Now lets start the Migrations Process -

Add the New Migration.

Open up Package Manager Console for DiscussionBoard.DataLayer Project and type in the below mentioned command AddedNumberOfLikesPropertyToCommentClass is just a description for the migration.

Command Result:

The migration code class - simple: only the definition of the newly added column exists to update the database schema.

See, the beauty - we can have an already existing database, can have data in that database and yet we can easliy add columns to tables in that database. Pre-existing indexes, triggers etc nothing will get messed up whatsoever. beauty.
Time to Update the database Schema.

Open up Package Manager Console for DiscussionBoard.DataLayer Project and type in the below mentioned command

Command Result:
Note* the highlighted bit is the Hash store in dbo.MigrationHistory table.

and the new column has been added.

In the next section we will see the ways - of how we can interact with our DiscussionBoard DataBase.

Interacting with Database - Inserting records in Discussion Database

Time to interact with our Database.

We will do the interaction using Project: DiscussionBoard.ConsoleApplication

So, follow along, As of now we do not have any Disscussion Data Whatsoever in our Database...

Over the next few sections we will be adding the following methods to our Console Application.

Insert Categories Method Implementation

So, in the code snippet below this is what we are doing:

DiscussionBoardContext Context in Using Block has all the required DbSets defined. The DbSet gives us access to interact with each of the defined type.

In this case we will be working with DbSet of type Catergory. As evident in the following code line: context.Categories.Add(definedCategory);

Further, We would like to Add the Category to the DbSet. Now the Context already knows we want to work with DbSet of Category and the FACT that we have used the ADD method makes it clear to the context that these category items need to be inserted in the Database. Simple. As evident in the following code line: context.Categories.Add(definedCategory);

Finally, we call the SaveChanges() Method of the Context. This will excute the Insert Statements for us. Note* SaveChanges() Method will execute everything that the Context is Tracking. For us it is only tracking these new Categories objects, so it will fire a Insert Query for each of them. The code line: context.SaveChanges();

Before, we run the code, I want to introduce you to new Logging feature that has been introduced in EF6. All we are doing is with our Context Instance we have set its Log Property to the Console.WriteLine Function EF will output the log for us in a console window. Now the console window will display all of the activity that EF did to get the job done. It is a good/nice to have feature, especially while developing. the code line: context.Database.Log = Console.WriteLine;

the code snippet from Program.cs class in Project: DiscussionBoard.ConsoleApplication

Note* before we run please make sure you have uncommneted the call to Method InsertCatergories() in Main Mehtod of Program.cs Class and also commented out Method GetMembers() in the said method and class.

After Running - the Console Project... the result (I Fired a query in Management Studio 2014)

the Log Output: (you are welcome to look around - Check the Connection EF makes, Transaction EF Begins etc)

The Query result:

Before we move ahead - Please add the following code in Project: DiscussionBoard.ConsoleApplication Class Program.cs Method Main. We, are Disabling EF Database Initialization. when we are working with Discussion Board Context Database.SetInitializer(new NullDatabaseInitializer<DiscussionBoardContext>());

Now, EF will only execute commands related to our specified Context.

Note* This is an important aspect and in production Database Initialization should be ideally turned off. Now you will notice that EF only fires queries related to our specified context in the Console Window - where the Logs are being reported.

So, Now if you re-run the above code - you will witness in the Console Window that EF only fired an Insert Command and nothing else unlike previously where there was a barage of interaction. Good for Performnace..

Note* You might want to look at AddRange Method of the Context Class. It takes IEnumerable and makes life easier when it comes to working with lists. No need for Foreach blocks.

Before, we proceed further please make below changes to InsertCatergories() Method We are making these changes, so, that we do not end up saving duplicates into the database. We could have gone for DropCreateDatabase option as our database initialiser scheme but I am leaving that for you to explore as it is fairly simple. Also, I wish to keep this article as straight forward as possible.

the _getInitialCategories() Method.. fairly simple
Insert Topics Method Implementation

Now that we have had a start with implementing InsertCatergories() Method ,
in this section I will focus on reteriving records and Inserting Topics records.
So, Quering Categories Table and Inserting Topics in Topics Table corresponding to a Category.

the InsertTopics() Method :

The code in the above table is self explonatory and comments do the job very well to clarify code.

Let us run the Project and Insert Some Topics.

Make sure to Uncomment InsertTopics() Method before pressing F5.

Debugging the execution...

The method is about to be invoked..

2 Categories records have been found.. as expected..

Saving the Topic record - after linking/attaching it to the 2 Categories fetched.

ohhh... something is not right here... 2 Insert Statements have been fired to Insert Categories.
but we already have those Categories..

Let us look at the database...

So records 2 and 3 have been re-inserted. Enum 2 stands Sports for and 3 stands for Technology.

This is not what we wanted and hence in Method InsertTopics() we linked the Topic to the 2 retrieved Categories.

Well, we tried to save an Entity Graph here and could not clearly specify our intentions regarding the Graph. EF interpretted that Topic and attached Categories as to be ADDED. When we say context.Topics.Add(topic); this is interpreted by EF as Topic record and everything attached to it should be ADDED.

Moreover, the context we used to reterive Categories is different to the one we are using to insert Topic. So, the current context has no knowledge that these Categories were actually pulled from the Database (and EF also discards the fact that the Id column on those Categories is greater than ZERO. yes it does so.).

We need to find a way around this. there are many elegant solutions using Repository Pattern and Unit of Work Pattern by employing an Interface etc but I will leave that to you to figure out. For now, I will use a simple approch to address this issue.

Inserting Graphs.

We can get around this by explicitly managing the Change Tracker.

The code..now in InsertTopics() Method ..

Now, before we can test it - Please follow the Steps below.
or else you will get exceptions - login failed etc.

Delete the Database entirely

COMMENT just for this run - the Code below...

Time... to build and run the project.

Select Run without Debugging

Press down any key - for ReadKey() when the Console-Window Pops-up.
Evidentely you will have to do it for around 5 times or so.. for 5 Category records.

Database looks Good.

Alright so now its Show-Time.

Uncomment below Highlighted Code.

Rebuild and let us debug the code.

You are welcome to peek into Context etc at this stage.

The Console Log looks Good. No records have been inserted into Categories Table.

Querying Database Just to Make sure...

Categories Table:

Topics Table:

TopicCategories Table (the managed and used by Entity Framework.)

and all looks good.
Insert Members Method Implementation

Now, this will be a fairly simple method.

I will provide you with the code snippet and leave it for you to play with it.

Also, I have added some checking code for Topics so the insert bit is not triggered if the record already exists.

The Code...

Some Validation Code that I have added... on the side, to check before the inserts are triggered:

the Main Method Now looks like this...

My Test Run results:

The Log File looks alright. Each Member Insert Statement is followed by a ContactDetail Insert Statement.

Database Queries:

You are welcome to debug and test InsertMembers Method on you own.
Leave any questions or troubles you are having in the comments.
Insert Interests Method Implementation

Like good guys we will start with The Validation Method.

The Main Method looks like this now...

and now the the InsertInterests Method itself.

My Test Run results:

The Log File Looks alright. As expected, Insert Statements are fired for Interest Records only.

Database Queries:

You are welcome to debug and test InsertInterests Method on you own. Leave any questions or troubles you are having in the comments.
Insert ContactDetails Method Implementation

This Method will not be required as we insert ContactDetail records when we insert Member Records. See InsertMembers Method.

Also, a ContactDetail record cannot exists on its own. It shares a Zero-to-one relationship with Member Table.
Insert Comments Method Implementation

Like good guys we will start with The Validation Method.

The Main Method looks like this now...

and now the the InsertComments Method itself.

My Test Run results:

The Log File Looks alright. As expected, Insert Statements are fired for Comments Records only.

Database Queries:

You are welcome to debug and test InsertComments Method on your own. Leave any questions or troubles you are having in the comments

Interacting with Database - Updating and Querying Discussion Database

Updating the database...

We will update the Comment records that we have added... in the previous section.

Lets, update the comments above to something more meaning full.

We will start with looking at some examples of reteriving data from the Database.

Some, examples of READ queries below:

We will be using LINQ Methods to fetch data from the database.

// Code Snippet 

// ToList() is an LINQ Execution Method
// This will return all the Comments in Comment Table
var comments = context.Comments.ToList();

// FirstOrDefault() is an LINQ Execution Method
// This will return only the First Fetched comment from the Comment Table
var comment = context.Comments.FirstOrDefault();

// This will return all the Comments in Comment Table,
// that will MATCH the where condition.
// This query will fetch all the commenst that have been posted by Member with Id 2.
// the '.where' is not an execution method.
// The query is fired when it reached the ToList() LINQ method.
var comments = context.Comments.where(c => c.MemberId == 2).ToList();

// The Find Method
// The Find Method - is used to reterive a record based on the KEY column
// Key is nothing but the Primary Key in the database for that table.
int primaryKeyValue = 4;
// Fetch the record from Comment Table where Id is 4
var comment = context.Comments.Find(primaryKeyValue);

The above queries will query against the database get back the relevant data and will create doamin/entity objects in this case of Comment for all the relevant records that have been fetched.

We will be using these (actually, have used them so far in this project already.) LINQ Methods to fetch Data from database.

Now, getting back to the task at hand. of Updating records - In this case some Comments.

We will RANDOMLY pick a Comment and Update it.

Note* The code is self explonatory and easy to understand.

the Main Method now... looks like this...

the Update Method...

My Test Run results:

The Log File Looks alright. As expected, Update Statement is fired for Comment Record.

Database Queries:

You are welcome to debug and test UpdateRandomComment Method on your own.
Leave any questions or troubles you are having in the comments.

Note* You can Update Data in disconneted state by using the following example.
Explanation is provided in Code Comments.

The UpdateRandomCommentInDisconnectedState() Method -

The Main Method:

My Test Run results:

The Log File Looks alright. As expected, Update Statement is fired for Comment Record.

Database Queries:

You are welcome to debug and test UpdateRandomCommentInDisconnectedState Method on your own.
Leave any questions or troubles you are having in the comments.

Interacting with Database - Deleting Records in Discussion Database

The DeleteRandomComment() Method -

The Main Method:

My Test Run results:

Debugging DeleteRandomComment method -

The Log File Looks alright. As expected, Delete Statement is fired for Comment Record.

Database Queries:

You are welcome to debug and test DeleteRandomComment Method on your own.
Leave any questions or troubles you are having in the comments.

Interacting with Database - Retrieving Graphs of Data Records from Discussion Database

In this section we will look at the following:

We will look at Eager Loading which is implemented using DbSet.Include() Method.

We will look at Explicit Loading which is implemented using Load() Method.

Implementing RetrievingDataGraphs() Method.

the Main Method...

debugging the Method...

Eager Loading in Action - Comments Loaded for reterived Topic Record...

Explicit Loading in Action - Comments Loaded for reterived Topic Record...

No Comments Loaded yet for reterived Topic Record...

With this, we have come to the end of this Article. :)

You have done well. get a chilled pint, you have earned it. :)

Final Thoughts

This is all for now. Hope you enjoyed the journey.

On a side note - I have written a couple of articles regarding WCF web services, please feel free to explore them at your convenience.

Its time for me to sign off. Feel free to ask questions, provide feedback and everything in between as I am on the same boat with you.

PS: The Boat is called "Burn and Learn".

Good Luck. and keep learning :)

History

Version 1 submitted on 15th November 2015.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)