Discover technology with a human touch with one of our database solutions. Read more on one of our techniques:

Entity Framework is an object-relational mapping (ORM) framework provided free and open-source from Microsoft.  It is a way to create classes that can communicate with a SQL database.  There are two general ways of using Entity Framework:  Code First and Database First.  The Code First method involves creating a model that represents a database and creating a database from that model using Entity Framework.  The Database First method uses an existing database and creates a model from it.  This model can then communicate with the database that it represents. 

A blending of these two methods is to create a “Code First” model from an existing database.  The existing database is used to create the model and then any further updates to the database are done in the model.  In this example we will create a database and then generate a model from it.

 Create a new solution in Visual Studio and then add a SQL Server Database Project to it.

   

This database will be a simple database that will represent data from a school.  We are going to create two simple tables with some relationships between them.  The first will be a table of courses that students will be enrolled in.  The second will be a table of students with what courses they have for their first, second, and third periods of the day.  

First, let’s add the table for the courses.  Right click on the database project and click Add then click Table. Name the new item Course.  Once you have done that copy the code below into the T-SQL panel. 

CREATE TABLE [dbo].[Course]
(
       [CourseId] INT IDENTITY(1,1) NOT NULL,
       [Name] NVARCHAR(50) NOT NULL,
       CONSTRAINT [PK_Course] PRIMARY KEY CLUSTERED ([CourseId] ASC)
)

All you need to understand about the code above is that we are creating a table called Course and that table has a CourseId (that is the Primary Key of the table) and a Name. Your solution should look like the image below now. 

Now let’s add the table for the Students.  Do the same step for adding the Course table to the solution and paste the code below in the T-SQL  panel.

CREATE TABLE [dbo].[Student]
(
       [StudentId] INT IDENTITY(1,1) NOT NULL,
       [FirstName] NVARCHAR(50) NOT NULL,
       [LastName] NVARCHAR(50) NOT NULL,
       [FirstPeriod] INT NOT NULL,
       [SecondPeriod] INT NOT NULL,
       [ThirdPeriod] INT NOT NULL,
       CONSTRAINT [PK_Student] PRIMARY KEY CLUSTERED ([StudentId] ASC),
       CONSTRAINT [FK_Student_FirstPeriod] FOREIGN KEY ([FirstPeriod]) REFERENCES [Course]([CourseId]),
       CONSTRAINT [FK_Student_SecondPeriod] FOREIGN KEY ([SecondPeriod]) REFERENCES [Course]([CourseId]),
       CONSTRAINT [FK_Student_ThirdPeriod] FOREIGN KEY ([ThirdPeriod]) REFERENCES [Course]([CourseId]),
)

Here we are creating a table for students called Student.  It has the First and Last Names of the students as well as the CourseIds for their First, Second, and Third Periods of the day.  We are also telling that database that those CourseIds are Foreign Keys bound to the Course table which will force the database to only accept Ids that are in the CourseId column of the Course table. 

The last thing we want to do is create a script to seed the database with some data that we can work with.  Right click on the SQL database project and click Add then click Script.  Select Post Deployment script from the options presented. 

Name the file Seed.PostDeployment.sql.  Once the file has been created copy and paste the following code below the comments on the script.

MERGE INTO Course AS CourseTarget
USING (VALUES
  (N'Math'),
  (N'Science'),
  (N'English'),
  (N'Social Studies'),
  (N'Shop'),
  (N'Home Ec')
)
AS CourseSource (Name)
ON CourseTarget.Name = CourseSource.Name
-- insert new rows
WHEN NOT MATCHED BY TARGET THEN
INSERT (Name)
VALUES (Name);
MERGE INTO Student AS StudentTarget
USING (VALUES
  (N'Susan', N'Storm', 1, 2, 3),
  (N'Matthew', N'Murdoch', 4, 5, 6),
  (N'Peter', N'Parker', 1, 3, 6),
  (N'Jessica', N'Jones', 3, 4, 1)
)
AS StudentSource (FirstName, LastName, FirstPeriod, SecondPeriod, ThirdPeriod)
ON StudentTarget.FirstName = StudentSource.FirstName
-- insert new rows
WHEN NOT MATCHED BY TARGET THEN
INSERT (FirstName, LastName, FirstPeriod, SecondPeriod, ThirdPeriod) 
VALUES (FirstName, LastName, FirstPeriod, SecondPeriod, ThirdPeriod); 

