Source Code

Entity Framework 5 Code First Relationships

A Simple Table

Professor Relvar lays the framework for learning the Framework.

First, Edward [said the Professor], we’re going to write some code that will make it easier to learn about the Framework. And we’ll always strive to be a simple as possible--but not simpler.* You’re familiar with Visual Studio, I assume? Good. We’ll create a Console Application and name it something whimsical. How about “GnomeMobile”? No? Well, then, we’ll name it “LearningEF.” Next, we’ll add the Entity Framework 5 NuGet package. We’ll use SQL Express, so that we can easily run queries using Enterprise Manager. Are you following? Good. Normally, I like a file per class, but today I feel abnormal, so we’ll put the entire schema in one file. Here is the class for our first table.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace LearningEF
{
    public class Person
    {
        public int PersonId { get; set; }
        public string Name { get; set; }
    }
}

And now, in another file, code to create the database itself. Notice the using statements.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data.Entity;

namespace LearningEF
{
    public class LearningEFDb: DbContext
    {
        public DbSet Persons { get; set; }
    }
}

[“But Professor,” protested Edward, “Shouldn’t that be ‘People’?”]

I am illustrating a point, young man. Youth and impatience go together like peanut butter and bananas, I suppose. Before answering your question, in our Program class, we write code to instantiate and return items from our database. Using ToList() forces eager loading of the data, which in turn forces the Framework to create the database.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace LearningEF
{
    class Program
    {
        static LearningEFDb _db = new LearningEFDb();

        static void Main(string[] args)
        {
            var list = _db.Persons.ToList();
        }
    }
}

Let’s run our program and see what’s created.

Ah. So. We get a database with the rather excessive name LearningEF.LearningEFDb, with a table named People. Apparently the Framework thinks it knows more about grammar than I**. This is a byproduct of convention over configuration. We, um, borrowed this idea from the DHH Library, and heaven knows where they got it from. Regardless, the Framework doesn’t care how I named my DbSet. I could have named it Kumquats and the database table would be named People. But I don’t like People. So, I’ll inform the Framework of my preference. I’ll also enforce my own database name, which means a trip to the app.config file. Enough talk. Here, look:

app.config

<?xml version="1.0" encoding="utf-8"?>
<configuration>
  <configSections>
    <!-- For more information on Entity Framework configuration, visit http://go.microsoft.com/fwlink/?LinkID=237468 -->
    <section name="entityFramework" type="System.Data.Entity.Internal.ConfigFile.EntityFrameworkSection, EntityFramework, Version=5.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" requirePermission="false" />
  </configSections>
  <connectionStrings>
    <add name="LearningEFDb" 
         connectionString="Server=.\SQLEXPRESS;Database=EFDb;Trusted_Connection=True"
         providerName="System.Data.SqlClient"/>
  </connectionStrings>

LearningEFDb.cs

public class LearningEFDb: DbContext
{
    public DbSet<Person> Persons { get; set; }

    public LearningEFDb(): base("LearningEFDb")
    {

    }

LearningEFSchema.cs

using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;

namespace LearningEF
{
    [Table("Persons")]
    public class Person
    {
        public int PersonId { get; set; }
        public string Name { get; set; }
    }
}

Now, we drop the database and rerun the program.

That’s better, isn’t it? The database has a rational name, and the table has a name whose rationality cannot be denied. We’ll do one more thing. Well, no, we’ll do many more things, of course. What I mean to say is, I don’t want to manually drop the database each time, and I already know I’ll need help saving and displaying information, so, in the interests of time....

[The Professor’s hands fluttered over the keyboard, and in a minute the following code appeared.]

LearningEFDb.cs

public class LearningEFDb: DbContext
{
    public DbSet<Person> Persons { get; set; }

    public LearningEFDb(): base("LearningEFDb")
    {
        KillConnections();
        Database.SetInitializer(new DatabaseInitializer());
        //run with force:true so recreated immediately instead of upon first query.
        Database.Initialize(force: true); 
    }

