Programmatic Ponderings

Build a Data Access Layer (DAL) in Visual Studio 2012 using the recently-released Entity Framework 5 with Code First Development. Leverage Entity Framework 5’s eagerly anticipated enum support, as well as Code First Migrations and Lazy Loading functionality. An updated version of this project’s source code, using EF6 is now available on GitHub. The GitHub repository contains all three Entity Framework blog posts.

Introduction

In August of this year (2012), along with the release of Visual Studio 2012, Microsoft announced the release of Entity Framework 5 (EF5). According to Microsoft, “Entity Framework (EF) is an object-relational mapper that enables .NET developers to work with relational data using domain-specific objects. It eliminates the need for most of the data-access code that developers usually need to write.”

EF5 offers multiple options for development, including Model First, Database First, and Code First. Code First, first introduced in EF4.1, allows you to define your model using C# or VB.Net POCO classes. Additional configuration can optionally be performed using attributes on your classes and properties or by using a fluent API. Code First will then create a database if it doesn’t exist, or Code First will use an existing empty database, adding new tables, according to Microsoft.

Exploring Code First

I started by looking at EF5’s Code First development to create a new database. I chose to create a Data Access Layer (DAL) for a health tracker application. This was loosely based on a mobile application I developed some time ago. You can track food intake, physical activities, and how much water you drink per day.

To further explore EF5’s new enum support feature, I substituted two of the database tables, containing static ‘system’ data, for enumeration classes. Added in EF5, Enumeration (enum) support, was the number one user-requested EF feature. Also added to EF5 were spatial data types and my personal favorite, table-valued functions (TVF).

Once the new database was created, I tested another newer EF feature, Code First Migrations. Code First Migrations, introduced in EF4.3, is a feature that allows a database created by Code First to be incrementally changed as your Code First model evolves.

Creating the HealthTracker DAL

Final View of Both Projects in Solution

Here are the steps I followed to create my EF5 Entity Framework DAL project:

1. Create a new C# Class Library, ‘HealthTracker.DataAccess’, in a Solution, ‘HealthTracker’. Target the .NET Framework 4.5.

New Class Library

2. Create (4) POCO (Plain Old CLR Object) classes: Person, Activity, Meal, and Hydration, shown below. Note the virtual properties. According to Microsoft, this enables the Lazy Loading feature of Entity Framework. Lazy Loading means that the contents of these properties will be automatically loaded from the database when you try to access them.

using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using System.Linq;

namespace HealthTracker.DataAccess.Classes
{
    public class Person
    {
        public int PersonId { get; set; }
        public string Name { get; set; }
        public virtual List<Meal> Meals { get; set; }
        public virtual List<Activity> Activities { get; set; }
        public virtual List<Hydration> Hydrations { get; set; }
    }
}

using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using System.Linq;

namespace HealthTracker.DataAccess.Classes
{
    public class Activity
    {
        public int ActivityId { get; set; }
        public DateTime Date { get; set; }
        public ActivityType Type { get; set; }
        public string Notes { get; set; }
        public int PersonId { get; set; }
        public virtual Person Person { get; set; }
    }
}

using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using System.Linq;

namespace HealthTracker.DataAccess.Classes
{
    public class Hydration
    {
        public int HydrationId { get; set; }
        public DateTime Date { get; set; }
        public int Count { get; set; }
        public int PersonId { get; set; }
        public virtual Person Person { get; set; }
    }
}

using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using System.Linq;

namespace HealthTracker.DataAccess.Classes
{
    public class Meal
    {
        public int MealId { get; set; }
        public DateTime Date { get; set; }
        public MealType Type { get; set; }
        public string Description { get; set; }
        public int PersonId { get; set; }
        public virtual Person Person { get; set; }
    }
}

3. Add data annotations to the POCO classes. Annotations are used to specify validation for fields in the data model. Data annotations can include required, min, max, string length, and so forth. Annotations are part of the System.ComponentModel.DataAnnotations namespace. As an alternative to annotations, you can use the Code First Fluent API.

