Add Foreign Keys to same entity in EF Core 6

Skander 0
Viewed 39

I have an entity representing a File object to review

Review
{
int Id {get; set;};
string Level1 {get; set;}
string Level2 {get; set;}
string Level3 {get; set;}
}

With pre set and defined values list. Various Users have to pick a Review on the given File.

I need to link my object with those review such as

File:
{
int Id {get; set;};
<Some props>

Review MainReview {get; set;}
Review SecondaryReview {get; set;}
Review OtherReview {get; set;}
}

I need to link two entities as foreign Key. Adding 3 dependencies such as:

 [ForeignKey("MainReview")]
 public int MainReviewId { get; set; }
 public MainReview MainReview { get; set; }
 
 [ForeignKey("SecondaryReview")]
 public int SecondaryReviewId { get; set; }
 public SecondaryReview SecondaryReview { get; set; }
 
 [ForeignKey("OtherReview")]
 public int OtherReviewId { get; set; }
 public OtherReview OtherReview { get; set; }

and Adding lists into Review as:

public List<File> Files { get; set; }

This is never valid for EF migration. I’m using EF Microsoft.EntityFrameworkCore 6.0.7 (Project in core 6). Could you please check and Help.

Thanks.

foreign-keys .net-core entity-framework-core entity-framework c#
2 Answers

Let's start with a few changes in the models.

  1. In this case, there is a cycle and we need to make foreign keys nullable.
  2. Make properties public
    public class Review
    {
        public int Id { get; set; }
        public string Level1 { get; set; }
        public string Level2 { get; set; }
        public string Level3 { get; set; }
    
        public List<File>? FilesForMainReview { get; set; }
        public List<File>? FilesForSecondaryReview { get; set; }
        public List<File>? FilesForOtherReview { get; set; }
    }
    
    public class File
    {
        public int Id { get; set; }
    
    
        public int? MainReviewId { get; set; }
    
        public Review? MainReview { get; set; }
    
    
        public int? SecondaryReviewId { get; set; }
        public Review? SecondaryReview { get; set; }
    
    
        public int? OtherReviewId { get; set; }
        public Review? OtherReview { get; set; }
    }
  1. For these kind of relations we can use FluentAPI

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        base.OnModelCreating(modelBuilder);
    
        modelBuilder.Entity<Review>().HasMany(x => x.FilesForMainReview).WithOne(x => x.MainReview).HasForeignKey(x => x.MainReviewId).OnDelete(DeleteBehavior.Restrict);
        modelBuilder.Entity<Review>().HasMany(x => x.FilesForSecondaryReview).WithOne(x => x.SecondaryReview).HasForeignKey(x => x.SecondaryReviewId).OnDelete(DeleteBehavior.Restrict);
        modelBuilder.Entity<Review>().HasMany(x => x.FilesForOtherReview).WithOne(x => x.OtherReview).HasForeignKey(x => x.OtherReviewId).OnDelete(DeleteBehavior.Restrict);
    }

These are my sample data:

SET IDENTITY_INSERT [dbo].[Review] ON 
GO
INSERT [dbo].[Review] ([Id], [Level1], [Level2], [Level3]) VALUES (1, N'R1L1', N'R1L2', N'R1L3')
GO
INSERT [dbo].[Review] ([Id], [Level1], [Level2], [Level3]) VALUES (2, N'R2L1', N'R2L2', N'R2L3')
GO
INSERT [dbo].[Review] ([Id], [Level1], [Level2], [Level3]) VALUES (3, N'R3L1', N'R3L2', N'R3L3')
GO
SET IDENTITY_INSERT [dbo].[Review] OFF
GO

SET IDENTITY_INSERT [dbo].[File] ON 
GO
INSERT [dbo].[File] ([Id], [MainReviewId], [SecondaryReviewId], [OtherReviewId]) VALUES (2, 1, 2, 3)
GO
INSERT [dbo].[File] ([Id], [MainReviewId], [SecondaryReviewId], [OtherReviewId]) VALUES (5, 2, 1, 3)
GO
INSERT [dbo].[File] ([Id], [MainReviewId], [SecondaryReviewId], [OtherReviewId]) VALUES (6, 3, 2, 1)
GO
SET IDENTITY_INSERT [dbo].[File] OFF
GO

And in the end, this is the code and the result:

using (var db = new MyDbContext())
{
    var result = db.File.Include(x => x.MainReview).Include(x => x.SecondaryReview).Include(x => x.OtherReview).ToList();
}

enter image description here

Saeid Amini0

If I'm understanding your requirements. Each file can have 3 reviews? One for each "Level"? In which case I would change your schema to something like;

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

    public virtual ICollection<Review> Review { get; set; }
}
public enum ReviewLevel {
   Level1 = 1,
   Level2,
   Level3,
}
public class Review {
    public int Id { get; set; }
    public int FileId { get; set; }
    public ReviewLevel Level { get; set; }

    public virtual File File { get; set; }
}

With a unique index on FileId & Level to prevent duplicates.

Jeremy Lakeman0
Related