Defining a Many-to-Many Relationship with an Explicit Bridge Entity

Our model allows us to associate artists with comic books, but 0:00 our current approach doesn't allow us to specify the role that the artist had. 0:04 To do that, we need to move from an implicit bridge table to an explicit one 0:09 by adding an entity class to our model for the bridge table. 0:14 By defining an explicit bridge entity class, we can include additional 0:18 properties beyond the properties that are needed to define the relationship. 0:21 Bridge entities or tables that include extra properties or 0:26 columns are sometimes said to have payload. 0:30 Let's start with adding the role entity class. 0:34 Right click on the Model's folder, and select Add, Class. 0:36 Name the class Role and click Add. 0:42 Add the Public Access modifier in the following properties, and 0:47 int property named ID. 0:52 Again this property will contain a unique value for each role in our system. 0:55 A string property named, Name. 1:01 This is the name of the role. 1:06 Now let's add the bridge entity class. 1:09 I'll name the class ComicBookArtist. 1:15 At the public access modifier, and the following properties, 1:24 an int property named Id. 1:29 This property will contain a unique value for each comic book to artist relationship 1:32 in our system An int property named ComicBookId. 1:37 This is a foreign key property to the comic book that we're adding artists to. 1:44 An int property named ArtistId. 1:50 This is a foreign key property to the artist 1:55 that we're adding to the comic book. 1:57 And an int property named RoleId. 2:00 This is a foreign key property to the role that the artist had for this comic book. 2:05 Now let's add our navigation properties, 2:10 a ComicBook property named ComicBook. 2:14 And an ArtistProperty named Artist. 2:20 And lastly a Role property named Role. 2:26 Now we need to update the comic book and 2:34 artist entity classes to use our new bridge entity class. 2:37 Starting with the comic book class, 2:44 change both references to the artist class to ComicBookArtist. 2:47 And do the same for the artist class. 3:03 Now let's update our test data. 3:17 Just below our artists, let's add 3:24 a couple of roles, var role1 = new Role. 3:28 Name = "Script". 3:33 Var role2 = new Role. 3:38 Name = "Pencils". 3:43 To help with adding artist to our comic books, 3:51 let's add a method to the comic book entity class. 3:54 This will be a public method that doesn't return a value. 4:04 So a return type of void. 4:07 And I'll name the method AddArtist. 4:10 It'll accept two parameters, an Artist parameter of type artist 4:16 and a Role parameter of type role. 4:22 Now let's call the add method on the artist navigation collection property and 4:26 pass in an instance of the comic book artist class. 4:30 Artists.Add(new ComicBookArtist(). 4:33 I'll set the Artists and Role properties to their respective parameter values. 4:41 Artist = artist. 4:45 And Role = role. 4:49 Notice that we only had to explicitly set the artist and role navigation properties. 4:53 Because we're adding the comic book artist entity to a navigation property 4:58 on the comic book entity, EF is able to set the comic book navigation 5:03 property value for us implicitly.With that helper method in place, 5:07 we can easily replace our comicBook Artists.Add method calls with Add artist, 5:12 passing in the appropriate role for each artist. 5:17 AddArtist, and then. 5:24 Role1. 5:28 AddArtist. 5:34 And role2. 5:38 And again. 5:44 AddArtist. 5:47 role1. 5:51 AddArtist and role2, and 5:57 then the last comic book. 6:02 AddArtist. 6:07 Role1. 6:11 And lastly AddArtist. 6:13 Role2. 6:19 Now, we need to update our comicBooks query to not only include our artist data, 6:25 but to include the associated artist and role data for each bridge entity instance. 6:30 To do that, we can use the select operator on the artist collection in order to 6:35 specify the path to the child property that we want to include on the comic book 6:40 artist entity. 6:45 So, cb => cb.artist.select. 6:46 a => to, a.artist. 6:51 And a second call to the include method in order to add the second child property, 6:58 the row property. 7:04 .Include(cb => 7:05 cb.Artists.Select(a => a.Role. 7:09 Notice that we didn't need a third call to the include method in order to populate 7:17 the artist's collection. 7:22 Every navigation property in the path to the child navigation property that 7:23 we're populating will also be populated. 7:27 To complete our changes, let's update the collection of artist name strings 7:31 to be a collection of artist role names. 7:36 We can do that by modifying the select operator to return an interpolated string. 7:39 So, .Select (a 7:48 => $"" quotes 7:53 {a.Artist.name}- 7:58 {a.Role.Name}. 8:05 Let's rename the artist names variable by putting the cursor on the variable, and 8:14 pressing Ctrl+R, Ctrl+R. 8:19 Then change the name to artistRoleNames and press Enter. 8:22 Let's also rename artistsDisplayText to artistRolesDisplayText. 8:30 Let's test our app And 8:36 here's our three comic books along with their respective artists and roles. 8:41 Let's review the database. 8:48 If you already have the tables folder open be sure to refresh the list of tables. 8:53 The artist table remains unchanged, and 9:05 the roles table is what we'd expect with ID and name columns. 9:09 The comic book artist table contains four columns. 9:19 The ID primary key column and three foreign key columns. 9:23 ComicBookId, ArtistId and RoleId. 9:27 Let's review the tables data. 9:32 We can see the ComicBookId 1 is associated with ArtistId 1 and 9:38 2, and RoleId 1 and 2, respectively. 9:44 The same for ComicBookId 2. 9:48 And ComicBookId 3 is associated with ArtistIds 1 and 9:54 3, and RoleIds 1 and 2 respectively. 9:59 Having the flexibility of an implicit or explicit bridge entity when working with 10:05 many-to-many relationships, allows you to start with the implicit approach and 10:10 switch to explicitly defining the bridge entity when you need to. 10:14 After the break, 10:19 we'll see how we can use data annotations to refine the generated database. 10:19