using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using System.Linq;

namespace HealthTracker.DataAccess.Classes
{
    public class Person
    {
        public int PersonId { get; set; }

        [Required]
        [StringLength(100,
        ErrorMessage = "Name must be less than 100 characters."),
        MinLength(2,
        ErrorMessage = "Name must be less than 2 characters.")]
        public string Name { get; set; }

        public virtual List<Meal> Meals { get; set; }
        public virtual List<Activity> Activities { get; set; }
        public virtual List<Hydration> Hydrations { get; set; }
    }
}

using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using System.Linq;

namespace HealthTracker.DataAccess.Classes
{
    public class Activity
    {
        public int ActivityId { get; set; }

        [Required]
        public DateTime Date { get; set; }

        [Required]
        [EnumDataType(typeof(ActivityType))]
        public ActivityType Type { get; set; }

        [StringLength(100,
        ErrorMessage = "Note must be less than 100 characters.")]
        public string Notes { get; set; }

        [Required]
        public int PersonId { get; set; }
        public virtual Person Person { get; set; }
    }
}

using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using System.Linq;

namespace HealthTracker.DataAccess.Classes
{
    public class Hydration
    {
        public int HydrationId { get; set; }

        [Required]
        public DateTime Date { get; set; }

        [Required]
        [Range(0, 20,
        ErrorMessage = "Hydration amount must be between 0 - 20.")]
        public int Count { get; set; }

        [Required]
        public int PersonId { get; set; }
        public virtual Person Person { get; set; }
    }
}

using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using System.Linq;

namespace HealthTracker.DataAccess.Classes
{
    public class Meal
    {
        public int MealId { get; set; }

        [Required]
        public DateTime Date { get; set; }

        [Required]
        [EnumDataType(typeof(MealType))]
        public MealType Type { get; set; }

        [StringLength(100,
        ErrorMessage = "Description must be less than 100 characters.")]
        public string Description { get; set; }

        [Required]
        public int PersonId { get; set; }
        public virtual Person Person { get; set; }
    }
}

4. Create (2) enum classes: MealType and ActivityType, shown below.

using System;
using System.Collections.Generic;
using System.Linq;

namespace HealthTracker.DataAccess.Classes
{
    public enum ActivityType
    {
        Treadmill,
        Jogging,
        WeightTraining,
        Biking,
        Aerobics,
        Other
    }
}

using System;
using System.Collections.Generic;
using System.Linq;

namespace HealthTracker.DataAccess.Classes
{
    public enum MealType
    {
        Breakfast,
        MidMorning,
        Lunch,
        MidAfternoon,
        Dinner,
        Snack,
        Brunch,
        Other
    }
}

5. Add Entity Framework (System.Data.Entity namespace classes) to the Solution by right-clicking on the Solution and selecting ‘Manage NuGet Packages for Solution…’ Install the ‘EntityFramework’ package. If you haven’t discovered the power of NuGet with Visual Studio, check out their site.

Manage NuGet Packages – Add Entity Framework

6. Add a DbContext class, ‘HealthTrackerContext’, shown below. According to Microsoft, “DbContext represents a combination of the Unit-Of-Work and Repository patterns and enables you to query a database and group together changes that will then be written back to the store as a unit.”

using System;
using System.Collections.Generic;
using System.Linq;
using System.Data.Entity;
using HealthTracker.DataAccess.Classes;

namespace HealthTracker.DataAccess
{
    public class HealthTrackerContext : DbContext
    {
        public DbSet<Activity> Activities { get; set; }
        public DbSet<Hydration> Hydrations { get; set; }
        public DbSet<Meal> Meals { get; set; }
        public DbSet<Person> Persons { get; set; }
    }
}

7. Add ‘Code First Migrations’ by running the Enable-Migrations command in the NuGet Package Manager Console. This allowed me to make changes to POCO classes, while keeping the database schema in sync. This only needs to be run once.

Enable-Migrations -ProjectName HealthTracker.DataAccess -Force -EnableAutomaticMigrations -Verbose

