Migration added an extra column in table without having a property in model

Question

I am working with .NET Core 2 MVC and I have the following models:

BooksCategories Model:

 public class BooksCategories
{
    [Key]
    public int BookCategoryId { get; set; }

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

    public bool isSelected { get; set; }
}

FirstInput Model:

public class FirstInput
{
    //[Key]
    public int FirstInputId { get; set; }

    public string UserId { get; set; }

    [ForeignKey("UserId")]
    public virtual ApplicationUser User { get; set; }

    public bool Books { get; set; }

    public string SelectedBookCategories { get; set; }

    public List<BooksCategories> BookCategoriesObj { get; set; }


}

My idea is when the user first registers into an an application, he/she is redirected to a form-page to complete his profile by ticking his favorite categories. The categories inside the View are populated from the database.

However, when I did the migration, I got the following result:

migrationBuilder.CreateTable(
            name: "BooksCategories",
            columns: table => new
            {
                BookCategoryId = table.Column<int>(nullable: false)
                    .Annotation("SqlServer:ValueGenerationStrategy", SqlServerValueGenerationStrategy.IdentityColumn),
                FirstInputId = table.Column<int>(nullable: true),
                Name = table.Column<string>(nullable: false),
                isSelected = table.Column<bool>(nullable: false)
            },
            constraints: table =>
            {
                table.PrimaryKey("PK_BooksCategories", x => x.BookCategoryId);
                table.ForeignKey(
                    name: "FK_BooksCategories_UserFirstInput_FirstInputId",
                    column: x => x.FirstInputId,
                    principalTable: "UserFirstInput",
                    principalColumn: "FirstInputId",
                    onDelete: ReferentialAction.Restrict);
            });

The framework has added another column in the BookCategories table called FirstInputId that is dependent with an indexer from the FirstInput model. This has seriously messed up my user generated entries. I don't want user data to be stored inside the BooksCategories table, I only want to fetch it from the db. Then they will be saved as multiple answers in a string in the FirstInput table.

What have I done wrong? P.S. I tried to find something similar but nothing was close to the problem I am having.

score 0 · Answer 1 · answered Mar 16 '18 at 07:32

0

My idea is when the user first registers into an application, he/she is redirected to a form-page to complete his profile by ticking his favorite categories.

Why don't you store these preferences in the table ApplicationUser? It creates that foreign key because you configured a one-to-many relationship. With you models, A FirstInput has many BookCategory. And why do you store isSelected in a database though...?

My solution is FirstInput should not be stored in the database. Instead, treat it as an InputModel for a view. On view, if the user selects a radio button called Horror Books, find the Id and add to the List FirstInput.BookCategoriesObj and so on.

And re-design the database models like this:

public class ApplicationUser : IdentityUser
{
    ICollection<BookCategory> BookCategories { get; set; }
}

public class Book
{ ... }

public class BookCategory
{
    public int Id { get; set; }

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

    public ICollection<ApplicationUser> ApplicationUsers { get; set; }
}

This is now many-to-many relationship (An application user can have zero or many favorite book categories. An category can be a favorite choice of zero or many users). Entity Framework will create a joining table to store these preferences (probably named ApplicationUserBookCategories).

To insert records, just do it like other normal types. Or you can see more here.

answered Mar 16 '18 at 07:32

Lam Le

1,489
3
14
31

My initial thought was not to burden the users table with more information. Then, the property isSelected, was to create checkboxes (since the user should add more than one book category). For every isSelected checkbox, the value would be added into a string and the entry would look like – SoftDev30_15 Mar 16 '18 at 08:39
UserId | Books | BooksCategories 1245 | true | Horor, Fiction,Mystery The truth is I didn't want the is_selected to have a column in my BooksCategories table, but from other solutions that I looked up, the correct way for values to be stored, was if the checkbox had is_selected == true – SoftDev30_15 Mar 16 '18 at 08:47
`UserId | Books | BooksCategories 1245 | true | Horor, Fiction,Mystery` Is this a record of ApplicationUser table? It violates normal form 1... Why do you want to store Is_Selected in the database, but not a clear table like this `(Id | BookCategoryId | ApplicationUserId)` which is called UsersBookPreference? If we can sort Is_Selected out, I'm sure even if you go with that table violating NF1, we can still do that. – Lam Le Mar 16 '18 at 10:22
Initially I wanted a clear table like ID, BookCategories and ApplicationUserId, for my FirstInputModel (and it was separate from the ApplicationUser model). But I was not able to detect whether the checkboxes were selected (and decided I needed a bool property isSelected). Obviously I was mistaken. I did not want to use jquery for it, but maybe I will have to. If I have a model BookCategories with the properties CategoryId, and CategoryName, with a table in the database, and I want to populate checkboxes with values from this table, how would I do it? – SoftDev30_15 Mar 17 '18 at 08:40

Migration added an extra column in table without having a property in model

1 Answers1