Adding a Many-to-Many Relationship

The comic book to series relationship is not the only relationship that 0:00 we need to define in our model. 0:04 Each comic book could also be associated with one or 0:06 more artists and each artist can be associated with one or more comic books. 0:09 This kind of relationship is known as a many to many relationship. 0:14 Let's add an artist entity to our model and 0:20 define the relationship between the comic book and artist entities. 0:22 Right click on the model's folder and select Add Class. 0:27 Name the class, artist and click Add. 0:32 Add the Public Access modifier and the following properties. 0:41 An int property named Id. 0:47 Like our other entities, this property will contain a unique value for 0:51 each artist in our system. 0:55 A string property named Name. 1:00 This is the artist's name. 1:05 Now that we have the artist class, 1:07 let's define the many to many relationship between the comic book and artist Indies. 1:10 While we're here we can start by adding a collection of comic books to our 1:15 artist class. 1:20 Public, Icollection of type comic book, 1:21 comic books and we'll add a default constructor so 1:25 that we can initialize the comic books collection property. 1:29 Public Artist ComicBooks = 1:38 new List of <comicbook>. 1:43 Now let's add a collection of artists 1:53 to the comic book entity class. 1:58 Public I collection of type artist ,artists and 2:02 a default constructor to initialize 2:09 the artist collection property public 2:15 comic book artists = new list of artist. 2:20 By adding the comic books navigation collection property to the artist entity 2:26 class in the artist navigation collection property to the comic book entity class, 2:30 we've defined a many to many relationship between the comic book and 2:35 artist entities. 2:40 Each comic book instance can be associated with one or more artist instances. 2:42 And each artist instance can be associated with one or more comic book instances. 2:46 With our new artist entity and artist to comic books many to many 2:52 relationship in place, we can update our test data to make use of these changes. 2:55 Let's start with adding a second series. 3:01 I'll copy and paste this code, 3:04 change the series variables to series one and series two, 3:07 and set the title of the second series to The Invincible Iron Man. 3:12 Then let's define some artist's, 3:25 var artist1 = new Artist, name = Stan Lee. 3:31 Var artist2 = new artist, 3:42 name = Steve Ditko. 3:48 And var artist 3, 3:55 = new artist, 4:00 name = Jack Kirby. 4:03 We need to assign our comic book entity objects to their own variables so 4:10 we can call the add method on the comic book's artist collection property. 4:14 Let's cut this comic book and the object instantiation code, 4:18 And paste it to the right of var comicBook 1. 4:26 Remember that we renamed a series variable to series 1. 4:31 So let's replace the reference to the old variable with a new variable. 4:35 Now let's add our first two artists, Stan Lee and Steve Ditko, 4:42 to this comic book comicBook1.Artists.Add(artist1);. 4:48 And comicBook1.Artists.Add(artist2);. 4:55 Now repeat that process for the second comic book. 5:02 Cut the comic book entity object instantiation code and 5:08 paste it to the right of var comicBook2, 5:15 rename the series variable to series1, and 5:19 add our first two artists to this comic book. 5:25 While we're at it let's add a third comic book, 5:40 var comicBook3 = new ComicBook. 5:45 I'll set the series property to our second series. 5:50 The issue number to one and publishedOn to DateTime.Today. 5:56 For this comic book let's add our first and third artists. 6:04 Let's fix our comic books db set property add method calls by passing in 6:18 the variables comicBook1 and comicBook2. 6:22 And don't forget that we added a third comic book. 6:29 So, we need to add a third call to the comic books db set add method. 6:31 Context.ComicBooks.add(comicBooks3). 6:38 Now that we've updated our test data. 6:43 Let's update our comic books query to include our artist data. 6:45 To do that we need to use an include method call, just like we did when 6:49 including the series data .include cb goes to cb.artists. 6:54 Let's update our output to include a list of each comic book's artist. 7:03 I'll use a link query to transform the collection 7:07 of artist entity objects to a collection of artistNames, 7:13 var artistNames = ComicBook.Artists.Select( 7:21 a => a.Name).ToList();. 7:27 Then I'll use the strings join method to transform the collection of 7:31 strings into a comma delimited list. 7:36 Var artistsDisplayext = string.join, 7:39 the first parameter is the string to use as 7:43 the separator that goes in between each value. 7:48 Let's use a comma followed by a space. 7:53 The second parameter is the collection of values to join. 7:58 Artist names, 8:03 now I'll write that value to the console using the console right line method. 8:04 Console WriteLine (artistsDisplayText). 8:12 With those changes in place, let's test our app. 8:18 And here's our three comic books along with their respective artists. 8:27 The Amazing Spider-Man number one, Stan Lee, Steve Ditko, 8:32 The Amazing Spider-Man number two. 8:36 Again with Stan Lee and Steve Ditko, and 8:38 the Invincible Iron Man number one with Stan Lee and Jack Kirby. 8:41 Now let's review the database that EF generated for 8:46 us using the SQL server opted explorer window. 8:49 There are two new tables artist comic books and artists. 8:58 The artist table is exactly what we'd expect it to be. 9:07 It contains columns for each of our artist entitiy class 9:11 properties ID and name. 9:16 The artist comicbooks table contains two columns artist ID and comic book ID. 9:21 Interestingly this table doesn't have a corresponding indie class in our model. 9:28 EF automatically added this table to the database 9:33 in order to store the artist to kind books many to many relationship data. 9:36 This table is known by a variety of names including junction, 9:41 join, linking, mapping, or bridge. 9:46 Let's review the tables data. 9:51 We can sort the data to make it easier to review. 9:55 Click the sort and filter dataset tool bar icon and 9:58 set the sort for both columns to ascending. 10:02 Then click OK. 10:10 Now we can see that artist ID 1 is associated with 10:13 comic book I.D.'s 1, 2 and 3. 10:18 And artist ID 2 is associated with comic book I.D.'s 1 and 2. 10:22 And artist ID 3 is associated with comic book ID 3. 10:28