Package Manager Console – Code First Migrations

This creates the following class in a new Migrations folder, called Configuration.cs

namespace HealthTracker.DataAccess.Migrations
{
    using System;
    using System.Data.Entity;
    using System.Data.Entity.Migrations;
    using System.Linq;

    internal sealed class Configuration : DbMigrationsConfiguration<HealthTracker.DataAccess.HealthTrackerContext>
    {
        public Configuration()
        {
            AutomaticMigrationsEnabled = true;
        }

        protected override void Seed(HealthTracker.DataAccess.HealthTrackerContext context)
        {
            //  This method will be called after migrating to the latest version.

            //  You can use the DbSet<T>.AddOrUpdate() helper extension method
            //  to avoid creating duplicate seed data. E.g.
            //
            //    context.People.AddOrUpdate(
            //      p => p.FullName,
            //      new Person { FullName = "Andrew Peters" },
            //      new Person { FullName = "Brice Lambson" },
            //      new Person { FullName = "Rowan Miller" }
            //    );
            //
        }
    }
}

Running the Update-Database command in Package Manager Console updates the database when you have made changes.

Update-Database -ProjectName HealthTracker.DataAccess -Verbose -Force

I found the Code First Migrations feature a bit confusing at first. It took several tries to work out the correct command to use to add Code First Migrations to my data access project, and the command to call later, to update the database based on my project.

Dependency Graph

Here is the way the class relationships should look using Visual Studio 2012’s powerful new Dependency Graph feature:

Visual Studio 2012 Dependency Graph of HealthTracker

Testing the Project

To test the entities, I created a simple console application, which references the above EF5 class library. The application takes a person’s name as input. It then adds and/or updates Activities, Meals, and Hydrations, for that Person, depending on whether or not that Person already exists in the database.

1. Create a new C# Console Application, ‘HealthTracker.Console’.

New Console Application

2. Add a reference to the ‘HealthTracker.DataAccess’ project.

Add Reference to HealthTracker.DataAccess Project

3. Create the Examples class and add the code below to the Main method class. As seen in the Examples class, using Micorsoft’s LINQ to Entities with the new .NET Framework 4.5, makes querying the entities very easy.

using System;

namespace HealthTracker.ConsoleApp
{
    class Program
    {
        private static string _newName = String.Empty;

        static void Main()
        {
            GetNameInput();
            var personId = Examples.FindPerson(_newName);

            if (personId == 0)
            {
                Examples.CreatePerson(_newName);
                personId = Examples.FindPerson(_newName);
            }

            Examples.CreateActivity(personId);
            Examples.CreateMeals(personId);
            Examples.UpdateOrAddHydration(personId);

            Console.WriteLine("Click any key to quit.");
            Console.ReadKey();
        }

        private static void GetNameInput()
        {
            Console.Write("Input Person's Name: ");
            var readLine = Console.ReadLine();
            if (readLine != null) _newName = readLine.Trim();
            if (_newName.Length > 2) return;
            Console.WriteLine("Name to short. Exiting program.");
            GetNameInput();
        }
    }
}

using System;
using System.Globalization;
using System.Linq;
using HealthTracker.DataAccess;
using HealthTracker.DataAccess.Classes;

namespace HealthTracker.ConsoleApp
{
    public class Examples
    {
        private static readonly DateTime Today = DateTime.Now.Date;

        /// <summary>
        /// Example of finding a Person in database.
        /// </summary>
        /// <param name="name">Name of the Person</param>
        /// <returns>Person's unique PersonId</returns>
        public static int FindPerson(string name)
        {
            using (var db = new HealthTrackerContext())
            {
                var personId = db.Persons.Where(person => person.Name == name)
                    .Select(person => person.PersonId).FirstOrDefault();

                if (personId == 0)
                    Console.WriteLine("Person, {0}, could not be found...", name);
                else
                    Console.WriteLine("PersonId {0} retrieved...", personId);

                return personId;
            }
        }

