Many businesses use one or more companies to supply a variety of marketing services, such as a advertising, public relations, digital or direct marketing. Those companies will have many clients. Clients and agencies are the same entity -

Code:

Company

:
public class Company
{
public int CompanyId { get; set; }
public string CompanyName { get; set; }
public string City { get; set; }
}
I use an enumeration to determine whether a company is a client or whether it is an agency, and if so, what type:
public enum AgencyType
{
NotSet,
Advertising,
PR,
Digital,
FullService
}
The key to the solution is to set up the navigational properties correctly, and then to add some configuration that maps those properties to columns in a separate table that stores the relationships. . Here's the completed

Code:

Company

class including the

Code:

AgencyType

enum and the navigational properties:
public class Company
{
public Company()
{
Agencies = new HashSet();
Clients = new HashSet();
}
public int CompanyId { get; set; }
public string CompanyName { get; set; }
public string City { get; set; }
public AgencyType AgencyType { get; set; }
public ICollection Agencies { get; set; }
public ICollection Clients { get; set; }
}
The navigational properties are simply collections of the

Code:

Company

entity named

Code:

Agencies

and

Code:

Clients

. The relationship will be represented in the database by a separate table named

Code:

CompanyAgencies

that holds just the keys for each side of the relationship. I have named one of the keys

Code:

ClientId

and the other

Code:

AgencyId

so that the relationship is easy to understand. The mapping of the relationship can either be configured via the fluent configuration API in the

Code:

DbContext

's

Code:

OnModelCreating

method:
protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
modelBuilder.Entity()
.HasMany(c => c.Agencies)
.WithMany(c => c.Clients)
.Map(m =>
{
m.MapLeftKey("ClientId");
m.MapRightKey("AgencyId");
m.ToTable("CompanyAgencies");
});
}
or as a separate mapping class that implements

Code:

EntityTypeConfiguration<T>

:
public class CompanyMap : EntityTypeConfiguration
{
public CompanyMap()
{
HasMany(c => c.Agencies)
.WithMany(c => c.Clients)
.Map(m =>
{
m.MapLeftKey("ClientId");
m.MapRightKey("AgencyId");
m.ToTable("CompanyAgencies");
});
}
}
If you use the separate mapping class approach, you register it in the

Code:

OnModelCreating

method:
protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
modelBuilder.Configurations.Add(new CompanyMap());
}
To test this solution, I add some code to the

Code:

Seed

method that generates a bunch of companies, some named after colours representing clients, and others named after animals that represent agencies. The latter group have a specific

Code:

AgencyType

defined:
protected override void Seed(CompanyContext context)
{
context.Companies.AddOrUpdate(c => c.CompanyName,
new Company { CompanyName = "Black", City = "London" },
new Company { CompanyName = "White", City = "Rome" },
new Company { CompanyName = "Brown", City = "Brussels" },
new Company { CompanyName = "Green", City = "Berlin" },
new Company { CompanyName = "Red", City = "Lisbon" },
new Company { CompanyName = "Yellow", City = "Paris" },
new Company { CompanyName = "Orange", City = "Amsterdam" },
new Company { CompanyName = "Blue", City = "Madrid" },
new Company { CompanyName = "Cat", City = "New York", AgencyType = AgencyType.Advertising },
new Company { CompanyName = "Dog", City = "San Francisco", AgencyType = AgencyType.Digital },
new Company { CompanyName = "Fish", City = "Boston", AgencyType = AgencyType.PR }
);

context.SaveChanges();

var adAgency = context.Companies.Single(c => c.AgencyType == AgencyType.Advertising);
var adClients = context.Companies.Where(c => c.City.StartsWith("B") && c.AgencyType == AgencyType.NotSet).ToList();
adAgency.Clients = adClients;

var digitalAgency = context.Companies.Single(c => c.AgencyType == AgencyType.Digital);
var digiClients = context.Companies.Where(c => c.City.StartsWith("L") && c.AgencyType == AgencyType.NotSet).ToList();
digitalAgency.Clients = digiClients;

var prAgency = context.Companies.Single(c => c.AgencyType == AgencyType.PR);
var client = context.Companies.Single(c => c.CompanyName == "Black");
prAgency.Clients.Add(client);
}
The next line of code illustrates how to retrieve the advertising agency and all its clients:
var adAgency = context.Companies.Include(c => c.Clients).First(c => c.AgencyType == AgencyType.Advertising);
If you passed this as a model to a view, you can display the details in the following manner:

@Model.CompanyName

@if (Model.Clients.Any())
{

Clients

@foreach (var client in Model.Clients)
{

• @client.CompanyName

}

}

Summary

The are quite a few articles and blog entries showing how to manage many-to-many relationships using the Entity Framework (some of them on my site!), but there isn't very much about managing this kind of relationship when only one table is involved. This article has now addressed that and shown how to accomplish it.