0

Here is the simplified version of my problem. Think of a survey application:

  • There are questions,
  • A question have many options, an option can only belong to a question
  • A question should have one category, a category can belong to many questions
  • A question may have many tags, a tag can belong to many questions

Besides, I would like to define dependencies between a questions and options of other questions such that if a user has voted 'No' option for 'Do you have a car?' question, he/she will not be asked for 'What is the brand of your car?'.

Both auto generated database schema created by Code First and auto generated code created by database first are not satisfying. Below are codes and db schemas generated for both methods.

EF can handle Question - Tag relationship as expected but it cannot handle Dependency relationship between Question and Option (as I understood) because Question already has its own options.

In terms of EF, what would be the ideal code/db design for this case?

Code First - Code

public class Question
{
    public long Id { get; set; }
    public string Text { get; set; }
    public virtual ICollection<Tag> Tags { get; set; }
    public virtual ICollection<Option> Options { get; set; }
    public virtual ICollection<Option> DependencyOptions { get; set; }

    [ForeignKey("CategoryId")]
    public virtual Category Category { get; set; }
    public long CategoryId { get; set; }
}

public class Option
{
    public long Id { get; set; }
    public string Text { get; set; }

    public virtual ICollection<Question> DependencyQuestions { get; set; }

    [ForeignKey("QuestionId")]
    public virtual Question Question { get; set; }
    public long QuestionId { get; set; }
}

public class Tag
{
    public long Id { get; set; }
    public string Name { get; set; }
    public virtual ICollection<Question> Questions { get; set; }
}
public class Category
{
    public long Id { get; set; }
    public string Name { get; set; }

    public virtual ICollection<Question> Questions { get; set; }
}

Code First - Tables

Code First - Tables

Db First - Tables

Db First - Tables

Db First - Code

public class Question
{    
    public long Id { get; set; }
    public string Text { get; set; }
    public long CategoryId { get; set; }

    public virtual Category Category { get; set; }
    public virtual ICollection<Option> Options { get; set; }
    public virtual ICollection<Option> Options1 { get; set; }
    public virtual ICollection<Tag> Tags { get; set; }
}

public class Option
{    
    public long Id { get; set; }
    public string Text { get; set; }
    public long QuestionId { get; set; }

    public virtual Question Question { get; set; }
    public virtual ICollection<Question> Questions { get; set; }
}

public class Tag
{    
    public long Id { get; set; }
    public string Name { get; set; }

    public virtual ICollection<Question> Questions { get; set; }
}

public class Category
{    
    public long Id { get; set; }
    public string Name { get; set; }

    public virtual ICollection<Question> Questions { get; set; }
}
Mehmet Ataş
  • 11,081
  • 6
  • 51
  • 78

2 Answers2

0

I'm not sure if I understood it right but I think you're going to need (and already have) a many-to-many on Option/Question - plus, you also have a one-to-many (owner) relation in between them.

I don't know enough to discuss whether that is the best solution - but taking your word for having both the 'dependency' relationship (and the other one)...

...for that you'd need to 'fine tune' your relationships and your index tables. EF/CF creates the default ones in the background, what you need I think is to create the relations in fluent configuration - and add extra columns yourself.

And I would generally recommend doing your own configuration in the code - vs attributes / default one - as in any complex scenarios that gives you more options and control over it, less errors etc. In my case, I just like to know which tables and columns are created for me.

And here is a specific sample code (I removed other tables unnecessary, just Q/O)...

public class QuestionContext : DbContext
{
    public DbSet<Question> Questions { get; set; }
    public DbSet<Option> Options { get; set; }
    public DbSet<QuestionOption> QuestionOptions { get; set; }
    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        modelBuilder.Entity<QuestionOption>()
            .HasKey(i => new { i.OptionID, i.QuestionID });

        modelBuilder.Entity<QuestionOption>()
            .HasRequired(i => i.Opiton)
            .WithMany(u => u.DependencyOptions)
            .HasForeignKey(i => i.OptionID)
            .WillCascadeOnDelete(false);

        modelBuilder.Entity<QuestionOption>()
            .HasRequired(i => i.Question)
            .WithMany(u => u.DependencyOptions)
            .HasForeignKey(i => i.QuestionID)
            .WillCascadeOnDelete(false);

        modelBuilder.Entity<Option>()
            .HasRequired(i => i.Question)
            .WithMany(u => u.Options)
            .HasForeignKey(i => i.QuestionId)
            .WillCascadeOnDelete(false);
    }
}
public class Question
{
    public long Id { get; set; }
    public string Text { get; set; }
    public virtual ICollection<Option> Options { get; set; }
    public virtual ICollection<QuestionOption> DependencyOptions { get; set; }
}
public class Option
{
    public long Id { get; set; }
    public string Text { get; set; }
    // [ForeignKey("QuestionId")]
    public virtual Question Question { get; set; }
    public long QuestionId { get; set; }
    public virtual ICollection<QuestionOption> DependencyOptions { get; set; }
}
public class QuestionOption
{
    public long OptionID { get; set; }
    public Option Opiton { get; set; }
    public long QuestionID { get; set; }
    public Question Question { get; set; }
    public int DependencyType { get; set; }
    public string DependencyNote { get; set; }
    public bool Active { get; set; }
    public bool UseEtc { get; set; }
}

