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 -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 Company class including the AgencyType enum and the navigational properties:
public class Company { public Company() { Agencies = new HashSet<Company>(); Clients = new HashSet<Company>(); } public int CompanyId { get; set; } public string CompanyName { get; set; } public string City { get; set; } public AgencyType AgencyType { get; set; } public ICollection<Company> Agencies { get; set; } public ICollection<Company> Clients { get; set; } }
The navigational properties are simply collections of the Company entity named Agencies and Clients. The relationship will be represented in the database by a separate table named CompanyAgencies that holds just the keys for each side of the relationship. I have named one of the keys ClientId and the other 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 DbContext's OnModelCreating method:
protected override void OnModelCreating(DbModelBuilder modelBuilder) { modelBuilder.Entity<Company>() .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 EntityTypeConfiguration<T>:
public class CompanyMap : EntityTypeConfiguration<Company> { 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 OnModelCreating method:
protected override void OnModelCreating(DbModelBuilder modelBuilder) { modelBuilder.Configurations.Add(new CompanyMap()); }
To test this solution, I add some code to the 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 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:
<h1>@Model.CompanyName</h1> @if (Model.Clients.Any()) { <h3>Clients</h3> <ul> @foreach (var client in Model.Clients) { <li>@client.CompanyName</li> } </ul> }
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.
Learn about configuring Many To Many relationships in Entity Framework core,
