Entity Framework Core - mapping two joins to the same table

0
votes

I have two tables Clients and Jobs. A client can have a number of jobs. However, I have a second relationship on Jobs that should also refer back to Clients called WarrantyCompany. Basically a warranty company is a client as well. Well the client/job relationship is the primary relationship but I want to reference back to the client table a second time to get the warranty company details.

Here is the Client table:

using JobsLedger.DATA.Entities.Interfaces;
using JobsLedger.DATA.Interfaces;
using System;
using System.Collections.Generic;

namespace JobsLedger.DATA.Entities
{
#nullable enable
    public class Client : IEntityBase, IAuditedEntityBase
    {

        public Client()
        {
            Jobs = new List<Job>();

        }
        public int Id { get; set; }

        // One warranty company client to a job.
        public int? WarrantyCompanyId { get; set; }
        public Job? WarrantyCompany { get; set; }

        // One suburb to a client.
        public int? SuburbId { get; set; }
        public Suburb? Suburb { get; set; }

        // If its a warranty company then we simply link it one to one to the brand id.
        public Brand? Brand { get; set; }

        public virtual ICollection<Job> Jobs { get; set; } = default!;

        public virtual ICollection<Job> WarrantyCompanyJobs { get; set; } = default!;
    }
#nullable disable
}

I am referencing to the job table using:

// One warranty company client to a job.
public int? WarrantyCompanyId { get; set; }
public Job? WarrantyCompany { get; set; }

The Job Table follows:

using JobsLedger.DATA.Entities.Interfaces;
using JobsLedger.DATA.Interfaces;
using System.Collections.Generic;

namespace JobsLedger.DATA.Entities
{
    public class Job : IEntityBase, IAuditedEntityBase
    {
#nullable enable
        public Job()
        {
            Visits = new List<Visit>();
        }

        public int Id { get; set; }

        public int? WarrantyCompanyId { get; set; }

        public Client? WarrantyCompany { get; set; }

        public int ClientId { get; set; } = default!;

        public Client Client { get; set; } = default!;

    }
#nullable disable
}

In this table I reference back to the client table using:

public int? WarrantyCompanyId { get; set; }
public Client? WarrantyCompany { get; set; }

I am using Entity Framework Core Fluent API to create the relationship.

I decided to do both relationships from the client point of view:

// Two one to many relationships between client and job.
modelBuilder.Entity<Client>()
    .HasMany(t => t.Jobs)
    .WithOne(g => g.Client)
    .HasForeignKey(g => g.ClientId)
    .OnDelete(DeleteBehavior.Restrict);

// Same table but a client can also be a warranty agent.
modelBuilder.Entity<Client>()
    .HasMany(a => a.Jobs)
    .WithOne(b => b.WarrantyCompany)
    .HasForeignKey(b => b.WarrantyCompanyId);

I've tried to create a migration however I get the following error:

Cannot create a relationship between 'Client.Jobs' and 'Job.WarrantyCompany', because there already is a relationship between 'Client.Jobs' and 'Job.Client'. Navigation properties can only participate in a single relationship.

Which is self evident. I have also tried to create the relationship from the job table point of view:

modelBuilder.Entity<Job>()
    .HasOne(a => a.Client)
    .WithOne(b => b.WarrantyCompany)
    .HasForeignKey<Client>(b => b.WarrantyCompanyId);

I am finding I am having trouble visualizing the relationship when there are two back to the same client table.

Essentially, in a nutshell, I have a client who can have one to many jobs but for each of those jobs I might have an optional warranty company set where the warranty company is a client. Two relationships between client and job.

How can I have a primary relationship and also a secondary optional relationship between these two tables and is the secondary warranty company relationship a one to one or one to many?

2
c#entity-frameworkentity-framework-core
You are mapping the client Jobs navigation property for both relationships. You will need a new one, say WarrantedJobs? - Victor Ortuondo
Thanks for your comment. Wondering on how to do that.. - si2030

2 Answers

1
votes

The issue happens due to two relationships being mapped to the same Jobs collection within the Client class. Since there are actually two relationships they cannot reuse the same properties.

Your Client model already have a collection for the second relationship. You just need to fix the mapping. 

    // First client/job relationship
    modelBuilder.Entity<Client>()
        .HasMany(t => t.Jobs)
        .WithOne(g => g.Client)
        .HasForeignKey(g => g.ClientId)
        .OnDelete(DeleteBehavior.Restrict);

    // Second client/job relationship (warranty)
    modelBuilder.Entity<Client>()
        .HasMany(a => a.WarrantyCompanyJobs)
        .WithOne(b => b.WarrantyCompany)
        .HasForeignKey(b => b.WarrantyCompanyId);
1
votes

I have simplified table properties for readability.

Client Table:

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

    // One warranty company client to a job.
    public int? WarrantyCompanyId { get; set; }
    public Job? WarrantyCompany { get; set; }

    public ICollection<Job> Jobs { get; set; } = default!;

    public ICollection<Job> WarrantyCompanyJobs { get; set; } = default!;
}

And Job Table:

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

    public int? WarrantyCompanyId { get; set; }
    public Client? WarrantyCompany { get; set; }


    public int ClientId { get; set; } = default!;
    public Client Client { get; set; } = default!;

}

And for configuration, I have created a JobConfig class

public class JobConfig : IEntityTypeConfiguration<Job>
    {
        public void Configure(EntityTypeBuilder<Job> builder)
        {
            builder.HasOne(s => s.Client)
            .WithMany(g => g.Jobs)
            .HasForeignKey(f => f.ClientId);

            builder.HasOne(s => s.WarrantyCompany)
                .WithMany(g => g.WarrantyCompanyJobs)
                .HasForeignKey(f => f.WarrantyCompanyId);
        }
    }

And in Context class, suppose context class name is ProjectContext, I override OnModelCreating method.

public class ProjectContext: DbContext
{
    public ProjectContext()
    {

    }

    public Client Client { get; set; }
    public Job Job { get; set; }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.ApplyConfiguration(new JobConfig());
    }
 }

It will generate a table and relations like this.

enter image description here