This script will seed data into the tables we have created so we have something that we can test with.  Please note that both the design of the database above as well as the method for inputting the seed data are not best practices. It is simply a quick way for us to get a database we can work with. 

Now that we have our database designed and the seed data ready we can publish the database.  Right click on the databse project and click Publish. You will get the following dialog.

   

Click on the Edit button next to the Target database connection field.  You will get a dialog that will ask where you would like to deploy the database to.  For now we will be publishing to the local database on our machine.  Set the Server name field to (localdb)\ProjectsV12 if you are working in Visual Studio 2015.  Set it to the appropriate version number if you are working in a prior version (V11.0 instead of ProjectsV12 for Visual Studio 2013 etc.).  Set the database name field to School.

 

Click the Test Connection button to insure you connect properly but you should have no problems.  Click OK and then click Save Profile on the on the previous dialog.  Now click the Publish button and the database will publish to the local database on your machine. Now click View on the top bar and then click SQL Server Object Explorer.  You will get a toolbar with a list of database engines to connect to.  Navigate to Sql Server -> (localdb)\ProjectsV12 -> Databses -> School.  You have now accessed the local deployed SQl database.  Now open the Tables folder and you should see both tables: Course and Student.  

Right click both the Course and the Student tables and click View Data.  You should see the seed data in the tables.

 

Now we have a database that we can use Entity Framework with.  The next step is to create a project that we can add a model too.  For simplicities sake, let’s create an API with the MVC framework and create the model in the Model folder.  Right click the solution and click Add then click New Project.  Under Visual C# click the Web option and select ASP.NET Web Application.  Name the Project School.Api and click OK.

 

On the next dialog select the Web API option.  Then click Change Authentication and select No Authentication.  Finally uncheck the Host in the cloud option under the Microsoft Azure section. Click OK and a template will be added to the solution for a Web API.

 

Now that we have created the framework for the API let’s add the model.  Right click on the Model folder and click Add then click New Item. On the next dialog select Data under Visual C# and then select Data.  Select ADO.NET Entity Data Model from the options and name it School.Model then click the Add button. 

On the next dialog select the Code First from Database option then click next.

 

On the following dialog click New Connection.

 

After this you will get the same dialog you got before for creating a database.  Type (localdb)\ProjectsV12 into the Server Name field and then select School from the dropdown menu.

 

Click the OK button then change the name connection settings to SchoolDb.  Then click the Next button.  On the following dialog click the check mark next to Tables and insure the “Pluralize or singularize generated object names” option is selected.

 

Click the Finish button and a model will be generated that allows us to communicate with the database. Let’s take a look at this generated model.

 

We see above that we now have three new classes.  Course and Student are the Model classes that represent the individual tables in the database. School is the Model class that represents the model class.  The code for the School class is below.

namespace School.Api.Models
{
       using System;
       using System.Data.Entity;
       using System.ComponentModel.DataAnnotations.Schema;
       using System.Linq; 

       public partial class School : DbContext
       {
              public School()
                     : base("name=SchoolDb")
              {
              } 

              public virtual DbSet Courses { get; set; }
              public virtual DbSet Students { get; set; } 

              protected override void OnModelCreating(DbModelBuilder modelBuilder)
              {
                     modelBuilder.Entity()
                           .HasMany(e => e.Students)
                           .WithRequired(e => e.Course)
                           .HasForeignKey(e => e.FirstPeriod)
                           .WillCascadeOnDelete(false); 

                     modelBuilder.Entity()
                           .HasMany(e => e.Students1)
                           .WithRequired(e => e.Course1)
                           .HasForeignKey(e => e.SecondPeriod)
                           .WillCascadeOnDelete(false); 

                     modelBuilder.Entity()
                           .HasMany(e => e.Students2)
                           .WithRequired(e => e.Course2)
                           .HasForeignKey(e => e.ThirdPeriod)
                           .WillCascadeOnDelete(false);
              }
       }
}