    public void KillConnections()
    {
        string kill = @"declare @kill varchar(8000) = '';
        select @kill=@kill+'kill '+convert(varchar(5),spid)+';'
        from master..sysprocesses 
        where dbid=db_id('" + Database.Connection.Database + @"');
        exec (@kill);";
        string connectionString = Database.Connection.ConnectionString;
        using (var cn = Database
            .DefaultConnectionFactory
            .CreateConnection(connectionString))
        {
            cn.Open();
            cn.ChangeDatabase("master");
            var cmd = cn.CreateCommand();
            cmd.CommandText = kill;
            cmd.ExecuteNonQuery();
            cn.Close();
        }
    }

    public class DatabaseInitializer : DropCreateDatabaseAlways
    {

    }

LearningEFSchema.cs

[Table("Persons")]
public class Person
{
    //Primary Key
    public int PersonId { get; set; }
    [Required]
    public string Name { get; set; }
}

Program.cs

static LearningEFDb _db = new LearningEFDb();

static void Main(string[] args)
{
    Console.WriteLine("Create Person without a name.");
    Person edward = new Person();
    _db.Persons.Add(edward);
    SaveChanges();
    Console.WriteLine("Add the required name and save.");
    edward.Name = "Edward Farley";
    SaveChanges();
    Console.WriteLine(“Get the record we just saved, and display.”);
    edward = _db.Persons.First();
    Console.WriteLine("Person: " + edward.Name);
    
    Console.WriteLine("Finished");
    Console.ReadLine();
}

static void SaveChanges()
{
    Console.WriteLine("Saving changes.");
    try { _db.SaveChanges(); Console.WriteLine("Successful save."); }
    catch (Exception ex)
    {
        string exceptionError = ex.GetBaseException().Message;
        if (exceptionError.ToLower().Contains("Validation failed for one or more entities.".ToLower()))
        {
            exceptionError = "";
        }
        WriteValidationErrors(exceptionError);
    }
}

static void WriteValidationErrors(string exceptionError)
{
    if (!String.IsNullOrWhiteSpace(exceptionError))
    {
        Console.WriteLine("  ERROR: " + exceptionError);
    }
    var errors = _db.GetValidationErrors();
    foreach (var entry in errors)
    {
        foreach (var error in entry.ValidationErrors)
        {
            Console.WriteLine("  ERROR: " + error.ErrorMessage);
        }
    }
}

Young man, you are allowed to say “Wow” on occasion, though there’s nothing extraordinary here. I’ve simply provided a way to drop and recreate the database each time we run the program. In addition, I have a method to save Framework changes and display validation errors. For good measure, I made it required that a person has a name--everyone does here, you know. I’ve updated the program to create a person and show off our work. When we run the program, the Persons table is slightly different. Names cannot be null. Well, I suppose someone’s name could, technically, be “Null,” but I’ve never heard of it. I think I’ll dispense with the pictures, and provide equivalent SQL scripts.

T-SQL

create table Persons (
  PersonId int not null identity primary key,
  Name nvarchar(max) not null
)

Here is the program output.

Create Person without a name.
Saving changes.
  ERROR: The Name field is required.
Add the required name and save.
Saving changes.
Successful save.
Get the record we just saved, and display.
Person: Edward Farley
Finished

Well, I think that’s quite enough for one day, don’t you? Come back tomorrow. Oh, and one last thing! Ask your dear mother if she’d make me one of her famous peanut butter and banana sandwiches. Off you go!

*Quote attributed to Albert Einstein: ”Everything should be made as simple as possible, but not simpler.”
**The Professor seems to be joking, or he likes old usages of words. Or, it’s possible--but unlikely--he expects to know the number of people in the Persons table. Regardless, EF’s pluralization to “People” is standard. People vs. Persons
Convention over configuration
Kumquat
The Gnome-Mobile
DHH