Adding Unchanged items to an Object Graph full of Added items

Question

I have a domain object called Option. It is part of a larger Quiz schema. The first two primary keys (Id) in that table correlate to the values “true” and “false” of the Text column. But after that, the items in that table are of no consequence for the scope of this question. The significance of those two rows “true” and “false” are that they will be re-used many times over.

Also note that the Option table is an identity (auto increment) table. And I do have the following in my OptionMap file:

this.HasKey(t => t.Id);
this.Property(t => t.Id).HasDatabaseGeneratedOption(DatabaseGeneratedOption.Identity);

Obviously, I don’t want to keep adding true and false to that table every time a new question with true and false as options is added to the Quiz.

In JavaScript, at the client, I have coded it such that when a new quiz is created, and true and/or false are added as options for the question, then an Id of 1 and 2 is added to the object sent to the server (for true and false respectively - recall top 2 rows as explained at top of post). When the question is not using either of those as an option, the Id of the Option sent to the server is undefined.

At the server, in my service, I have written the following code which is able to detect that true or false has been added as an option and which attempts to use the existing stored value for true/false (as applicable) from the option table:

foreach (var question in newQuestions)
{       
    _quizRepository.AddQuestion(question);
    var options = question.QuestionWithOptions.Select(q => q.Option).ToList();

    // This loop is an edge case. true and false will be very common answers. Rather than storing them over and over again,
    // this loop will re-use the already stored answers for true and false, which complies with the quiz schema.
    foreach (var option in options)
    {
        if (option.Text.Equals("true", StringComparison.OrdinalIgnoreCase) ||
            option.Text.Equals("false", StringComparison.OrdinalIgnoreCase))
        {
            var storedOption = _quizRepository.Context.Option.Single(o => o.Id == option.Id);
            foreach (var questionWithOption in question.QuestionWithOptions)
            {
                if (questionWithOption.Option.Id == storedOption.Id)
                {
                    questionWithOption.Option = storedOption;
                    _quizRepository.Context.Entry(questionWithOption.Option).State = EntityState.Unchanged;
                }
            }
        }                    
    }

    _quizRepository.SaveChanges();
}

I have tried to set the state to Unchanged and Modified, but every time I call SaveChanges, it adds new rows for true and false. And as noted above, this is exactly what I am trying to avoid.

Any help in figuring out how I can save these new questions using the existing Options for "true" and "false" will be much appreciated.

Edit - Added Domain Classes

Question:

public partial class Question
{
    public Question()
    {
        QuestionWithOptions = new List<QuestionWithOption>();
        QuizWithQuestions = new List<QuizWithQuestion>();
    }
    public int Id { get; set; }
    public string Text { get; set; }
    public int? idTimeLimit { get; set; }
    public QuestionType idType { get; set; }

    public virtual ICollection<QuestionWithOption> QuestionWithOptions { get; set; }
    public virtual ICollection<QuizWithQuestion> QuizWithQuestions { get; set; }
}

QuestionWithOption:

public partial class QuestionWithOption
{
    public int Id { get; set; }
    public int idQuestion { get; set; }
    public int idOption { get; set; }
    public bool CorrectAnswer { get; set; }
    public string Letter { get; set; }
    public virtual Option Option { get; set; }
    public virtual Question Question { get; set; }
}

Option:

public partial class Option
{
    public Option()
    {
        this.QuestionWithOptions = new List<QuestionWithOption>();
    }

    public int Id { get; set; }
    public string Text { get; set; }
    public virtual ICollection<QuestionWithOption> QuestionWithOptions { get; set; }
}

Add Question Method

