How to save rows in a specific order into an associative entity in ASP .NET?

Question

I am creating a system that can register players playing games. One game can have up to 4 players, and players can have unlimited games. The relevant source code for this:

    public class Player
{
    public int PlayerId { get; set; }

    [Required]
    public string Username { get; set; }

    public virtual ICollection<Game> Games { get; set; }
}

And:

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

        [Required]
        public string Location { get; set; }

        public virtual ICollection<Player> Players { get; set; }
    }

EF generated the tables properly and also one extra table: PlayerGames, which has two columns: Player_PlayerId and Game_GameId. It looks like this: https://i.stack.imgur.com/mVgm9.jpg

The Create method of the GamesController looks like this:

   public ActionResult Create([Bind(Include = "GameId,Location,PlayerId")] Game game)
    {
            int[] ids = Request.Form["PlayerId"].Split(',').Select(Int32.Parse).ToArray();
            List<Player> k = ids.Select(t => db.Players.First(x => x.PlayerId == t)).ToList();
            ICollection<Player> players = ids.Select(t => db.Players.First(x => x.PlayerId == t)).ToList();
            game.Players = players;

            db.Database.Log = s => Debug.Write(s);
            if (ModelState.IsValid)
            {
                db.Games.Add(game);
                db.SaveChanges();
                return RedirectToAction("Index");
            }
            return View(game);
    }

The problem is that db.SaveChanges() generates a Query that ignores the order of the Players list: the Id-s will be in order every single time. However, for the system it is important to know what order the players are in the list. Is there any way I could insert it into the database preserving the order?

Here is the SQL log for reference:

INSERT [dbo].[PlayerGames]([Player_PlayerId], [Game_GameId])
VALUES (@0, @1)
-- @0: '2' (Type = Int32)
-- @1: '1013' (Type = Int32)
-- Executing at 4/21/2015 1:22:13 PM +02:00
-- Completed in 1 ms with result: 1

INSERT [dbo].[PlayerGames]([Player_PlayerId], [Game_GameId])
VALUES (@0, @1)
-- @0: '3' (Type = Int32)
-- @1: '1013' (Type = Int32)

-- Executing at 4/21/2015 1:22:13 PM +02:00
-- Completed in 0 ms with result: 1

INSERT [dbo].[PlayerGames]([Player_PlayerId], [Game_GameId])
VALUES (@0, @1)
-- @0: '4' (Type = Int32)
-- @1: '1013' (Type = Int32)
-- Executing at 4/21/2015 1:22:13 PM +02:00
-- Completed in 0 ms with result: 1

INSERT [dbo].[PlayerGames]([Player_PlayerId], [Game_GameId])
VALUES (@0, @1)
-- @0: '6' (Type = Int32)
-- @1: '1013' (Type = Int32)
-- Executing at 4/21/2015 1:22:13 PM +02:00
-- Completed in 0 ms with result: 1

You need to add a helper column for the order and sort by that. Relational databases don't preserve order unless you tell them to order it somehow. — Sami Kuhmonen, Apr 21 '15 at 11:46
You need to make it look something like the answer in this question - http://stackoverflow.com/questions/7050404/create-code-first-many-to-many-with-additional-fields-in-association-table — GregoryHouseMD, Apr 21 '15 at 12:00

GregoryHouseMD · Accepted Answer · 2015-04-21T12:51:58.437

Your classes should look like this:

public class Player
{
    public int PlayerId { get; set; }
    [Required]
    public string Username { get; set; }
    public virtual ICollection<OpenedGame> OpenedGames { get; set; }
}

public class Game
{
    public int GameId { get; set; }
    [Required]
    public string Location { get; set; }
    public virtual ICollection<OpenedGame> OpenedGames { get; set; }
}

public class OpenedGame
{
    public int OpenedGameId { get; set; }
    public int GameId { get; set; }
    public virtual Game Game { get; set; }
    public int PlayerId { get; set; }
    public virtual Player Player { get; set; }
    public int Location { get; set; }
}

This way you can keep track of which player is on which location in game.

EDIT: If you want to check what games a player is playing do this:

var list = context.OpenedGames.Where(x=>x.PlayerId==pId).ToList();

or a list of players that play a single game:

var list = context.OpenedGames.Where(x=>x.GameId==gId).ToList();

Thank you very much, however, I still don't know how to implement this in my solution. I have an ICollection of Players because I need the player objects for some methods in my game class. Is there any way I could get a hold of the player objects like this? — JC Hammer, Apr 21 '15 at 12:36
I added how you can get the list or games or players. Read up a but on EF and it'll all clear up. — GregoryHouseMD, Apr 21 '15 at 12:55

How to save rows in a specific order into an associative entity in ASP .NET?

1 Answers1