1
00:00:00,720 --> 00:00:03,340
Let's update the Program
class' main method

2
00:00:03,340 --> 00:00:06,290
to use our new comic book to
series entity relationship.

3
00:00:07,420 --> 00:00:12,050
First, we need to replace this reference
to the SeriesTitle property with our new

4
00:00:12,050 --> 00:00:13,136
Series property.

5
00:00:17,608 --> 00:00:23,025
For now, we can just instantiate
a Series entity object in line and

6
00:00:23,025 --> 00:00:26,131
set its title property, new Series,

7
00:00:29,730 --> 00:00:34,290
Title = "The Amazing Spider-Man".

8
00:00:34,290 --> 00:00:35,940
Now, let's update our query.

9
00:00:37,680 --> 00:00:40,950
When retrieving the comic book's data,
we need to be explicit and

10
00:00:40,950 --> 00:00:44,460
tell EF that we also want
to include the series data.

11
00:00:44,460 --> 00:00:49,360
If we don't do this, each comic book
in the Series property would be null.

12
00:00:49,360 --> 00:00:54,855
To retrieve the series data, we need
to make a call to the include method.

13
00:00:54,855 --> 00:00:58,481
.Include, and pass in the string for

14
00:00:58,481 --> 00:01:05,410
the path to the property that we want
to include, in this case, Series.

15
00:01:06,980 --> 00:01:12,165
This will work, but having to specify
the path as a string is less than ideal,

16
00:01:12,165 --> 00:01:15,700
it would be all too easy
to mistype the path.

17
00:01:15,700 --> 00:01:19,270
Luckily, there's another version
of the include method that we can

18
00:01:19,270 --> 00:01:23,060
use that accepts a lambda expression,
instead of a string.

19
00:01:23,060 --> 00:01:27,341
To get access to that method, we need
to add an additional using statement.

20
00:01:32,337 --> 00:01:36,697
Using System.Data.Entity.

21
00:01:38,768 --> 00:01:43,100
Now we can replace the string parameter
value with a lambda expression that

22
00:01:43,100 --> 00:01:45,386
specifies which property to include.

23
00:01:45,386 --> 00:01:49,922
cb goes to cb.Series.

24
00:01:51,330 --> 00:01:55,029
Notice that I named this parameter cb,
which are the initials for

25
00:01:55,029 --> 00:01:58,800
the comicBook class,
which is the parameter's type.

26
00:01:58,800 --> 00:02:03,000
We have one more change to make, we need
to replace the series title property

27
00:02:03,000 --> 00:02:07,010
reference in our for
each loop with the series title property.

28
00:02:07,010 --> 00:02:08,280
Let's test our changes.

29
00:02:10,590 --> 00:02:12,290
Okay, looks good so far.

30
00:02:15,200 --> 00:02:16,660
Let's try an experiment.

31
00:02:16,660 --> 00:02:21,621
What happens if we add
a second comic book?

32
00:02:21,621 --> 00:02:25,760
I'll copy and paste this code, so
that we're adding a second comic book.

33
00:02:28,320 --> 00:02:31,609
And I changed the IssueNumber to 2.

34
00:02:35,063 --> 00:02:39,483
To make it easier to see what is
happening with the data in our database,

35
00:02:39,483 --> 00:02:43,913
let's configure EF to drop and
create the database on every app start.

36
00:02:43,913 --> 00:02:47,662
We can accomplish this by changing
our database initializer to

37
00:02:47,662 --> 00:02:50,290
use the DropCreateDatabaseAlways class.

38
00:02:52,020 --> 00:02:55,940
Let's also add a display text property
to the ComicBook entity class

39
00:02:55,940 --> 00:02:59,630
that includes both the series title and
the Issue Number.

40
00:02:59,630 --> 00:03:04,671
That will make it easier to identify
comic books in the console output,

41
00:03:04,671 --> 00:03:12,920
public string DisplayText { get.

42
00:03:12,920 --> 00:03:17,940
We can safely add read only or get our
only properties to our entity class,

43
00:03:17,940 --> 00:03:23,020
as EF will ignore when generating the in
memory representation of the model

44
00:03:23,020 --> 00:03:26,270
any properties that don't define a setter.

45
00:03:26,270 --> 00:03:29,036
Let's use an interpolated string for
our return value.

46
00:03:29,036 --> 00:03:33,780
Interpolated strings are string
literals that begin with a dollar sign.

47
00:03:38,340 --> 00:03:44,130
Interpolated strings allow us to mix code
expressions along with literal text.

48
00:03:44,130 --> 00:03:48,382
Let's start with the Series.Title property
value within a set of curly braces.

49
00:03:52,673 --> 00:03:57,237
Then follow that with a number sign
in the IssueNumber property value,

50
00:03:57,237 --> 00:03:59,452
also within a set of curly braces.

51
00:04:02,130 --> 00:04:04,090
To prevent a null reference exception,

52
00:04:04,090 --> 00:04:09,020
if the series property is null,
we can include a null conditional operator

53
00:04:09,020 --> 00:04:12,330
just before attempting to
access the title property.

54
00:04:12,330 --> 00:04:16,700
Using this operator will cause the series
property to be checked if it's null.

55
00:04:16,700 --> 00:04:19,030
And if it is, return null.

56
00:04:19,030 --> 00:04:23,050
Otherwise, it will return
the value of the title property.

57
00:04:23,050 --> 00:04:27,435
Now, let's update the console write line
method call to use our new display text

58
00:04:27,435 --> 00:04:28,091
property.

59
00:04:36,958 --> 00:04:39,120
Press F5 to run the app again.

60
00:04:41,800 --> 00:04:44,498
And here's the display text for
our two comic books.

61
00:04:48,760 --> 00:04:51,296
Let's review the data in the database.

62
00:04:58,012 --> 00:05:00,695
Using the SQL Server Object Explorer
window,

63
00:05:00,695 --> 00:05:04,080
right click on the ComicBooks table and
select View Data.

64
00:05:05,270 --> 00:05:09,030
Right away, we can see that
the table contains two rows.

65
00:05:09,030 --> 00:05:15,489
The first row has an issue number column
value of 1 and the second row, 2.

66
00:05:15,489 --> 00:05:20,530
Here on the right, we can see a column
named Series_Id, which you might recall

67
00:05:20,530 --> 00:05:25,890
isn't explicitly defined as a property
under comic book entity class.

68
00:05:25,890 --> 00:05:29,580
Because we defined a one-to-many
relationship between comic books and

69
00:05:29,580 --> 00:05:33,590
series, EF is automatically
adding this column

70
00:05:33,590 --> 00:05:37,990
in order to store the Series_Id value for
each comic book.

71
00:05:37,990 --> 00:05:43,450
In relational database terms, this
column is known as a foreign key column.

72
00:05:43,450 --> 00:05:49,020
The values contained within the column
represent key values in a foreign table,

73
00:05:49,020 --> 00:05:51,750
in this case, the series table.

74
00:05:51,750 --> 00:05:54,430
Unfortunately, there is
a problem with our data.

75
00:05:55,500 --> 00:05:58,100
Instead of having just
one series record or

76
00:05:58,100 --> 00:06:02,680
row with the title The Amazing Spider-Man,
we have two.

77
00:06:02,680 --> 00:06:07,440
Ideally, we should only have a single
record with an Id of 1, and both of our

78
00:06:07,440 --> 00:06:13,320
comic book records would use that Id
value for their Series_Id column values.

79
00:06:13,320 --> 00:06:14,574
To resolve this issue,

80
00:06:14,574 --> 00:06:18,097
we need to make a simple change to
our program class' main method.

81
00:06:27,393 --> 00:06:32,206
Instead of instantiating two
series entity objects, here and

82
00:06:32,206 --> 00:06:36,943
here, we need to instantiate
the series entity object once.

83
00:06:39,139 --> 00:06:44,597
Var series = new Series,

84
00:06:44,597 --> 00:06:49,813
Title = "The Amazing,

85
00:06:52,900 --> 00:06:55,050
Spider-Man".

86
00:06:55,050 --> 00:06:59,301
Then set the comic book series'
properties to the Series variable.

87
00:07:10,215 --> 00:07:15,085
Just by sharing the same series entity
object between the two comic book entity

88
00:07:15,085 --> 00:07:18,923
objects, EF will now correctly
understand our intentions,

89
00:07:18,923 --> 00:07:22,500
that these two comic books
share the same series.

90
00:07:22,500 --> 00:07:25,334
Run the app again, so
that we can test our changes.

91
00:07:27,910 --> 00:07:30,530
Hm, the app is just hanging.

92
00:07:31,840 --> 00:07:33,930
And finally, here's the exception.

93
00:07:34,940 --> 00:07:39,821
An unhandled exception of type
System.Data.SqlClient.SqlException

94
00:07:39,821 --> 00:07:42,303
occurred in EntityFramework.dll.

95
00:07:42,303 --> 00:07:47,157
Additional information: Cannot drop
database ComicBookGallery because

96
00:07:47,157 --> 00:07:48,678
it is currently in use.

97
00:07:50,969 --> 00:07:55,141
This exception is occurring because
we left open the comic books

98
00:07:55,141 --> 00:07:57,190
in series data tabs.

99
00:07:57,190 --> 00:08:00,232
Let's close those tabs and
run the app again.

100
00:08:04,942 --> 00:08:07,370
Okay, here's our expected output.

101
00:08:11,228 --> 00:08:13,863
View the series table data again.

102
00:08:20,671 --> 00:08:24,715
And now we have a single record for
The Amazing Spider-Man series.

103
00:08:28,133 --> 00:08:33,926
And both comic books are now using
the same Series_Id column value of 1.