This toggles between the following 2 lines of code (I'm trying anything and everything)

    public void AddQuestion(Question question)
    {
        //((TTSWebinarsContext) db).Question.Attach(question);
        ((TTSWebinarsContext) db).Question.Add(question);
    }

Edit - Final Comment

Let me just add how ridiculous this situation is. In the same operation, where it is adding new Options to the Option table (instead of using the existing Options 1 and 2), the values inserted into the QuestionWithOption table are the correct values i.e. 1 and 2. So, the operation itself is not even sound. There are redundant values being added to the Option table which are not even FKs to anything in the operation. I'm starting to understand fans of NHibernate.

Answer 1

Another option wouild be without messing around with the states (but a bit more ressource intensive): iterate over option, replace default answers,remember indices in options of those. Then once out of the iteration, remove all items by their index as you remembered them.

I do it this way because I do not want to meddle in the affairs of EF tooo much...

EDIT:

Or even better: Add a field that is not mapped to the Option class and set this if you want to remove that option. An after the replace do a list.RemoveAll(f => f.Flag);

Another EDIT:

And I think you have your loops mixed up. I would - if I see the structures correctly - rewrite it to:

void storeQustionsOptimized( QUESTION newQuestions CONTEXT _quizRepository )
{
    var defaultOptionTrue = xxx;
    var defaultOptionFalse = xxx;

    foreach (var question in newQuestions)
    {     
        foreach (var questionWithOption in question.QuestionWithOptions)
        {
            if (questionWithOption.Option.Text.Equals("true", StringComparison.OrdinalIgnoreCase) )
            {
                questionWithOption.Option = defaultOptionTrue;
            }
            else if( questionWithOption.Option.Text.Equals("false", StringComparison.OrdinalIgnoreCase))
            {
                 questionWithOption.Option = defaultOptionFalse;
            }
        }
        _quizRepository.AddQuestion(question);
    }

    _quizRepository.SaveChanges();        
}

Answer 2

I think this is the error:

questionWithOption.Option = storedOption;
_quizRepository.Context.Entry(questionWithOption.Option).State = EntityState.Unchanged;

Should be solved by:

questionWithOption.Option = storedOption;

Without attaching option's entity to context, it should use provided option.

Answer 3

If you try to fix the code mantaining true and false on database (probably not the best solution), the first issue, in this piece of code

        var storedOption = _quizRepository.Context.Option.Single(o => o.Id == option.Id);
        foreach (var questionWithOption in question.QuestionWithOptions)
        {
            if (questionWithOption.Option.Id == storedOption.Id)
            {
                questionWithOption.Option = storedOption;
                _quizRepository.Context.Entry(questionWithOption.Option).State = EntityState.Unchanged;
            }
        }

You read storedOption from DB and you never change it in this piece of code. Then you set _quizRepository.Context.Entry(questionWithOption.Option).State = EntityState.Unchanged; that is not necessary because questionWithOption.Option is storedOption and no one changed storedOption.

Second issue somewhere in your mapping (probably in question.QuestionWithOptions) or in _quizRepository.AddQuestion(question);)

EDIT
reading other comments, be also sure to use always the same context also in question if you retrieve it from DB. But in this case the error should not be PK violation but should be something like the context between entities is different.

Answer 4

This is what I coded to search for a solution, before you provided the source code, and it worked. Only my random option was added multiple times.

Main

static void Main(string[] args)
{
    var optionTrue = new Option()
    {
        Id = 1,
        Text = "true"
    };
    var optionFalse = new Option()
    {
        Id = 2,
        Text = "false"
    };
    var optionRandom = new Option()
    {
        Text = "edaada"
    };
    var question1 = new Question();
    question1.QuestionWithOptions.Add(new QuestionWithOptions()
    {
        Id = 1,
        Option = optionTrue
    });
    question1.QuestionWithOptions.Add(new QuestionWithOptions()
    {
        Id= 2,
        Option = new Option()
        {
            Id = 10,
            Text = "blala"
        }
    });

    var newQuestions = new List<Question> {question1};

    using (var quizRepository = new QuizRepository())
    {
        foreach (var question in newQuestions)
        {
            var options = question.QuestionWithOptions.Select(q =>q.Option).ToList();

            foreach (var option in options)
            {
                if (option.Text.Equals("true", StringComparison.OrdinalIgnoreCase) ||
                    option.Text.Equals("false", StringComparison.OrdinalIgnoreCase))
                {
                    var storedOption = quizRepository.Options.Single(o => o.Id == option.Id);
                    foreach (var questionWithOption in question.QuestionWithOptions)
                    {
                        if (questionWithOption.Option.Id == storedOption.Id)
                        {
                            questionWithOption.Option = storedOption;
                            quizRepository.Entry(questionWithOption.Option).State = EntityState.Unchanged;
                        }
                    }
                }
            }

            quizRepository.Questions.Add(question);

            quizRepository.SaveChanges();
        }
    }
}

DBContext

public class QuizRepository : DbContext
    {
        public virtual DbSet<Question> Questions { get; set; }
        public virtual DbSet<Option> Options { get; set; }
        public virtual DbSet<QuestionWithOptions> QuestionWithOptions { get; set; }

        public QuizRepository() : base("name=QuizDBConnectionString")
        {
        }
    }
    public class Question
    {
        public int Id { get; set; }
        public Question()
        {
            QuestionWithOptions = new HashSet<QuestionWithOptions>();
        }
        public ICollection<QuestionWithOptions> QuestionWithOptions { get; set; }
    }

    public class QuestionWithOptions
    {
        public int Id { get; set; }
        public Option Option { get; set; }

        public int QuestionId { get; set; }
        public Question Question { get; set; }
    }

    public class Option
    {
        public Option()
        {
            QuestionWithOptions = new HashSet<QuestionWithOptions>();
        }
        [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
        public int Id { get; set; }
        public string Text { get; set; }

        public ICollection<QuestionWithOptions> QuestionWithOptions { get; set;}
     }
}