How to many-to-many relation with the same class with Entity-framework

Question

I would like to create many-to-many relation with .NET MVC EF. I have Client model (it could be employer or employee or colleague etc., company or person). So any of the client can have employees or employer. I am not sure how to set this relation in EF.

My models (parts of db context):

public partial class Client
{
    public long Id { get; set; }
    public string Name { get; set; }
    public virtual ICollection<ClientRelation> TargetClientRelations { get; set; }
    public virtual ICollection<ClientRelation> SourceClientRelations { get; set; }
}

public enum ClientRelationType
{
    Employer_Employee        
}

public class ClientRelation
{
    public long Id { get; set; }
    public virtual Client Client { get; set; }
    public virtual Client TargetClient { get; set; }
    public ClientRelationType ClientRelationType { get; set; }
}

---

The problem is EF creates this relation table. I gues I should create some mapping..

CREATE TABLE [dbo].[ClientRelations](
    [Id] [bigint] IDENTITY(1,1) NOT NULL,
    [Client_Id] [bigint] NULL,
    [TargetClient_Id] [bigint] NULL,
    [Client_Id1] [bigint] NULL,
    [Client_Id2] [bigint] NULL,
    [ClientRelationType] [int] NOT NULL,
 CONSTRAINT [PK_dbo.ClientRelations] PRIMARY KEY CLUSTERED 
(
    [Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF,     ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[ClientRelations] ADD  DEFAULT ((0)) FOR     [ClientRelationType]
GO

ALTER TABLE [dbo].[ClientRelations]  WITH CHECK ADD  CONSTRAINT     [FK_dbo.ClientRelations_dbo.Clients_Client_Id] FOREIGN KEY([Client_Id])
REFERENCES [dbo].[Clients] ([Id])
GO

ALTER TABLE [dbo].[ClientRelations] CHECK CONSTRAINT [FK_dbo.ClientRelations_dbo.Clients_Client_Id]
GO

ALTER TABLE [dbo].[ClientRelations]  WITH CHECK ADD  CONSTRAINT     [FK_dbo.ClientRelations_dbo.Clients_Client_Id1] FOREIGN KEY([Client_Id1])
REFERENCES [dbo].[Clients] ([Id])
GO

ALTER TABLE [dbo].[ClientRelations] CHECK CONSTRAINT     [FK_dbo.ClientRelations_dbo.Clients_Client_Id1]
GO

ALTER TABLE [dbo].[ClientRelations]  WITH CHECK ADD  CONSTRAINT     [FK_dbo.ClientRelations_dbo.Clients_Client_Id2] FOREIGN KEY([Client_Id2])
REFERENCES [dbo].[Clients] ([Id])
GO

ALTER TABLE [dbo].[ClientRelations] CHECK CONSTRAINT     [FK_dbo.ClientRelations_dbo.Clients_Client_Id2]
GO

ALTER TABLE [dbo].[ClientRelations]  WITH CHECK ADD  CONSTRAINT     [FK_dbo.ClientRelations_dbo.Clients_TargetClient_Id] FOREIGN     KEY([TargetClient_Id])
REFERENCES [dbo].[Clients] ([Id])
GO

ALTER TABLE [dbo].[ClientRelations] CHECK CONSTRAINT     [FK_dbo.ClientRelations_dbo.Clients_TargetClient_Id]
GO

Answer 1

You need to add some additional configuration to your model to specify which property is related with.

Using Data Anotations you will need to use InverseProperty attribute:

public class ClientRelation
{
    public long Id { get; set; }

    [InverseProperty("SourceClientRelations")]
    public virtual Client Client { get; set; }
    [InverseProperty("TargetClientRelations")]
    public virtual Client TargetClient { get; set; }
    public ClientRelationType ClientRelationType { get; set; }
}

If you decide go for Fluent Api then your configurations would be:

modelBuilder.Entity<ClientRelation>()
  .HasOptional(l => l.TargetClient)
  .WithMany(p => p.TargetClientRelations);

modelBuilder.Entity< ClientRelation>()
  .HasOptional(l => l.Client)
  .WithMany(p => p.SourceClientRelations);