Using code first entity frameworks in .NET – my cheat sheet

If you are a .NET developers, chances are you will need a database at some point. In the olden days there was ADO.NET – but that was not fun because you had to be tightly-coupled with the database, and you were writing actual T-SQL statements, it was a mess.

image

Then, Entity Framework came out, which is an Object Relational Mapping (ORM) tool which lets you map your database tables, to data structures in your code. So instead of getting back a recordset, and referencing the 3rd column for the address, you can now just reference customer.StreetAddress. It’s a huge leap of efficiency – and with it, there are two fundamental ways to use is: start with the database, or start with code.

Database-first vs Code-first:
If you work in a big company, this decision might be already decided for you. However, if you have a choice, there are some pros and cons to each.

Database-first is where you create the database structure in SQL (or some other supported RDMBS technology), and then using an .edmx file, you can generate the proper C# or VB.NET classes. When the database changes, you simply right-click, refresh your model (in the .edmx) and your code is now synchronized. The pros here is that the .NET code just magically works. The cons are that A) your code must follow the database, versus your code pushing the latest changes to the database. B) the naming of database tables and columns is often different that .NET code, so interacting with these names is very not-.NET and C) the versioning and synchronizing can be a nightmare.

Code-first is where your code is the authority for your whole app. You design data structures that represent your data, and when you run the app, it either creates or modifies the database to bend to the will of your app. Pretty much the only downside to this is that your application needs database owner (dbo) privilege because it might need to drop, create, or alter database objects and it needs permissions to do that. If that isn’t a problem in your organization though, this is a really great option! But, how do you do it?

Install Entity Framework via Nuget:
As a prerequisite, in Visual Studio, right-click on your project and choose Manage Nuget Packages and install EntityFramework.

Creating data structures:
There are lots and lots of resources on the web for seeing how to do database-first. However, information on code-first is much more difficult to find, plus it has changed slightly each year with each version of Entity Framework. So, this post will be about using Entity Framework v6.1.3.0.

You start by creating some data structures to represent the kind of data that you want to store. As a convention, these should exist in a folder, and sub-namespace of “Models”. So, create a Models folder and create some classes:

public class Artist
{
    public Guid ArtistId { get; set; }
    public String Name { get; set; }
    public List<Album> Albums { get; set; }
}

public class Album
{
    public Guid AlbumId { get; set; }
    public String AlbumName { get; set; }
    public Int32 ReleaseYear { get; set; }
    public Guid ArtistId { get; set; }
    public Artist Artist { get; set; }
}

public class Song
{
    public Guid SongId { get; set; }
    public Int32 TrackNumber { get; set; }
    public String Name { get; set; }
    public Guid AlbumId { get; set; }
    public Album Album { get; set; }
}

So – nothing fancy, just a logical representation of: Artist –> Album –> Song. Notice though there is a reference to the Id of the parent and a “navigation property” which points to the parent too. So, Song references both AlbumId and Album. This is a very convenient way to “walk” though the data, as we will see below. To be able to do song.Album.Artist.Name is pretty convenient – and is natively supported by Entity Framework.

Once you have the structure the way you want, there are two other things we’ll need for later:

  • Add a [Key] attribute above the primary key of each data structure. Add a “using” or “imports” statement at the top of the code file for System.ComponentModel.DataAnnotations – the assembly should’ve been added when you install the Entity Framework nuget package.
  • Mark all the properties as “virtual” (“overrideable” in VB.NET) – this is because of how Entity Framework does it’s ORM mapping.

Now, I’m left with the data structures in their final form:

public class Artist
{
    [Key]
    public virtual Guid ArtistId { get; set; }
    public virtual String Name { get; set; }
    public virtual List<Album> Albums { get; set; }
}

public class Album
{
    [Key]
    public virtual Guid AlbumId { get; set; }
    public virtual String AlbumName { get; set; }
    public virtual Int32 ReleaseYear { get; set; }
    public virtual Guid ArtistId { get; set; }
    public virtual Artist Artist { get; set; }
}

public class Song
{
    [Key]
    public virtual Guid SongId { get; set; }
    public virtual Int32 TrackNumber { get; set; }
    public virtual String Name { get; set; }
    public virtual Guid AlbumId { get; set; }
    public virtual Album Album { get; set; }
}

Set up your DbContext:
Those data structures are all well and good, but how do you actually use them? You get a handle to the database via a DbContext – and in that Context, you tell it about the data structure that you want to push to the database… the data structures above.

So, in that same Models folder, create a MusicInformationDbContext, for example, like this:

public class MusicInformationDbContext : DbContext
{
    public DbSet<Artist> Artists { get; set; }
    public DbSet<Album> Albums { get; set; }
    public DbSet<Song> Songs { get; set; }
}

and you’ll need a using/imports statement at the top of System.Data.Entity for it to be able to find DbContext.

Set up your database, and point to it:
I’ll assume you have a database set up some place and you have DBO privilege on it. Since Entity Framework is going to be modifying the structure, it MUST have this permission. So first, set up a connection string your web.config/app.config which points to the server and database:

