CODEWITHSUNDEEP
sqldatabasedatabase-designentity-framework-core
Jan Paolo Go
Jan Paolo Go

Reputation: 6522

EF Core Unique Constraint across multiple tables

How do I create/design the relationship and unique constraint:

A PROJECT has many SUBJOBS. A SUBJOB has many TASKS. A TASK has a Number which must be unique across a single PROJECT.

I tried the following for the relationship but I can't figure out how to enforce the unique constraint for Task.Number and Project.ProjectId:

public class ProjectContext : DbContext
{
    public DbSet<Project> Project { get; private set; }

    public DbSet<SubJob> SubJob { get; private set; }

    public DbSet<Task> Task { get; private set; }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Project>(b =>
        {
            b.HasKey(p => p.ProjectId);
            b.HasAlternateKey(p => p.Code);
        });

        modelBuilder.Entity<SubJob>(b =>
        {
            b.HasKey(s => s.SubJobId);
            b.HasOne(s => s.Project).WithMany(p => p.SubJobs).HasForeignKey(s => s.ProjectId);
            b.HasAlternateKey(s => new { s.ProjectId, s.Code });
        });

        modelBuilder.Entity<Task>(b =>
        {
            b.HasKey(l => l.TaskId);
            b.HasOne(l => l.SubJob).WithMany(s => s.Tasks).HasForeignKey(l => l.SubJobId);
        });
    }
}

public class Project
{
    public int ProjectId { get; private set; }

    public string Code { get; set; }

    public ICollection<SubJob> SubJobs { get; private set; } = new List<SubJob>();
}

public class SubJob
{
    public int SubJobId { get; private set; }

    public int ProjectId { get; private set; }

    public Project Project { get; set; }

    public string Code { get; set; }

    public ICollection<Task> Tasks { get; private set; } = new List<Task>();
}

public class Task
{
    public int TaskId { get; private set; }

    public int SubJobId { get; private set; }

    public SubJob SubJob { get; set; }

    public int Number { get; set; }
}

Upvotes: 0

Views: 1147

Answers (3)

shocks
shocks

Reputation: 53

One option to achieve this is creating a foreign key in Task class referencing Project and then create a unique index that forces the uniqueness.

Task class would have this extra property:

public int ProjectId { get; private set; }

And your DbContext would look like this (when using FluentAPI):

            modelBuilder.Entity<Task>()
                .HasOne<Project>()
                .WithMany()
                .HasForeignKey(t => t.ProjectId);
            modelBuilder.Entity<Task>()
                .HasIndex(t => new { t.Number., t.ProjectId})
                .IsUnique();

PS: Depending on your other relationships, you might need to change your deleting behavior to avoid cycles.

Upvotes: 0

Smit
Smit

Reputation: 2459

To define unique constraint on Tasks table, you would need to get value of ProjectId in Task class. The modified Task entity would look like this.

public class Task
{
    public int TaskId { get; private set; }
    public int SubJobId { get; private set; }
    public SubJob SubJob { get; set; }
    public int Number { get; set; }
    public int ProjectId { get; set; } // Added property
}

Since every Task has a SubJob and every SubJob has a Project, Task.ProjectId should be equal to Task.SubJob.ProjectId (which in turn takes value from SubJob.Project.ProjectId). To make sure this value is passed around we can define a relationship between Task & SubJob to use composite keys like following

modelBuilder.Entity<Task>(b =>
{
    b.HasKey(l => l.TaskId);
    b.HasOne(l => l.SubJob).WithMany(s => s.Tasks).HasForeignKey(l => new { l.SubJobId, l.ProjectId })
        .HasPrincipalKey(l => new { l.SubJobId, l.ProjectId });
    b.HasAlternateKey(t => new { t.Number, t.ProjectId });
});

Then we can define the unique constraint over Number & ProjectId using HasAlternateKey.

You could also use Composite Keys with ProjectId as being part but that could require to setup value generation for other part of PK.

Unless you really need unique constraint, consider using unique index. More info can be found here

Upvotes: 0

pjs
pjs

Reputation: 383

I see two possibilities. Use natural keys for the child classes, so Subjob might be {ProjectId, JobCode} and Task might be {ProjectId, JobCode, Number}. But if the {ProjectId, Number} must be unique, then that would be the PK and JobCode would be a non-null FK, but not part of the PK.

Or, if your database supports indexed views you could create a view with a unique index on {ProjectId, Number}.

Upvotes: 1

Related Questions