We see two DbSet classes for Courses and Students.  These are the classes that we interact with to push and pull data from those individual tables. We then have an OnModelCreating method that sets the parameters of the API using Entity Frameworks fluent API system. We will revisit this method shortly. 

Below we see the code for the Student class.

namespace School.Api.Models
{
    using System;
    using System.Collections.Generic;
    using System.ComponentModel.DataAnnotations;
    using System.ComponentModel.DataAnnotations.Schema;
    using System.Data.Entity.Spatial; 

    [Table("Student")]
    public partial class Student
    {
        public int StudentId { get; set; } 

        [Required]
        [StringLength(50)]
        public string FirstName { get; set; } 

        [Required]
        [StringLength(50)]
        public string LastName { get; set; }

        public int FirstPeriod { get; set; }
        public int SecondPeriod { get; set; }
        public int ThirdPeriod { get; set; } 

        public virtual Course Course { get; set; } 

        public virtual Course Course1 { get; set; } 

        public virtual Course Course2 { get; set; }
    }
}

We see some straightforward properties here that are 1 to 1 relationships between the table columns and the property names.  But we also see Course objects named Course, Course1, and Course2.  Entity Framework does not know what to name these courses but using some logic we can recognize that these are the populated Course objects from the database.  Knowing this we can rename these to something that makes sense for our project.  Copy the code below and replace the Course objects with it. 

public virtual Course FirstPeriodCourse { get; set; }

public virtual Course SecondPeriodCourse { get; set; }

public virtual Course ThirdPeriodCourse { get; set; }

Now let’s look at the Course class.

namespace School.Api.Models
{
    using System;
    using System.Collections.Generic;
    using System.ComponentModel.DataAnnotations;
    using System.ComponentModel.DataAnnotations.Schema;
    using System.Data.Entity.Spatial;

