Relationship between 3 tables in EF Core 2 and SQL Server

Question

I have tables User, Document & Status. Need to have a relationship defined in EF Core 2.0 which can give me data like below:

Document               ReferredBy           AssignedTo          Status
DOC-0001(DocumentId)   USR-0001(UserId)     USR-0002(UserId)    STA-001(statusId)
DOC-0002(DocumentId)   USR-0002(UserId)     USR-0001(UserId)    STA-002(statusId)

Can someone help creating this? I am using SQL Server 2016.

Any help much appreciated.

Answer 1

The easiest way to get what you are after is to create those three tables in a test database as you want them then scaffold the database to your project and look at the output.

Context

public partial class DatabaseContext : DbContext
{
    public virtual DbSet<Document> Document { get; set; }
    public virtual DbSet<Status> Status { get; set; }
    public virtual DbSet<User> User { get; set; }

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        if (!optionsBuilder.IsConfigured)
        {
            optionsBuilder.UseSqlServer(@"<ConnectionString>;");
        }
    }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Document>(entity =>
        {
            entity.Property(e => e.DocumentId).ValueGeneratedNever();

            entity.Property(e => e.ReferredById).ValueGeneratedOnAdd();

            entity.HasOne(d => d.AssignedTo)
                .WithMany(p => p.DocumentAssignedTo)
                .HasForeignKey(d => d.AssignedToId)
                .OnDelete(DeleteBehavior.ClientSetNull)
                .HasConstraintName("FK_Document_User1");

            entity.HasOne(d => d.ReferredBy)
                .WithMany(p => p.DocumentReferredBy)
                .HasForeignKey(d => d.ReferredById)
                .OnDelete(DeleteBehavior.ClientSetNull)
                .HasConstraintName("FK_Document_User");

            entity.HasOne(d => d.Status)
                .WithMany(p => p.Document)
                .HasForeignKey(d => d.StatusId)
                .OnDelete(DeleteBehavior.ClientSetNull)
                .HasConstraintName("FK_Document_Status");
        });

        modelBuilder.Entity<Status>(entity =>
        {
            entity.Property(e => e.StatusName).IsRequired();
        });

        modelBuilder.Entity<User>(entity =>
        {
            entity.Property(e => e.UserName).IsRequired();
        });
    }
}

Document

public partial class Document
{
    public int DocumentId { get; set; }
    public int ReferredById { get; set; }
    public int AssignedToId { get; set; }
    public int StatusId { get; set; }

    public User AssignedTo { get; set; }
    public User ReferredBy { get; set; }
    public Status Status { get; set; }
}

Status

public partial class Status
{
    public Status()
    {
        Document = new HashSet<Document>();
    }

    public int StatusId { get; set; }
    public string StatusName { get; set; }

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

User

public partial class User
{
    public User()
    {
        DocumentAssignedTo = new HashSet<Document>();
        DocumentReferredBy = new HashSet<Document>();
    }

    public int UserId { get; set; }
    public string UserName { get; set; }

    public ICollection<Document> DocumentAssignedTo { get; set; }
    public ICollection<Document> DocumentReferredBy { get; set; }
}