Entity Framework Relationships with Code First Migrations

Entity Framework 6 has features out of the box to make your life easy. In this article, I would like to take a look at code first migrations in entity framework while focusing on relationships. There is a lot of modeling in this, so get ready to embrace the one common foundation in all line-of-business applications.

To sum up, a data model is where you’d find data. In most applications, the data model gets hooked to a database. The data model is often referred to as the domain model. The domain model is necessary because it enables you to think of the problem in a way that relates to the real world. The real world problem is what gets called the domain. Oftentimes, you’ll hear about domain models as having these relationships:

• One-to-one
• One-to-many
• Many-to-many

If you can’t quite make sense of what this means, fear not, we’ll explore each one throughout the article. To make this exciting, I’ll delve into EF code first migrations and make it practical. So get your laptops ready as this will be a hands on exercise.

Setup

To get started, fire up Visual Studio 2013 and create a new Console Application. You should end up with a simple Program.cs with a Main static method. I recommend renaming Program.cs to Start.cs to avoid ambiguity.

Now open up Package Manager Console and type:

PM>Install-Package EntityFramework -Version 6.1.3

With the bare foundation in place, we can start to model the data. Let’s imagine a basic system of users, passwords, roles, and programs. This is completely made up, but these concepts will help illustrate the relationships. Now to type up the models in C#:

class User
{
    public long Id { get; set; }
    [Required]
    [StringLength(25)]
    public string Name { get; set; }
    [StringLength(25)]
    public string Company { get; set; }
    public virtual Password Password { get; set; }
    public virtual ICollection<Role> Roles { get; set; }
    public virtual ICollection<Program> Programs { get; set; }
}

In this User model, I’m using it as the base model in which all relationships derive from. This is often referred to as the parent object. Entity framework uses long Id to set the primary key. Data annotations used in validations, like [Required], set constraints in the database.

Now to type up the relational models:

class Password
{
    [Key, ForeignKey("User")]
    public long UserId { get; set; }
    [Required]
    [StringLength(256)]
    public string PasswordHash { get; set; }
    [Required]
    [StringLength(3)]
    public string EncryptionMethod { get; set; }
    public virtual User User { get; set; }
}
class Role
{
    public long Id { get; set; }
    public long UserId { get; set; }
    [Required]
    public string Name { get; set; }
}
class Program
{
    public long Id { get; set; }
    [Required]
    [StringLength(25)]
    public string Name { get; set; }
    public virtual ICollection<User> Users { get; set; }
}

There is a lot going on with these models. The good news is, entity framework can create tables in a database based on the above. To let EF know about our models, we need to create a database context:

class ApplicationDbContext : DbContext
{
    public ApplicationDbContext() : base(@"Data Source=(LocalDb)\v11.0;
        AttachDbFilename=C:\Dev\MyFullPath\EfRelationshipsCodeFirstDb.mdf;
        Integrated Security=True;
        Connect Timeout=30")
    {
    }

    public DbSet<User> Users { get; set; }
    public DbSet<Password> Passwords { get; set; }
    public DbSet<Role> Roles { get; set; }
    public DbSet<Program> Programs { get; set; }
}

For simplicity, I've identified the location of the local database in the base() constructor. Make sure you give yours a full path. With that out of the way, we can now type this up in Package Manager Console:

PM> Enable-Migrations -EnableAutomaticMigrations

Sweet! Visual Studio should open up a Configuration.cs file. It is officially time to play with data.

One-to-one

You should have a Seed method inside the Configuration.cs file we just created. To seed the data:

context.Users.AddOrUpdate(new User
{
    Id = 1,
    Name = "Joe"
});
context.Passwords.AddOrUpdate(new Password
{
    UserId = 1,
    EncryptionMethod = "MD5",
    PasswordHash = "blue"
});

To push the seed into the database, run this in Package Manager Console:

PM> Update-Database

What we have in this example is a user has a single password relationship. One-to-one relationships are useful for appending miscellaneous data you may or may not need. In this contrived example, I’m saying a user may or may not have a password. The Password table does not have an Id on its own, but relies on UserId as the primary and foreign key. Foreign keys in SQL Server get indexed, so no need to append an extra [Index] attribute to relational keys. This is good news for performance when you query large data sets.

We can verify this by examining the database in Server Explorer:

To verify we have data, we can type this up inside the Main static method:

var context = new ApplicationDbContext();
var user = context.Users.Include("Password").First(x => x.Id == 1);
Console.WriteLine("What is your name?");
Console.WriteLine(user.Name);
Console.WriteLine("What is your favorite color?");
Console.WriteLine(user.Password.PasswordHash);

I used Include() to bring in the extra relationship to the Password property. Entity framework uses “lazy loading” to let you choose what data to join dynamically. This is the reason I declared Password as a virtual property in User.

Feel free to poke around in the database, as we are about to embark into deeper waters.

One-to-many

With a User already created, why not add many Roles? After all, it makes sense to claim that a User can have many Roles. Put this in Configuration.cs:

context.Roles.AddOrUpdate(
    new Role
    {
        Id = 1,
        UserId = 1,
        Name = "to find the holy grail"
    },
    new Role
    {
        Id = 2,
        UserId = 1,
        Name = "Uh?"
    });

To update the database:

PM> Update-Database

Run this little test to show how a User has many Roles, put this in Main:

user = context.Users.Include("Roles").First(x => x.Id == 1);
Console.WriteLine("What is your quest?");
Console.WriteLine(user.Roles.First(x => x.Id == 1).Name);
Console.WriteLine("What is the capital of Assyria?");
Console.WriteLine(user.Roles.First(x => x.Id == 2).Name);

Feel free to poke around in the database. Notice UserId in the Roles table gets assigned as a foreign key. This lets the database engine know we have a relational table. You should have something like this in Server Explorer:

Many-to-many

Welcome to the alma mater of all database relationships. Many-to-many relationships are tricky because they are conceptually confusing. In this exercise, I’ve chosen to go with a User that has and belongs to many Programs. So a Program has and belongs to many Users.

To see this baby in action, put this in Configuration.cs:

context.Users.AddOrUpdate(new User
{
    Id = 2,
    Name = "Jane"
});
context.Programs.AddOrUpdate(
    new Program
    {
        Id = 1,
        Name = "A"
    },
    new Program
    {
        Id = 2,
        Name = "B"
    });

You may be wondering where the relational data goes. Program “A”, for example, needs a way to know which user it belongs to. Well, we can write this in code, add this to Configuration.cs:

var user1 = context.Users.Include("Programs").First(x => x.Id == 1);
var user2 = context.Users.Include("Programs").First(x => x.Id == 2);
if (user1.Programs.Count == 0)
{
    foreach (var prog in context.Programs)
    {
        user1.Programs.Add(prog);
    }
    context.SaveChanges();
}
if (user2.Programs.Count == 0)
{
    foreach (var prog in context.Programs)
    {
        user2.Programs.Add(prog);
    }
    context.SaveChanges();
}

Since we don’t have a model we can write data to, we must use the Add() extension method from Linq. To test the data, type this up in Main:

var users = context.Users.Include("Programs").ToList();
users.ForEach(u =>
{
    var progs = u.Programs.ToList();
    progs.ForEach(p =>
    {
        Console.WriteLine("User " + u.Name + " has program " + p.Name);
    });
});

We may also see which users belong to what programs, add this to Main:

var programs = context.Programs.Include("Users").ToList();
programs.ForEach(p =>
{
    var usrs = p.Users.ToList();
    usrs.ForEach(u =>
    {
        Console.WriteLine("Program " + p.Name + " has user " + u.Name);
    });
});

You may still be wondering, how does the database keep track of many-to-many relationships? Well, there is a relational table called ProgramUsers. Let’s see what’s in Server Explorer:

If you hit Ctrl-F5, you’ll see the console fire up:

Conclusion

That’s a wrap! Entity framework gives you a ton of capability when working with data. I find it most useful when you are learning about relationships. Having a good grasp of these concepts will keep you from doing harm to relational databases. The beauty in entity framework is it encourages a clean cut separation of concerns, while embracing the data model.

If interested, you may find the entire demo up on GitHub.

The post Entity Framework Relationships with Code First Migrations appeared first on BeautifulCoder.NET.