Search code examples
c#asp.net-mvcentity-frameworkasp.net-identitycode-migration

Code migration for table with many to many relation and extra columns


I'll create a table with a many to many relationship between the ApplicationUser and an own made class named Topic. The table I'll use for holding the up and down votes from an user for a topic so that the user can't vote multiple times to a topic. So my idea is to use a table like show below (⚿ are the primary keys of the table):

╔══════════════════════╦════════════════════╦═════════╦════════════════════════╗
║       UserId ⚿      ║      TopicId ⚿    ║   Vote  ║          Time          ║
╠══════════════════════╬════════════════════╬═════════╬════════════════════════╣
║ x                    ║ 1                  ║ up      ║ 2016/02/02 15:00:00    ║
║ y                    ║ 2                  ║ up      ║ 2016/02/01 15:00:00    ║
║ y                    ║ 1                  ║ down    ║ 2016/01/01 14:00:00    ║
╚══════════════════════╩════════════════════╩═════════╩════════════════════════╝
            ↓                     ↓              ↓                 ↓           
    Must reference to     Must reference to   Other information about the vote 
  the table where the    the table where my                                   
    users are stored      topic are stored  

How can I migrate this structure to the database with identity framework? I use also the code first way of working.

Below you can find my Topic class and other classes that are extended to that class. The code in the class ApplictionUser is unchanged.

public class Topic
{
    public int TopicId { get; set; }
    public bool Deleted { get; set; }
    public string UserId { get; set; }
    public ApplicationUser User{ get; set; }
    public string Text { get; set; }
    public DateTime Creation { get; set; }
    public List<Vlag> Flags { get; set; }
    public int? BlogId { get; set; }
    public Blog Blog { get; set; }
    public int? ReactionId { get; set; }
    public Reaction Reaction { get; set; }
}

public class Blog: Topic, IVote
{
    public int Id { get; set; }
    public int CategorieId { get; set; }
    public Categorie Categorie { get; set; }
    public string Name { get; set; }
    public int Down { get; set; } 
    public int Up { get; set; }  

    public int CalculateTotal()
    {
        return Up - Down;
    }
}

public class Reactie : Topic, IVote
{
    public int Id { get; set; }
    public int Down { get; set; }
    public int Up{ get; set; }
    public Blog OwnerBlog { get; set; }
    public int OwnerBlogId { get; set; }

    public int CalculateTotal()
    {
        return Up - Down;
    }
}

public interface IVote // used for holding the number of up and down votes for a blog 
                       // or reaction.
{
    int Up { get; set; }
    int Down { get; set; }

    int CalculateTotal();
}

Solution

  • I've found a way to do this:

    Table name: Votes

    VoteId ⚿ UserId ↗ TopicId ↗ Vote Time
    1 x 1 up 2016/02/02 15:00:00
    2 y 2 up 2016/02/01 15:00:00
    3 y 1 down 2016/01/01 14:00:00

    Column information:

    • VoteId ⚿: New primary key of the table
    • UserId ↗: References to the table where the users are stored
    • TopicId ↗: References to the table where my topic are stored
    • Vote and Time: Other information about the vote

    Legend:

    • ⚿ is the primary key
    • ↗ are the forgain keys