Entity Framework associative table mapping

Question

Abstracting some details I have these classes:

public class Department: Entitie
{
    public string Description { get; private set; }
    public DateTime CreateDate { get; private set; }
    public virtual ICollection<Service> Services {get; set;}
}

public class Service: Entitie
{
    public string Description { get; set; }
    public Guid DepartmentId { get; set; }
    public virtual Department Department { get; set; }
}

public class DepartmentServiceRequest: Entitie
{
    public Guid DepartmentId { get; set; }
    public virtual Department Department { get; set; }

    public virtual ICollection<Service> Services { get; set; }
}

The class DepartmentServiceRequest is just one of my many attempts to make it work.

A Department lends several Services. A department can request one or more services from another department, and this needs to be mapped in advance. That is, it has the services it provides and the services it can order. The goal is not register a Service, is map what types he can register.

In the bank the result is this:

I don’t understand why he’s taking the Id of DepartmentServiceRequest to the Service.

It is possible via mapping to inform that it should generate this associative table only with the Ids of Department and Service? This seems to me the most logical, thinking in a relational way.

Answer 1

As I said, the association is incorrect. Some information is missing from the associative table:

public class DepartmentServiceRequest: Entitie
{
    [Key]
    public Guid DepartmentServiceRequestId { get; set }
    [Index("IUQ_DepartmentServiceRequest_DepartmentId_ServiceId", IsUnique = true, Order = 1)]
    public Guid DepartmentId { get; set; }
    [Index("IUQ_DepartmentServiceRequest_DepartmentId_ServiceId", IsUnique = true, Order = 2)]
    public Guid ServiceId { get; set; }

    public virtual Department Department { get; set; }    
    public virtual Service Service { get; set; }
}

The reason he’s carrying the key to Service is that you yourself indicated this in this statement:

public virtual ICollection<Service> Services { get; set; }

This is incorrect an associative scope. With this annotation, you are stating that a Request has several services. Therefore, that each Service is connected to one, and only one Request, what is wrong.

The Entity Framework automatically tries to fix this for you, and inserting the extra key is correct behavior. What is not correct is the modeling. In the other answer I gave I had already stated this. I’m just typing with other words.

It does not cost to repeat: for the behavior to be complete, change also Department and Service to accept the associative link:

public class Department: Entitie
{
    public string Description { get; private set; }
    public DateTime CreateDate { get; private set; }

    public virtual ICollection<Service> Services {get; set;}
    public virtual ICollection<DepartmentServiceRequest> DepartmentServiceRequests {get; set;}
}

public class Service: Entitie
{
    public string Description { get; set; }
    public Guid DepartmentId { get; set; }

    public virtual Department Department { get; set; }
    public virtual ICollection<DepartmentServiceRequest> DepartmentServiceRequests {get; set;}
}

[Index], introduced in this form from the Entity Framework 6.1.0, ensures the uniqueness of the associative record. Additional validations may be required in the application to avoid strange key duplicity errors for the user.