With the following migration... 

        CreateTable(
            "dbo.Questions",
            c => new
                {
                    Id = c.Long(nullable: false, identity: true),
                    Text = c.String(maxLength: 4000),
                })
            .PrimaryKey(t => t.Id);

        CreateTable(
            "dbo.Options",
            c => new
                {
                    Id = c.Long(nullable: false, identity: true),
                    Text = c.String(maxLength: 4000),
                    QuestionId = c.Long(nullable: false),
                })
            .PrimaryKey(t => t.Id)
            .ForeignKey("dbo.Questions", t => t.QuestionId)
            .Index(t => t.QuestionId);

        CreateTable(
            "dbo.QuestionOptions",
            c => new
                {
                    OptionID = c.Long(nullable: false),
                    QuestionID = c.Long(nullable: false),
                    DependencyType = c.Int(nullable: false),
                    DependencyNote = c.String(maxLength: 4000),
                    Active = c.Boolean(nullable: false),
                    UseEtc = c.Boolean(nullable: false),
                })
            .PrimaryKey(t => new { t.OptionID, t.QuestionID })
            .ForeignKey("dbo.Options", t => t.OptionID)
            .ForeignKey("dbo.Questions", t => t.QuestionID)
            .Index(t => t.OptionID)
            .Index(t => t.QuestionID);

You have two separate relationships defined (one one-to-many and other m-to-m), side by side.

Within QuestionOption table you can now manually specify all you need to add for your dependency (that's your DependencyOption - I just thought this naming clarifies it more). So you'd have something like Question(A) -> allows Option(B) - but given your logic you may need to add some more.

It looks like you'd need to establish relationships in between Question, Question, Option - so 3 indexes etc. Given the code above you can do that with just a simple extension if needed. 

modelBuilder.Entity<QuestionOption>()
    .HasKey(i => new { i.OptionID, i.QuestionLeftID, i.QuestionRightID });
modelBuilder.Entity<QuestionOption>()
    .HasRequired(i => i.Opiton)
    .WithMany(u => u.DependencyOptions)
    .HasForeignKey(i => i.OptionID)
    .WillCascadeOnDelete(false);
modelBuilder.Entity<QuestionOption>()
    .HasRequired(i => i.QuestionLeft)
    .WithMany(u => u.DependencyOptionsLeft)
    .HasForeignKey(i => i.QuestionLeftID)
    .WillCascadeOnDelete(false);
modelBuilder.Entity<QuestionOption>()
    .HasRequired(i => i.QuestionRight)
    .WithMany(u => u.DependencyOptionsRight)
    .HasForeignKey(i => i.QuestionRightID)
    .WillCascadeOnDelete(false);
modelBuilder.Entity<Option>()
    .HasRequired(i => i.Question)
    .WithMany(u => u.Options)
    .HasForeignKey(i => i.QuestionId)
    .WillCascadeOnDelete(false);
public class Question
{
    public long Id { get; set; }
    public string Text { get; set; }
    public virtual ICollection<Option> Options { get; set; }
    public virtual ICollection<QuestionOption> DependencyOptionsLeft { get; set; }
    public virtual ICollection<QuestionOption> DependencyOptionsRight { get; set; }
}
public class QuestionOption
{
    public long QuestionLeftID { get; set; }
    public Question QuestionLeft { get; set; }
    public long QuestionRightID { get; set; }
    public Question QuestionRight { get; set; }
    public long OptionID { get; set; }
    public Option Opiton { get; set; }
    public int DependencyType { get; set; }
    public string DependencyNote { get; set; }
    public bool Active { get; set; }
    public bool AllowForbid { get; set; }
}

For more complex scenarios (with many-to-many and manually defining relations in fluent code - which I recommend) - take a look at these detailed examples I made a while ago - it has most of the mappings you may need.

Many to many (join table) relationship with the same entity with codefirst or fluent API?

Code First Fluent API and Navigation Properties in a Join Table

EF code-first many-to-many with additional data

...that's if you need any fast pointers - let me know with questions - and some more details

Community
  • 1
  • 1
NSGaga-mostly-inactive
  • 14,052
  • 3
  • 41
  • 51
0

My understanding is you want a subsequent question to be dependent on the answer to a prior question...

My suggestion would be to to have a relationship table between questions and options called something like RequiredAnswer. This relationship will signify that a user must have answered a question or questions with one or all (up to you for implementation) of the related options (your diagrams don't include an answers table). Then it's up to the code implementation on how you want to use this relationship.

Moho
  • 15,457
  • 1
  • 30
  • 31