Multiple relationships to the same table in EF7(Core)
In case someone will run into this question. Here is more elegant solution
public class Question
{
public Guid Id { get; private set; }
public IReadOnlyList<Variant> Variants { get; private set; }
public Guid CorrectVariantId { get; private set; }
public Guid? AnsweredVariantId { get; private set; }
public bool IsAnswerCorrect => CorrectVariantId == AnsweredVariantId;
public bool IsAnswered => AnsweredVariantId != null;
}
public class Variant
{
public Guid Id { get; private set; }
public Guid QuestionId { get; private set; }
public string HiddenUserLogin { get; private set; }
public User HiddenUser { get; private set; }
}
// mapping
mb.Entity<Question>()
.HasMany(q => q.Variants)
.WithOne()
.HasForeignKey(nameof(Variant.QuestionId))
.IsRequired()
.OnDelete(DeleteBehavior.Cascade);
mb.Entity<Question>()
.HasOne(typeof(Variant))
.WithOne()
.HasForeignKey<Question>(nameof(Question.AnsweredVariantId))
.IsRequired(false)
.OnDelete(DeleteBehavior.Restrict);
// EF creates Unique Index for nullable fields
mb.Entity<Question>()
.HasIndex(q => q.AnsweredVariantId)
.IsUnique(false);
// create index instead of FK hence the cyclic dependency between Question and Variant
mb.Entity<Question>()
.HasIndex(q => q.CorrectVariantId)
.IsUnique();
The two examples given already got me part of the way there, but I wanted a collection and a single item of the same object type and therefore the same table on my model like in the original question. I've tried to provide a simple example of this below that works for .NET Core 2.2:
public class ParentModel
{
public int Id { get; set; }
// Id for single instance navigation property
public int? ChildModelId { get; set; }
// Single instance navigation property to ChildTable, identified by ChildModelId property as foreign key
public virtual ChildModel ChildModel { get; set; }
// Collection navigation property to ChildTable with identified by ParentId property
public virtual ICollection<ChildModel> ChildModels { get; set; }
}
public class ChildModel
{
public int Id { get; set; }
// Id for ParentModel property back to ParentTable
public int ParentId { get; set; }
// Single instance navigation property to ParentTable, identified by ParentId property as foreign key
public virtual ParentModel ParentModel { get; set; }
}
public class ApplicationDbContext : IdentityDbContext
{
public ApplicationDbContext(DbContextOptions<ApplicationDbContext> options)
: base(options)
{
}
protected override void OnModelCreating(ModelBuilder builder)
{
base.OnModelCreating(builder);
builder.Entity<ParentModel>()
.ToTable("ParentTable");
// Configure collection of ChildModels (ParentTable to ChildTable/one-to-many relationship)
builder.Entity<ParentModel>()
.HasMany(t => t.ChildModels)
.WithOne(t => t.ParentModel)
.HasForeignKey(t => t.ParentId)
.IsRequired()
.OnDelete(DeleteBehavior.Cascade);
builder.Entity<ChildModel>()
.ToTable("ChildTable");
// Configure single ChildModel navigation property on ParentModel (one-to-one relationship)
builder.Entity<ParentModel>()
.HasOne(t => t.ChildModel)
.WithOne()
.HasForeignKey(typeof(ParentModel), nameof(ParentModel.ChildModelId))
.IsRequired(false)
.OnDelete(DeleteBehavior.Restrict);
}
}
The key to avoiding the Navigation properties can only participate in a single relationship.
error is to configure the navigation property back to the parent table only once. We configure this on for the ChildModels
collection on the ParentTable using .WithOne(t => t.ParentModel)
. We then don't bother configuring the other side of the relationship for the subsequent relationships by calling .WithOne()
empty, because if we did configure it again (eg .WithOne(t => t.ParentModel)
) it would error.
Also the virtual
modifiers on the navigation properties are to allow for lazy loading.
That was a bug/undesired behaviour in RC1 and it has been fixed.
You should create another property, say SecondQuestion for the other relationship.
public class Question
{
public string Id { get; set; } = Guid.NewGuid().ToString();
public List<Variant> Variants { get; set; }
public string CorrectVariantId { get; set; }
public Variant CorrectVariant { get; set; }
}
public class Variant
{
public string Id { get; set; } = Guid.NewGuid().ToString();
public string QuestionId { get; set; }
public Question Question { get; set; }
public Question SecondQuestion { get; set; }
}
Your DbContext:
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
modelBuilder.Entity<Question>()
.HasOne(q => q.CorrectVariant)
.WithOne(v => v.SecondQuestion)
.HasForeignKey<Question>(q => q.CorrectVariantId);
modelBuilder.Entity<Variant>()
.HasOne(v => v.Question)
.WithMany(a => a.Variants).HasForeignKey(x => x.QuestionId).OnDelete(DeleteBehavior.SetNull);
base.OnModelCreating(modelBuilder);
}
How to use it:
using (var myDb = new MyDbContext())
{
var variantFirst = new Variant();
var variantSecond = new Variant();
var question = new Question();
variantFirst.Question = question;
variantSecond.SecondQuestion = question;
myDb.Variants.Add(variantFirst);
myDb.Variants.Add(variantSecond);
myDb.SaveChanges();
}