        /// <summary>
        /// Example of Adding a new Person
        /// </summary>
        /// <param name="name">Name of the Person</param>
        public static void CreatePerson(string name)
        {
            using (var db = new HealthTrackerContext())
            {
                // Add a new Person
                db.Persons.Add(new Person { Name = name });
                db.SaveChanges();
                Console.WriteLine("New Person, {0}, added...", name);
            }
        }

        /// <summary>
        /// Example of updating existing Hydration count.
        /// Else adding new Hydration if it doesn't exist.
        /// </summary>
        /// <param name="personId">Person's unique PersonId</param>
        public static void UpdateOrAddHydration(int personId)
        {
            using (var db = new HealthTrackerContext())
            {
                var existingHydration = db.Hydrations.FirstOrDefault(
                    hydration => hydration.PersonId == personId
                        && hydration.Date == Today);

                if (existingHydration != null && existingHydration.HydrationId > 0)
                {
                    existingHydration.Count++;
                    db.SaveChanges();
                    Console.WriteLine("Existing Hydration count increased to {0}...",
                        existingHydration.Count.ToString(CultureInfo.InvariantCulture));
                    return;
                }

                db.Hydrations.Add(new Hydration
                {
                    PersonId = personId,
                    Date = Today,
                    Count = 1
                });
                db.SaveChanges();
                Console.WriteLine("New Hydration added...");
            }
        }

        /// <summary>
        /// Example of adding two Meals.
        /// </summary>
        /// <param name="personId">Person's unique PersonId</param>
        public static void CreateMeals(int personId)
        {
            using (var db = new HealthTrackerContext())
            {
                db.Meals.Add(new Meal
                {
                    PersonId = personId,
                    Date = Today,
                    Type = MealType.Breakfast,
                    Description = "(2) slices toast, (1) glass orange juice"
                });

                db.Meals.Add(new Meal
                {
                    PersonId = personId,
                    Date = Today,
                    Type = MealType.Lunch,
                    Description = "(1) protein shake, (1) apple"
                });

                db.SaveChanges();
                Console.WriteLine("Two new Meals added...");
            }
        }

        /// <summary>
        /// Example of adding an Activity
        /// </summary>
        /// <param name="personId">Person's unique PersonId</param>
        public static void CreateActivity(int personId)
        {
            using (var db = new HealthTrackerContext())
            {
                db.Activities.Add(new Activity
                {
                    PersonId = personId,
                    Date = Today,
                    Type = ActivityType.Treadmill,
                    Notes = "30 minutes, 500 calories"
                });

                db.SaveChanges();
                Console.WriteLine("New Activity added...");
            }
        }
    }
}

The console output below demonstrates the addition a new Person, Susan Jones, with an associated Activity, two Meals, and a Hydration.

Console Output – Adding New Person

The console output below demonstrates inputting Susan Jones a second time. Observe what happened to the output.

Console Output – Updating Existing Person

Below is a view of the newly created SQL Express ‘HealthTracker.DataAccess.HealthTrackerContext’ database. Note the four database tables, which correspond to the four POCO classes in the project. Why did the database show up in SQL Express or LocalDB? Read the ‘Where’s My Data?‘ section of this tutorial by Microsoft. Code First uses SQL Express or LocalDB by default, but you add a connection to your project to target SQL Server.

View of HealthTracker SQL Express Database

You can switch your connection SQL Server at anytime, even after the SQL Express or LocalDB database has been created, and updated using Code First Migrations. That’s what I chose to do after completing this post’s example. I prefer doing my initial development with a temporary SQL Express or LocalDB database while the entities and database schema are still evolving. Once I have the model a fairly stable point, I create the SQL Server database, and continue from there evolving the schema.

Conclusion

Having worked with earlier editions of Entity Framework’s Database First and Model First development, in addition to other ORM packages, I was impressed with EF5’s new features as well as the Code First development methodology. Overall, I feel EF5 is another big step toward making Entity Framework a top-notch ORM, on par with other established products on the market.