ASP.NET MVC Many-to-Many Relationship (Maintaining insertion order)

Question

I've defined two tables in SQL: "Inquerito" and "Pergunta", and a third table "Inquerito_Pergunta" to make the many-to-many relationship. In that last table, the primary key is both the primary key of the Inquerito and the Pergunta.

I'am supposed to add as many as "Perguntas" as I want into an "Inquerito" instance. And, it's important to keep the insertion order, so when I'm showing it to the user it's shown in the same order. A "Pergunta" can also have multiple "Inquerito", but the order doesn't matter in that case.

I'm using MVC 4 Entity Framework and my Models are defined like this:

    public partial class Inquerito
    {
      public Inquerito()
      {
          this.Pergunta = new List<Pergunta>();
      }

      public System.Guid id { get; set; }
      public virtual ICollection<Pergunta> Pergunta { get; set; }
    }


    public partial class Pergunta
    {
      public Pergunta()
      {
        this.Inquerito = new List<Inquerito>();
      }

      public System.Guid id { get; set; }
      public virtual ICollection<Inquerito> Inquerito { get; set; }
    }

As you can see I've already changed the default HashSet to a List.

To save all stuff to the database I do:

    inquerito.Pergunta.Add(pergunta);
    db.Pergunta.Add(pergunta);
    db.Inquerito.Add(inquerito);

The problem is the insertion order is lost.

After adding all "Pergunta" that I want I do:

        // Grava alterações e desconecta da base de dados.
        db.SaveChanges();

        Inquerito inquerito1 = db.Inquerito.Find(inquerito.id);
        if (inquerito1 != null)
        {
            foreach (Pergunta p in inquerito1.Pergunta.ToList())
            {
                System.Diagnostics.Debug.WriteLine("__pergunta: " + p.descricao);
            }
        }

        db = new quest_geralEntities();

        inquerito1 = db.Inquerito.Find(inquerito.id);
        if (inquerito1 != null)
        {
            foreach (Pergunta p in inquerito1.Pergunta.ToList())
            {
                System.Diagnostics.Debug.WriteLine("__pergunta: " + p.descricao);
            }
        }

So, the first time I print all the "Pergunta" linked to that "Inquerito" everything is shown in the right order (insertion order), but when I update the context, with: "new quest_geralEntities()" when I print it again the insertion order is completely lost.

I've been struggling with this problem for several hours now and I can't find a solution. I hope I've been clear enough to be helped.

Thanks.

Answer 1

If you want to maintain insertion order, I recommend using a field containing an int that increments. You can add an int identity column without it being the primary key and use that column to sort on, maintaining your insertion order.