c# - How to use enum and json to insert data in database?

Question

I'm trying to create a question table that includes descriptive and choice and YesNo questions and I want to insert all questions in one table. when I want to create choice question I wanna use enum and json to insert choices into table the same as YesNo question. And I wanna choose the correct answer to the question table too. How can I do that? This is my Question class:

 public class Question
{
    [Key]
    public int Id { get; set; }//1
    public int CourseId { get; set; }
    public string QuestionTitle { get; set; }
    public decimal Grade { get; set; }
    public DateTime TimeDuration { get; set; }
    public DateTime StartTime { get; set; }
    [EnumDataType(typeof(Choice))]
    public string Choice { get; set; }
    public string CorrectChoice { get; set; }
    [EnumDataType(typeof(YesNo))]
    public bool? YesNo { get; set; }
    public string Descriptive { get; set; }

    #region relations
    public virtual IdentityUser IdentityUser { get; set; }
    public Course course { get; set; }
    #endregion
}
public enum Choice
{
    Choice1, Choice2, Choice3, Choice4
}
public enum YesNo
{
    Yes, No
}

when I tried to create seprate tables for questions(descriptive table, YesNo table, choice table) my supervisor said it's not right and you should do that in above way.

question from:https://stackoverflow.com/questions/65870507/how-to-use-enum-and-json-to-insert-data-in-database

Answer 1

I think you are looking for an extra table to store your choices in, since you need a list of choices for each question, right?

public class QuestionChoice
{
    [Key]
    public int Id { get; set; }
    public string Description { get; set; }
    public bool IsTheCorrectAnswer { get; set; }

    #region relations
    // This should point to the question the choice belongs to
    public Question Question { get; set; }
    #endregion
}

Then update your Question class with a navigation property to the list of choices:

public class Question
{
    [Key]
    public int Id { get; set; }
    public int CourseId { get; set; }
    public string QuestionTitle { get; set; }
    public decimal Grade { get; set; }
    public DateTime TimeDuration { get; set; }
    public DateTime StartTime { get; set; }
    [EnumDataType(typeof(YesNo))]
    public bool? YesNo { get; set; }
    public string Descriptive { get; set; }

    #region relations
    public virtual IdentityUser IdentityUser { get; set; }
    public Course course { get; set; }
    
    // New property instead of 'Choice' and 'CorrectChoice'
    public List<QuestionChoice> Choices { get; set; }
    #endregion
}

To insert and retrieve a question with choices:

var question = new Question
{
    // Set all properties...

    Descriptive = "What color is a Marigold flower?",
    Choices = new List<QuestionChoice>
    {
        new QuestionChoice
        {
            Description = "It's red!",
            IsTheCorrectAnswer = false
        },
        new QuestionChoice
        {
            Description = "It's yellow!",
            IsTheCorrectAnswer = true
        }
    }
};

// Insert into the database
_dbContext.Questions.Add(question);
_dbContext.SaveChanges();

// Read from the database
var question = _dbContext.Questions.FirstOrDefault(q => q.Id == 1); // Whatever ID it has
var choices = question.Choices;
// ...