    [Table("Course")]
    public partial class Course
    {
        [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage", "CA2214:DoNotCallOverridableMethodsInConstructors")]
        public Course()
        {
            Students = new HashSet();
            Students1 = new HashSet();
            Students2 = new HashSet();
        }

        public int CourseId { get; set; }

        [Required]
        [StringLength(50)]
        public string Name { get; set; }

        [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage", "CA2227:CollectionPropertiesShouldBeReadOnly")]
        public virtual ICollection Students { get; set; }

        [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage", "CA2227:CollectionPropertiesShouldBeReadOnly")]
        public virtual ICollection Students1 { get; set; }

        [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage", "CA2227:CollectionPropertiesShouldBeReadOnly")]
        public virtual ICollection Students2 { get; set; }
    }
}

Again we see some 1 to 1 relationships between the the CourseId and Name columns in the database.  We also see 3 sets of collections of Student objects.  What are these collections?  These are the Students that have that class in their First Period, Second Period, and Third Period respectively.  Let’s give these collections some names that will make sense for us.

namespace School.Api.Models
{
    using System;
    using System.Collections.Generic;
    using System.ComponentModel.DataAnnotations;
    using System.ComponentModel.DataAnnotations.Schema;
    using System.Data.Entity.Spatial; 

    [Table("Course")]
    public partial class Course
    {
        [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage", "CA2214:DoNotCallOverridableMethodsInConstructors")]
        public Course()
        {
		FirstPeriodStudents = new HashSet();
		SecondPeriodStudents = new HashSet();
		ThirdPeriodStudents = new HashSet();

        } 

        public int CourseId { get; set; } 

        [Required]
        [StringLength(50)]
        public string Name { get; set; } 

        [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage", "CA2227:CollectionPropertiesShouldBeReadOnly")]
        public virtual ICollection FirstPeriodStudents { get; set; }

        [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage", "CA2227:CollectionPropertiesShouldBeReadOnly")]
        public virtual ICollection SecondPeriodStudents { get; set; }

        [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage", "CA2227:CollectionPropertiesShouldBeReadOnly")]
        public virtual ICollection ThirdPeriodStudents { get; set; }
    }
}

Note that we need to update the names in the Course() constructor method as well.  Now, remember the School class that we looked at before?  Let’s update that with the new names that we have.  We update the OnModelCreating method with the following code.

protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
	modelBuilder.Entity()
		.HasMany(e => e.FirstPeriodStudents)
		.WithRequired(e => e.FirstPeriodCourse)
		.HasForeignKey(e => e.FirstPeriod)
		.WillCascadeOnDelete(false);

	modelBuilder.Entity()
		.HasMany(e => e.SecondPeriodStudents)
		.WithRequired(e => e.SecondPeriodCourse)
		.HasForeignKey(e => e.SecondPeriod)
		.WillCascadeOnDelete(false); 

	modelBuilder.Entity()
		.HasMany(e => e.ThirdPeriodStudents)
		.WithRequired(e => e.ThirdPeriodCourse)
		.HasForeignKey(e => e.ThirdPeriod)
		.WillCascadeOnDelete(false);

}

As a final step right click on the School class and click rename.  Rename the file to SchoolContext and when the dialog appears asking if you would like to rename the references for School to SchoolContext click yes.  This follows along with the naming pattern used in Entity Framework as well as allows us to avoid naming conflicts between the School namespace and what was before the School class. 

We now have a fully functional model to work with.  Seeing this in action is fairly simple.  As a preliminary step right click on School.Api and click Rebuild solution.  Then right click on the Controllers folder and click Add then click Controller.  On the dialog that pops up click Web API 2 Controller with Actions, using Entity Framework.

On the following dialog click the “+” button next to the Data context class field.  Now in the Model class field start typing course and again hit enter when the type ahead fills in the information.  Finally check the check mark next to “Use async controller actions”.  Click the Add button and a controller will be added called CoursesController.

We now have a system capable of returning data from the database.  We can now run the project and make some direct calls to the API to see the data we get back.  Right click the School.Api Project and click the Set as startup project option.  On the top bar run the application using whatever browser you like. Put a breakpoint on the GetCourse method in the CoursesController class and then search the following URL in your browser. 

http://localhost:51973/api/Courses/1 

Note your localhost port will probably be different so use whatever appeared in the URL when you ran the project.  You should now be able to step through the method and see the data coming back from the database.  Congratulations, you now have a working front and back end for a web API!  However we do have one last problem.  Did you notice it? 

We have a self-referencing loop in the model.  Our Courses have Students which in turn have Courses which have Students and so on.  We cannot serialize this data because technically this data never ends.  You will problem notice an error page is returned in XML to the browser.  This happens because the XML serializer does not know what to do with this infinite recursion.  

The appropriate solution that works in all cases is to take your return data and cast it into and intermediate DTO object that only goes as many levels deep as you want it to.  Another way to handle this is to tell the serializer to ignore any references back to the original object  Stop the project and go to WebApiConfig under the App_Start folder. Add the following code to the bottom of the Register method.

config.Formatters.JsonFormatter
	.SerializerSettings
	.ReferenceLoopHandling = Newtonsoft.Json.ReferenceLoopHandling.Ignore;

What this does is tell the JSON serializer to ignore any self-references when it serializes an object.  However since the default request type is application/xml for web requests and we only updated the JSON serializer we need a way to get JSON instead.  I’m going to use a very nice Chrome Extension called Postman which you can get at the link below. 

https://www.getpostman.com/

 This is a very nice tool for generating API requests that can significantly speed up your workflow when testing your API.  Once Postman is installed run the API solution again from Visual Studio. Type the same URL you used before into Postman and insure under the Headers tab you have Content-Type set to application/json.  Insure that the method you are sending is a Get method in the dropdown next to the URL and click send.

  

You should get some JSON in the body of the return which is what you see in the image above.  You can see if we expand FirstPeriodStudents and ThirdPeriodStudents that we can see the students in those periods and their courses for their other periods. 

Notice anything special here?  In FirstperiodStudents the FirstPeriodCourse is not populated.  Why is this? Because the FirstPeriodCourse item in this case is already the top level item in the tree.  This is what we protected ourselves from when we changed the configuration for the JSON serializer.

You now have a basic idea of what it takes to create a code first model off of an existing database.  Hopefully with this example you have seen some of the tricks and pitfalls associated with this method.  We saw how to rename our objects to something that made more sense logically.  We also saw how to protect ourselves from infinite recursion in our data.