<connectionStrings>
  <add name=”MusicInformationConnectionString” providerName=”System.Data.SqlClient” connectionString=”Server=server;UserID=dbouser;Password=dbopassword;database=MusicInformationDB”/>
</connectionStrings>

Note the connection string name. We point to this from the DbContext class – we can at least set the default:

public class MusicInformationDbContext : DbContext
{
    public MusicInformationDbContext() : base(“MusicInformationConnectionString“)
    {
    }
    public DbSet<Artist> Artists { get; set; }
    public DbSet<Album> Albums { get; set; }
    public DbSet<Song> Songs { get; set; }
}

so now, whenever we create a new instance of this DbContext, it will automatically know to start talking to our new database.

Have your code create the database:
One of the big benefits of code-first is that your code can automatically generate a database. But how do you do that? This is done, and versioned, via a sub-technology of Entity Framework called “migrations”. To use migrations, open the Package Manager Console window in Visual Studio and type:

Enable-Migrations

This will create a Migrations folder in your project which has some important information. First, is the constructor of this Configuration class which was generated – in my case, I want to turn ON automatic migrations. Second is the overriden “Seed” method. When your application runs, if the database is created, it will run the code in Seed to AddOrUpdate key data in your database. For example, in a recent project, I had a Severity table where I wanted an Info, Warning, and Error record to always exist – so I set those up in this Seed method. You should definitely use .AddOrUpdate(..) for this, per the directions – because that way it will smoothly handle if those records already exist.

With everything we’ve described above, you would have to go back into Package Manager Console and run:

Update-Database

to go and generate your new database, and run the Seed method. However, in my particular case, I wanted this to happen automatically whenever the program ran. So, if you want that, in the “beginning” of your program, run something like this:

Database.SetInitializer<MusicInformationDbContext>(
    new MigrateDatabaseToLatestVersion<MusicInformationDbContext, Configuration>());

Note that the DbContext is a reference to my specific example above, and “Configuration” is the name of the file in the Migrations folder that was created. If you’ve renamed that, you would use your new name, here. An when I say at the begining of your project, I just mean whenever your program starts. For a command-line program, in static void main. For an ASP.NET app, in your Application_OnStart, etc.

Using your new database:
Now, let’s put it all together and see if it works. We should be able to (automatically create the database) and add new records, like this:

using (MusicInformationDbContext context = new MusicInformationDbContext())
{
    Artist artist = new Artist()
    {
        ArtistId = Guid.NewGuid(),
        Name = “Hanson”
    };
    Album album = new Album()
    {
        AlbumId = Guid.NewGuid(),
        ArtistId = artist.ArtistId,
        AlbumName = “Middle of Nowhere”,
        ReleaseYear = 2009
    };
    Song song = new Song()
    {
        SongId = Guid.NewGuid(),
        AlbumId = album.AlbumId,
        TrackNumber = 2,
        Name = “Mmmbop”
    };

    context.Artists.Add(artist);
    context.Albums.Add(album);
    context.Songs.Add(song);

    context.SaveChanges();
}

And modify existing records like this:

using (MusicInformationDbContext context = new MusicInformationDbContext())
{
    Song song = context.Songs.FirstOrDefault(item => item.Name == “Mmmbop”);

    // Or, if song existed from a previous request, you’d just need to
    // “attach” it to the current context:
    //context.Songs.Attach(song);

    if (song != null)
    {
        // Change the name
        song.Name = “Mmm-bop”;

        // Save the changes to the database.
        context.SaveChanges();
    }
}

and then read records like this:

using (MusicInformationDbContext context = new MusicInformationDbContext())
{
    IEnumerable<Song> allSongs = context.Songs
        .Include(“Album”)
        .Include(“Album.Artist”);

    foreach (Song currentSong in allSongs)
    {
        Console.WriteLine(“Artist: ” + currentSong.Album.Artist.Name
           
+ ” Album: ” + currentSong.Album.AlbumName
            + ” Song: ” + currentSong.Name);
    }

    Console.ReadKey();
}

There are of course arguments for and against wrapping this functionality in a Repository Pattern for example – but even without that, this sort of approach to communicating with a database can be thousands of lines of code less, than using ADO. All-in-all, it’s still the best way to do this in .NET. So, if you can get away with code-first AND you want to auto-push your database structure – above is how you do it!

Bottom line:
Because of my restrictions at work, I don’t do code-first often, and every time I do, I need to re-look this up. Even better, my colleague Stephen showed me that SetInitializer<T> technique, which auto-propagates your database structures. So, I wanted to write all of this down in one place so that I don’t have to re-learn it every time I need to do it!

Posted in Best-practices, Computers and Internet, Development Tools, Entity Framework, General, Uncategorized, Visual Studio
3 comments on “Using code first entity frameworks in .NET – my cheat sheet
  1. Muni says:

    Rob, it is very much useful. Probably one year back, an interviewer asked me about the Reverse Engineering in Entity Framework. I am sure it is Code First with Existing Database.

    But still I am having bit confusion over it. Thank You.

    Like

  2. […] Please navigate to this links to know about the code first approach. […]

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Archives
Categories

Enter your email address to follow this blog and receive notifications of new posts by email.

Join 5 other followers

%d bloggers like this: