Noise from the Basement

I gave the Introduction to ORMs for DBAs presentation at SQL Saturday 710 but it didn’t go as well as I would have liked (i.e. I had trouble getting my demo working).  Since the demo didn’t work live I thought I would show you what was supposed to happen during the demo.  You can find the slides I showed before the demo here.

The goal of this demo is create a basic website that tracks the games you and your friends play and display wins and losses on the home page.  I used a MacBook Pro for my presentation so you will see Mac screen shots.  That said the demo will also work on Windows.

In the previous post we created the Games Results Type table and CRUD methods.  This table stores the possible outcomes of a game (i.e. win, lose, or tie).  In this post we will create the Game Played Table and CRUD.  This table tracks when a game was played but does not track who played the game.  More on that later.

If you skipped the previous post but want to follow along open up 06 – Create GamesPlayed Table and CRUD.  Then run the migrations to create the Player, Games, and GameResultTypes tables in the database.  Your database similar to the below screen shot.  The migration IDs can be different but the tables should exist.

After completing Part 5 our database has the edge tables completed (Players, Games, and GameResultTypes) but none of the central tables (GamesPlayed and GamePlayers).  Lets start with our second most important edge table, the GamesPlayed table.

As usual we will start by creating the model.

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

namespace SaturdayMP.GameTracker.Models
{
    [Table("GamesPlayed")]
    public class GamePlayed
    {
        public int Id { get; set; }

        public int GameId { get; set; }
        public Game Game { get; set; }

        public DateTime DatePlayed { get; set; }
    }
}

This should look similar to the other models we have created so far with one minor difference.  This model is part of a one-to-many relationship with the Games table.  One game can be played many times. 

The below line is what links our new model to the Games table.  It says a GamesPlayed record belongs to a Game record.

public int GameId { get; set; }
public Game Game { get; set; }

Entity Framework, and most other ORMs, are smart enough to correctly map a reference to another model as a foreign key.

This relationship goes both ways and the relationship needs to be added to the Games table as well.  In this case it says a Game record can have many GamesPlayed records which is why a list is used.

public ICollection<GamePlayed> GamesPlayed { get; set; }

With the model created lets generate the migration.  This command should be familiar by now if you have been following along since the beginning.

dotnet ef migrations add CreateGamesPlayedTable

Checking the created migration we can see the foreign key created.

Run the migration on the database to create the new table.

dotnet ef database update

Next lets create the CRUD.

dotnet aspnet-codegenerator controller -name GamesPlayedController -outDir Controllers -m GamePlayed -dc GameTrackerContext -udl

Remember if the files don’t appear in the solution you might need to close the solution and re-open it.

To see our new CRUD methods we need to add the menu item to Layout.cshtml file.

<li><a asp-area="" asp-controller="GamesPlayed" asp-action="Index">Games Played</a></li>

Now lets run the application and navigate to the Games Played page.  Assuming you have a couple games created you should see something similar to the below.  If you don’t have any Games created, go to Games page first and add a couple.

Notice that the Game is linked to the Game ID?  That is not very user friendly.  We should show the name of the game instead. 

Lets start with the Index page.  Open up the page can change the following line so it references the name of the game instead of the ID. 

NOTE: you don’t need to stop running the application.  After your change you can just reload the page in your browser and see you changes.  Just make sure you have saved your changes you might have to hold down the Shift key when reloading the browser.

@Html.DisplayFor(modelItem => item.Game.Name)

That looks better.  Next up the Create page.  If we open up the view we don’t see a reference to the GamesPlayed object.  Instead we see a ViewBag.  This ViewBag contains a list of Games that we want to populate the dropdown with.

To show the game name in the create view we need to make changes to the controller.  Open up the GamesPlayedController and find the Get Create method.  Notice the creation of the ViewData?  ViewData and ViewBag are just different way of referencing the same data.  For some reason the Microsoft scaffolding decided to not be consistent.

The SelectList method is helper method to generate a list of data for populating a dropdown.  It takes a list of objects and what field you want to use as the key (DataValueField) and what field to display (DataTextField) to the user.  Currently the ID field is used as both the data and text fields.  To show the game name change the DataTextField argument, the 2nd one, to “Name”.

When you make changes to the controller you have to restart your application.  You can’t just reload the we page like we did when we made changes to the view.

Restart the application and create a new game played and it should now show the name of the game instead of the ID.

There are 3 more views that need to fixed: Details, Edit, and Delete.  In all cases the changes are similar to the ones we have done above.

To fix the Games Played Details update the view.  Change the Game.Id to Game.Name.

For the edit we need to update the SelectList to show the name but we have to do this in two spots.  The first is the Get and the second is the Post methods of Edit.

Finally we need to update the Delete page to show the name by updating view:

The Games Played section is now complete.  In the next post we will create the Game Players table.  This table will be the most complicated table so we will just create the table and save the CRUD for later.  It should also result in a smaller blog post.

If you got stuck you can find completed Part 6 here.  If you have any questions or spot an issue in the code I would prefer if you opened a issue in GitHub but you can e-mail (chris.cumming@saturdaymp.com) me as well.

I gave the Introduction to ORMs for DBAs presentation at SQL Saturday 710 but it didn’t go as well as I would have liked (i.e. I had trouble getting my demo working).  Since the demo didn’t work live I thought I would show you what was supposed to happen during the demo.  You can find the slides I showed before the demo here.

The goal of this demo is create a basic website that tracks the games you and your friends play and display wins and losses on the home page.  I used a MacBook Pro for my presentation so you will see Mac screen shots.  That said the demo will also work on Windows.

In the previous post we created the Games CRUD and the post before that we created the Games table.  In this post we will create both the GameResultTypes table CRUD methods. 

If you skipped the previous post but want to follow along open up 05 – Create GameResults Table.  Then run the migrations to create the Players and Games table in the database so your database similar to the below screen shot.  The migration IDs can be different but the tables should exist.

Now that we can store tables and players we can start thinking about how to store the games our players have played.  When a game is played we need to store the game that was played, who played in the game, and who won.  A first draft for our data model might look like:

Actually we can’t just track who won as there are three possible outcomes for a game: win, loss, and tie.  Depending on the game it’s possible to have multiple winners, losers, and ties.  For example, a cooperative game like Sentinels of the Multiverse either everyone wins or everyone loses.

We need a table that can track if a player lost, won, or tied a game.  A type table for the results of a game.  Our new data model looks like:

One problem developers and report writers have with type tables is determine what record stands for what type.  For example, what record in the GameResultTypes represents a Win?  How would you query for all the games a player won?

One possible way is to query on the string column:

Select *
From GamesPlayed gp
Inner Join GameResultTypes grt On gp.GameResultTypeId = grt.Id
Where gp.PlayerId = #
And grt.Type = 'Won'

This is a bad idea as the text could change.  How about the ID?

Select *
From GamesPlayed gp
Inner Join GameResultTypes grt On gp.GameResultTypeId = grt.Id
Where gp.PlayerId = #
And grt.ID = 10

Using an auto-increment ID as the identifier is a bad idea as it is set by the database and can change.  This is especially problematic as the database is migrated between environments such as from test to production.

How about not auto-incrementing the ID field?  That would work but I’m not a fan.  I prefer all the ID columns in my database to consistent.  I also prefer not to hard code IDs.  It should be up to the database to decide what the ID value is.  The only time I reference an ID is after I’ve retrieved the record via a non-ID search.

My preference is to have a field called KeyCode.  This is a unique integer field with a value that is set when the record is entered and never changed.  It can then be used whenever you need to access that particular type.  For example, if we assign the Win Game Result Type a Key Code of 10 then we can find all the games a player won by:

Select *
From GamesPlayed gp
Inner Join GameResultTypes grt On gp.GameResultTypeId = grt.Id
Where gp.PlayerId = #
And grt.KeyCode = 10

That means our GameResultTypes table now looks like:

Lets create a Game Result Type model for what we have so far.

using System.ComponentModel.DataAnnotations;

namespace SaturdayMP.GameTracker.Models
{
  public class GameResultType
  {
    public int Id { get; set; }

    public int KeyCode { get; set; }

    [MaxLength(10)]
    public string Type { get; set; }
  }
}

Another common feature of type table is we sometimes know all the records they will contain.  In this case they will only contain win, lose, and tie.

In software if we have a set amount of known types we can wrap them in an enum.  At it’s core an enum is really an integer but it only allows certain values.  For example, we can have colour enum that only allows certain colours:

public enum Colors
{
  Red,
  Blue,
  Green
}

Lets create an enum for our game result types in our model.  We could put in a seperate file but in this case the enum only exists because of the GameResultTypes so lets add it in the same file.

public enum GameResultTypes
{
Win = 10,
Loss = 11,
Tie = 12
}

If we don’t assign numbers to the enums they will be auto assigned.  In this case the enums will be set to the KeyCode field so we want to know what these values are.

Now that we have the enum lets update our model with some syntatic sugar to make it easier to work with the enums.

using System.ComponentModel.DataAnnotations;

namespace SaturdayMP.GameTracker.Models
{
  public enum GameResultTypeEnums
  {
    Win = 10,
    Loss = 11,
    Tie = 12
  }

  public class GameResultType
  {
    public GameResultType()
    {
    }

  public GameResultType(GameResultTypeEnums @enum)
  {
    KeyCode = (int)@enum;
    Type = @enum.ToString();
  }

  public int Id { get; set; }

  public int KeyCode { get; set; }

  [MaxLength(10)]
  public string Type { get; set; }

  public static implicit operator GameResultType(GameResultTypeEnums @enum) => new GameResultType(@enum);

  public static implicit operator GameResultTypeEnums(GameResultType gameResultType) => (GameResultTypeEnums)gameResultType.Id;
  }
}

Notice that we still have the KeyCode field as a integer.  Some ORMs allow you to use enums but currently Entity Framework Core does not allow this.

The couple lines at the bottom simplify the conversion of the GameResultTypeEnums to the GameResultType object so we can write something like:

// Convert enum to model object.
GameResultType gameResultObject = GameResultTypeEnums.Win;

// Convert model object to enum.
GameResultTypeEnums gameResultTypeEnum = gameResultObject;

Now that the model is complete we can create the actual table in the database using this now familiar commands to create the migration:

dotnet ef migrations add CreateGameResultTypesTable

Then apply the migration:

dotnet ef database update

Since we know that this table will only hold 3 records and we know what they are lets seed them.  To do this create a new class in the Data folder and call it DbInitializer.  In our new class create the Initialize method that will seed the data.

using System;
using System.Linq;
using SaturdayMP.GameTracker.Models;

namespace SaturdayMP.GameTracker.Data
{
  public static class DbInitializer
  {

    public static void Initialize(GameTrackerContext context)
    {
      if (context.GameResultTypes.Any())
      {
        // DB has already been seeded.
        return;
      }

      // Seed the Game Type Enums.
      foreach(GameResultTypeEnums result in Enum.GetValues(typeof(GameResultTypeEnums)))
      {
        context.Add<GameResultType>(result);
        context.SaveChanges();
      }

    }
  }
}

The first if statement checks if the data has already been seeded.  If it has, then don’t seed it again.  The second part loops over all the GameResultTypeEnums and creates a record in the database.

Now we need to call our DbInitializer.  Lets do this when our application starts up in the Main:

var context = services.GetRequiredService<SaturdayMP.GameTracker.Data.GameTrackerContext>();
DbInitializer.Initialize(context);

Now if you start the application the GameResultTypes table will be populated.

Notice that we don’t have any screens to edit GameResultTypes.  Lets stop the application and add our CRUD methods. Again this command should now be familiar:

dotnet aspnet-codegenerator controller -name GameResultsTypeController -outDir Controllers -m GameResultType -dc GameTrackerContext -udl

Unlike the other tables we have created we want to limit the actions that can be done to the GameResultTypes table.  Since we know it should only have 3 records which are seeded when the application is started we don’t need the Insert or Delete controller methods.  Lets remove them:

We also need to remove the views for these methods:

Finally lets change the Edit view so the user can only change the description of the type.  The user shouldn’t be able to change the KeyCode.  The quickest way to do this is make the input control for the KeyCode to be read-only in the Edit view:

Finally lets add a menu item so we can easily get to the Game Results Type pages:

Run the application and you should be able to edit just the text of the game results:

In the next post we will create the Games Played table and CRUD methods.  This is one of the the central tables of the application, second only to the Game Players table.

If you got stuck you can find completed Part 5 here.  If you have any questions or spot an issue in the code I would prefer if you opened a issue in GitHub but you can e-mail (chris.cumming@saturdaymp.com) me as well.

I gave the Introduction to ORMs for DBAs at SQL Saturday 710 but it didn’t go as well as I would have liked (i.e. I had trouble getting my demo working).  Since the demo didn’t work live I thought I would show you what was supposed to happen during the demo.  You can find the slides I showed before the demo here.

The goal of this demo is create a basic website that tracks the games you and your friends play and display wins and losses on the home page.  I used a MacBook Pro for my presentation so you will see Mac screen shots.  That said the demo will also work on Windows.

In the previous post we created the Games table and the post before that we created the Players table.  In this post we will create the CRUD methods to access the Games table.  

If you skipped the previous post but want to follow along open up 04 – Create Game CRUD.  Then run the migrations to create the Players and Games table in the database so your database similar to the below screen shot.  The migration IDs can be different but the tables should exist.

Lets get right into it and create the CRUD scaffolding to access the games table.  I’ll create the scaffolding using the same command line I used to the create the Players CRUD scaffolding.

dotnet aspnet-codegenerator controller -name GamesController -outDir Controllers -m Game -dc GameTrackerContext -udl

Just like the Player CRUD scaffolding you should see a GamesController and a bunch of Games views.  If you don’t see the views you might need to close Visual Studio and re-open it.

I just realized we never talked the specifics of this command when we created the player CRUD so let’s do that now.  The first argument says we want to create a controller because the ASP.NET code generator can generate more then just controllers.  Then the “name” argument tells it the name of the controller.

Notice that we pluralize the name?  The model is singular because it represents on thing, in this case a game.  The Table is plural because it holds multiple games.  Finally the controller is plural because it’s the controller for all the games.  Of course you can pick your own standard.  Just be consistent.

Back to the command.  The “outDir” argument says where we want the controller to be created.  The “m” argument tells the scafolding what model to base the controller and views on.  The “dc” argument tells the code generator what data context to use.  Remember you can have more then one data context.  Finally the “udl” argument tells the generator to use our default layout file when creating the views.

Back to our application, we should run it to make sure it works but before we do that lets add the menu item to the Games page.  Open up the _Layout.cshtml file and add the following line:

<li><a asp-area="" asp-controller="Games" asp-action="Index">Games</a></li>

Now that the menu item has been added run the application and you should see the Games menu on the home page.

Click it an will take you to the Games Page index which shows a list of all the games.  Since there are no games yet the list is empty.  Click the Create New link and add a new game to make sure everything works.

Now that everything works lets get back to talking about the code and the ORM methods the controller.  For the PlayersController we talked about the Index, Details, and Create methods.  That leaves the Edit and Delete methods.

First up the Edit methods.  Notice I said methods as there are two Edit methods.  One Edit method to get the information about the game to be edited and the other saves the changes.  Lets start with the Get Edit method.

public async Task<IActionResult> Edit(int? id)
{
  if (id == null)
  {
    return NotFound();
  }

  var game = await _context.Games.SingleOrDefaultAsync(m => m.Id == id);
  if (game == null)
  {
    return NotFound();
  }
  return View(game);
}

 

The id argument passed into the method is the ID of the game to be displayed.  There is some error checking to make sure the id is not NULL.  Remember this is a website on the Internet and the Internet will often send you garbage (i.e. IDs that are not numbers).

if (id == null)
{
  return NotFound();
}

Assuming the id is a number we use Entity Framework to try and find the game in the database.

var game = await _context.Games.SingleOrDefaultAsync(m => m.Id == id);

This line of code tells Entity Framework to look in the games table and find any records that has the match the ID we are looking for.  The SingleOrDefaultAsync returns the game record if found or NULL if no record is found.  If multiple records are found then an exception is raised.  Basically it generates the following query:

Select *
From Games
Where Id = #

The found game record is then displayed to the user via the web page.  The user makes changes to the game, in this case changing the name, then submits the changes.  This submit calls the second Edit method which we call the Post Edit method.

[HttpPost]
[ValidateAntiForgeryToken]
public async Task<IActionResult> Edit(int id, [Bind("Id,Name")] Game game)
{
  if (id != game.Id)
  {
    return NotFound();
  }

  if (ModelState.IsValid)
  {
    try
    {
      _context.Update(game);
      await _context.SaveChangesAsync();
    }
    catch (DbUpdateConcurrencyException)
    {
      if (!GameExists(game.Id))
      {
        return NotFound();
      }
      else
      {
        throw;
      }
    }
    return RedirectToAction(nameof(Index));
  }
  return View(game);
}

Quick aside, why am I calling the methods Get and Post?  They are terms used in the HTML protocal.  A GET request is used to retrieve data from the web server.  So in the case of the GET Edit method we want to “get” information about the game.  Post request is sending data to the webserver.  In this case we want to “post” the changes made to the game.

Back to the code.  First off what do these arguments mean?

public async Task<IActionResult> Edit(int id, [Bind("Id,Name")] Game game)

 

This first argument is the ID of the game we want to edit.  The second argument is the new values of the game.  The Bind in square brackets tells our application what fields to map to the Game object.  In this case it’s all the fields, the ID and Name.  In more complicated objects you might not want the user, especially a malicious user, to set certain fields.

Like the Get Edit method this method starts with some basic error handling.  First making sure the ID of the object to edit and the ID in the game object with the updated values are the same.  Remember we are dealing with Internet requests that might not be valid.

if (id != game.Id) 
{
  return NotFound(); 
}

Then it checks to make sure the updated values in the Game object are valid.  This is where you could also do business logic checking.

if (ModelState.IsValid)

Assuming all the error handling passes then the Game record in the database is updated.  The first line adds the updated Game object to the context.  Remember that changes to the context are written to the database when a save method is called.  In this case there is only one change.

_context.Update(game); 
await _context.SaveChangesAsync();

The actual saving is wrapped in a Try-Catch block to handle any errors in saving the edits.

That is it for the Edit methods.  Let us move on to the Delete methods.  First up the Get Delete method.

public async Task<IActionResult> Delete(int? id)
{
  if (id == null)
  {
    return NotFound();
  }

  var game = await _context.Games.SingleOrDefaultAsync(m => m.Id == id);
  if (game == null)
  {
    return NotFound();
  }
  return View(game);
}

 

Similar to the Get Edit method the Get Delete method simply finds the game we want to delete and returns it.  Presumably so the user can view the game before it’s deleted.  Nothing new to say so lets jump to the Post Delete method.

[HttpPost, ActionName("Delete")]
[ValidateAntiForgeryToken]
public async Task<IActionResult> DeleteConfirmed(int id)
{
  var game = await _context.Games.SingleOrDefaultAsync(m => m.Id == id);
  _context.Games.Remove(game);
  await _context.SaveChangesAsync();
  return RedirectToAction(nameof(Index));
}

 

Again the Post Delete method is very similar to the Post Edit method but in this case we don’t check if the game exists before deleting it.  The delete method with remove the game if it exists and if it does not exist then there is nothing to delete and no error is raised.

The SQL generated would look something like:

Delete From Games
Where Id = #

That is all for now.  In the next part we will create a table that has a one-to-many relationship and talk creating one-to-many ORM models.   If you got stuck you can find completed Part 4 here.  Finally if you have any questions or spot an issue in the code I would prefer if you opened a issue in GitHub but you can e-mail (chris.cumming@saturdaymp.com) me as well.

I gave the Introduction to ORMs for DBAs at SQL Saturday 710 but it didn’t go as well as I would have liked (i.e. I had trouble getting my demo working).  Since the demo didn’t work live I thought I would show you what was supposed to happen during the demo.  You can find the slides I showed before the demo here.

The goal of this demo is create a basic website that tracks the games you and your friends play and display wins and losses on the home page.  I used a MacBook Pro for my presentation so you will see Mac screen shots.  That said the demo will also work on Windows.

In the previous post we created the Games table and the post before that we created the Players table.  In this post we will create the CRUD methods to access the Players table.  

If you skipped the previous post but want to follow along open up 03 – Player CRUD.  Then run the migrations to create the Players and Games table in the database so your database similar to the below screen shot.  The migration IDs can be different but the tables should exist.

In this post we will create some Create, Retrieve, Update and Delete (CRUD) methods to access the Players table.  To quickly create the CRUD methods we will use auto-generated scaffolding.  Scaffolding, in this context, is basic CRUD code.  In production you would expand upon the scaffolding but for this demo we will mostly leave it as is.  It’s a great way to get something up and running.

We will run the scaffolding from the command line but before we do that we need to add the Microsoft.VisualStuido.Web.CodeGeneration.Design NuGet package.

Now that the package is added go the terminal and run the following command to make sure the new NuGet package is installed correctly.

dotnet restore

Now we can run the scaffolding commands.  Lets run it to create the Players CRUD methods.

dotnet aspnet-codegenerator controller -name PlayerController -outDir Controllers -m Player -dc GameTrackerContext -udl

Now go back to Visual Studio and you should see the PlayerController file and a bunch of Player views.  If you don’t see the player views then you might need to close Visual Studio and reopen it.

Now lets test the scaffold pages.  Run the application and you will get the home page.  You need to manually enter the URL for the Players page.

http://localhost:port#/players

You should be able to add, edit, and delete players as you like.

Let’s add a link to he Player pages on the home page.  Stop the application and open up the _Layout.cshtml file and add the following line just under the Home menu item:

<li><a asp-area="" asp-controller="Players" asp-action="Index">Players</a></li>

Now run the application again you should see the Players link.  Click it to make sure it works.

Now that the code is working we should talk about it a bit.  Since this is a ORM talk we will focus on the ORM code but we should quickly talk about the Model-View-Controller (MVC) pattern.

MVC is a software pattern that separates an application into three separate  parts.  You can probably guess what they are.  It’s easiest shown in the below diagram.

In this demo the views are the web pages the end user will see.  The files names created by the scaffolding say what the page does.  If we open one up, say the Index.cshtml, we will see mostly HTML mixed with some C# code as shown below.

When a web page is requested all the C# code is run on the webserver and translated into HTML before being sent to the browser.  In this example the C# code creates a row in the HTML table for every record it finds in the Players table.

@foreach (var item in Model)

The controller class are responsible for populating the views with data from the model.  They are also responsible for updating the model with new data submitted by the user.  If we look at the PlayerController we see there is scaffolding code to view all the players (the index method), create new players, view/edit existing players, and finally delete players.

Do you remember the GameTrackerContext class?  It is the link between the database and the application.  You can see it it passed into the constructor, which is the first bit of code that is executed when a class is instantiated.

public PlayersController(GameTrackerContext context)
{
  _context = context;
}

In this case the constructor takes the context and saves it so other methods in the class can use the context.  For example, the Index method uses the context class to retrieve all the records from the Player table.

 
// GET: Players
public async Task<IActionResult> Index()
{
  return View(await _context.Players.ToListAsync());
}

It’s the same as writing:

Select * From Players

The context takes the player data and maps it to the Player models.  The mapping is done by the ORM, in this case by Entity Framework.  The query is also generated by the ORM.  This is one on of the reasons developers like ORMs so much.  All this grunt work is handled for them.

Yes, I know selecting all the records from a table is not a feasible long term.  Remember this is scaffolding code.

Now let’s take a short look at the Details method.

// GET: Players/Details/5
public async Task<IActionResult> Details(int? id)
{
  if (id == null)
  {
    return NotFound();
  }

  var player = await _context.Players
    .SingleOrDefaultAsync(m => m.Id == id);
  if (player == null)
  {
    return NotFound();
  }

  return View(player);
}

This little bit of code finds on particular Player based on their ID.  It’s the same as writing:

Select * From Players Where Id = #

In this case it either finds the player in the database and maps it to the player model or it returns Null.  Again ORM did all the grunt work.  The developer didn’t have to write the query or the mapping code.

You are starting to get the idea but let’s look at one more, the Create method.  There are two Create methods, the first one simply returns the page for the user to enter the new player.  The second method is what is called when the user submits the create player page.  We are interested in the second method.

// POST: Players/Create
// To protect from overposting attacks, please enable the specific properties you want to bind to, for 
// more details see http://go.microsoft.com/fwlink/?LinkId=317598.
[HttpPost]
[ValidateAntiForgeryToken]
public async Task<IActionResult> Create([Bind("Id,Name")] Player player)
{
  if (ModelState.IsValid)
  {
    _context.Add(player);
    await _context.SaveChangesAsync();
    return RedirectToAction(nameof(Index));
  }
  return View(player);
}

This method creates a new player in the database.  In this case the ASP.NET MVC populates the model from the information the user entered on the web page.  Then the code adds the new player to the context.  The context does not actually save the new player to the database until the SaveChangesAsync method is called.

As a DBA you can probably guess why the context does not save the changes immediately to the database.  It would result in lots of connections and queries which is very inefficient.  Instead the context the takes note of the changes should be made and then batch executes them in one connection.  If possible the ORM will also minimize the number of queries it needs send.  This is known as the Unit of Work pattern.

In this example we are just adding a single record in a Unit of Work but I’m sure you can imagine more complex scenario where several records in several tables are updated in one unit of work.

I think that is enough for now.  In the next post we will create the Games CRUD similar to above and discus the Edit and Delete methods.  If you got stuck you can find completed Part 3 here .  Finally if you have any questions or spot an issue in the code I would prefer if you opened a issue in GitHub but you can e-mail (chris.cumming@saturdaymp.com) me as well.

I gave the Introduction to ORMs for DBAs at SQL Saturday 710 but it didn’t go as well as I would have liked (i.e. I had trouble getting my demo working).  Since the demo didn’t work live I thought I would show you what was supposed to happen during the demo.  You can find the slides I showed before the demo here.

The goal of this demo is create a basic website that tracks the games you and your friends play and display wins and losses on the home page.  I used a MacBook Pro for my presentation so you will see Mac screen shots.  That said the demo will also work on Windows.

This post creates a Player table and all the setup to for Entity Framework.  To follow along open up the 01 – Create Player Table project.  If you need a hand getting the tools installed check out Part 0 – Setup.

We start with a blank ASP.NET MVC application.  It’s similar to what you get when you create a default ASP.NET MVC application in Visual Studio but I’ve deleted some unneeded files related to the About and Contacts pages.

If we run the application we will a home page where we eventually want to display the Win/Lose table for the players.

Now lets make sure our database is up and running.  Since we running the demo on a Mac we need to run SQL Server inside Docker.  There is Docker Compose file in the root folder so we can run the below command to start SQL Server:

 
$ docker-compose up

We will use DataGrip as our SQL Client because it runs on Mac.  You could also use Microsoft SQL Operations Studio.  If we look at our database server we see it has nothing but the master database.

Everything seems to be working so lets start creating our application starting with the Player.

Define Player Model

To track what games people have played we will need to store information about the player and games being played.  Lets start with the player.

If you are a DBA you are itching to open up your SQL Client and create the Players table but we aren’t going to do that.  Instead we will use a feature in most ORMs called Code First.  You define your database model in code and let the ORM handle the database changes for you.

Define the Player model by creating a new class in the Models folder called Player.

This class is our model and represents the Players table in the database.  Since this is a simple application used by friends we only need their name.   So our model will look like:

namespace SaturdayMP.GameTracker.Models
{
  public class Player
  {
    public int Id { get; set; }

    public string Name { get; set; }
  }
}

 

This model defines that we have a Players table and it has two fields: Id and Name.  Notice I said the table name is Players with an “s” but the model name is singular.  Most ORMs have default conventions they use for dealing with names.  Entity Framework assumes your model name is singular and your table will be plural.  Entity Framework also assumes any field named Id will be a primary key.  Of course you can override the conventions if you want.

Setup Connection to Database

The model is now defined but before we can create the table we need a way for our application to access the database.

The first step is to create the database context.  The database context is how most of our code will interact with the database.  We will put the context class in the a new folder called Data and call our context GameTrackerContext.  If your application accesses more then one database you will have multiple contexts.

using SaturdayMP.GameTracker.Models;
using Microsoft.EntityFrameworkCore;

namespace SaturdayMP.GameTracker.Data
{
  public class GameTrackerContext : DbContext
  {
    public GameTrackerContext(DbContextOptions<GameTrackerContext> options) : base(options)
    {
    }

    public DbSet<Player> Players { get; set; }
  }
}

The important part is the DBSet line.  This tells the database context to map the Player model to the underlying Players table.  As we add more tables we will add additional DbSet lines.

Next lets set the connection string.  Do DBAs have to deal with connection strings or is it a developer thing?  Anyway, the connection string is set in the aspsettings.json file:

"ConnectionStrings": {
  "DefaultConnection": "Server=(local);Database=GameTrackerDemo;Trusted_Connection=False;User ID=sa;Password=Password1234!"
}

We won’t see the effects of this next step till later but we will do it now as we are doing all our setup now.  We need to tell the website that we have a database context and that it should automatically include it in controllers.  This is done in the Startup.cs file.  Add the following to the ConfigureServices method.

services.AddDbContext<SaturdayMP.GameTracker.Data.GameTrackerContext>(
 options => options.UseSqlServer(Configuration.GetConnectionString("DefaultConnection")));

Setup Command Line Tools

This next step is a bit of pain.  As of this writing all the cool menu items in Visual Studio on Windows are not available for Visual Studio for Mac.  We have to use the command line and this requires us to manually edit the project file.

Close Visual Studio then find the SaturdayMP.GameTracker project file and open it up in a text editor.

Then add the following two lines, one to the PackageReference ItemGroup and the other to the DotNetCliToolReference ItemGroup:

<ItemGroup>
  <PackageReference Include="Microsoft.AspNetCore.All" Version="2.0.6" />
  <PackageReference Include="Microsoft.EntityFrameworkCore.Design" Version="2.0.2" />
</ItemGroup>

<ItemGroup>
  <DotNetCliToolReference Include="Microsoft.VisualStudio.Web.CodeGeneration.Tools" Version="2.0.3" />
  <DotNetCliToolReference Include="Microsoft.EntityFrameworkCore.Tools.DotNet" Version="2.0.2" />
</ItemGroup>

 

Now reopen the the solution in Visual Studio and rebuild the solution.  If you got any errors check that you put the lines in the correct spot and didn’t forget a double quote or greater/lesser then character.

Open up a new terminal and navigate to the folder where your project is located.  Then run the following command to make sure the command line tools are loaded and installed:

 
$ dotnet restore

To test if everything works run the below command.  It should show something similar to the screenshot below.

 
$ dotnet ef

 

Generate Player Migration

Now we can generate the Player table.  Actually we will generate a migration that will generate the Player table.  A migration is file that contains changes we want to make to the database such as creating tables, add/removing columns on existing tables, adding indexes, etc.

dotnet ef migrations add CreatePlayerTable

Make sure you are in the project folder when you try to run the command or it will fail.  Assuming it succeeded you should see a Migrations folder in Visual Studio and one file in it that ends in “CreatePlayerTable”.

If we open up the file we see it does not contain SQL but C# code.  The reason for using C# is so the same migration file can be run against different databases such as Oracle, PostgreSQL, etc.  Even if you don’t understand C# it should be pretty clear that this file is creating the Player with the Id and Name fields.

A migration file contains contains changes you want to apply to a database.  They can be manually created but in this case we used Entity Framework to create it.  It created the migration file by comparing what the database currently looked like to what it should look like based on the models defined in code.  In this case the the database is empty (non-existent) but we have a Player model defined thus the thus the migration file creates the player table.

Run the Player Table Migration

Now we can run the migration to create the table in the database.

dotnet ef database update

The above command will actually run all the migrations we have defined.  Currently we only have one so it only runs the one.  If we switch to our SQL client and refresh we should see the new Player table in the database.

Notice the _EFMigrationHistory table?  This table tracks what migrations have been run on this database.  If a migration has already been run on the database it won’t be run a second time.

Drop and Recreate the Migration File

One thing you DBAs probably noticed is the Name field is set to max length, which is not good.  Lets change it to 50 characters.

In this case we haven’t shared our migration with anyone else yet so we can delete the migration and try again.  If we had pushed this migration to our code repository we would have to create a new migration to undo our previous one.

If you remember the migration file had an up and a down.  The down part is run if you un-apply the migration.  In this case it will drop our table.

Lets ask Entity Framework to unapply our migration.  In the below example we say we want to undo all the migrations but you can specify specific migrations to undo.

dotnet ef database update 0

Now if we refresh in our SQL Client the database still exists but the Players table has been dropped.

The Player table has been removed from the database but we still need to remove the migration.  It’s best if we let Entity Framework remove the migration for us.  The below command will remove the last migration file created.

dotnet ef migrations remove

Since it was the only migration file the Migrations folder was also removed.  If we had other migrations the folder would not have been removed.

Now that we have cleaned everything up go to the Player model and set the max length to 50 and save your changes.

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

 

Now recreate the migration file.

 
dotnet ef migrations add CreatePlayerTable

Notice that the max length is set in the migration file?  Now apply this migration.

dotnet ef database update

Refresh our database client and you should see Players table with a correct Name length field.

Whew, that was a lot of work.  The next part won’t be as long as we can skip a lot of the first time setup.

If you got stuck you can find completed Part 1 here and the completed application here.  In Part 2 we will create Games table similar to what we did above.  Finally if you have any questions or spot an issue in the code I would prefer if you opened a issue in GitHub but you can e-mail (chris.cumming@saturdaymp.com) me as well.

I gave a presentation at SQL Saturday 710 that didn’t go as well as I liked (i.e. I had trouble getting my demo working).  For those that attended thank you for patience.  Hopefully it was still worth attending and the discussion without the live coding was worth while.

Since the demo didn’t work live I thought I would show you what was supposed to happen during the demo.  The source code for the demo is here.

I used a MacBook pro for my presentation but the demo will also work on Windows.  Just be prepared for Mac screenshots.

First thing you need is Visual Studio 2017.  The community edition is free to use.  To install just keep clicking next.  It should install .NET Core 2.0, or higher for you.

Next you need to setup SQL Server.  You can find the steps to run SQL Server inside a Docker container here.  If you are on Windows the steps are similar just use the Windows SQL Server Docker image instead.

I used DataGrip as my client to access SQL Server but you can use Microsoft SQL Operations Studio instead.

That is all you need to run the demo.  Future posts all walk through demo steps as outlined above.  If you have any questions open a issue in the GitHub repository.