Instructor: At this point, 2 2
we want to add database support to our Web API, 3 3
so that all our data is persisted 4 4
beyond the lifetime of the application. 5 5
So, whenever the request to create a game is received 6 6
in our Web API, we want to create that game in the database, 7 7
and when the client requests a list of games, 8 8
we want to query that list from the database. 9 9
However, there's a problem. 10 10
The database does not speak the same language 11 11
as your ASP.NET Core Web API. 12 12
Your Web API is coded in C#, 13 13
but a SQL Server database 14 14
only understands the Transact SQL language. 15 15
That means that to fulfill the request to retrieve games, 16 16
your C# code needs to translate the Web API request 17 17
into a carefully crafted T-SQL query, 18 18
and then send that query to SQL Server. 19 19
SQL Server executes the query 20 20
and then your C# code has to read back any resulting rows 21 21
and translate them into a corresponding Web API response. 22 22
This presents a few problems. 23 23
As a C# developer, 24 24
you now need to learn completely new language, 25 25
T-SQL, in order to craft the required queries. 26 26
And you need to learn it well 27 27
to ensure you get good performance out of it. 28 28
You'll need to write a lot of additional data-access code, 29 29
whose only purpose is to translate things 30 30
from C# to T-SQL and vice versa. 31 31
And of course, deal with unexpected errors. 32 32
Talking about errors, this approach is error prone, 33 33
since it's very easy to make mistakes 34 34
when mapping things from one side to the other. 35 35
And, you need to manually keep your C# models in sync 36 36
with the corresponding database tables, 37 37
which can be quite challenging 38 38
This is where Object-Relational Mapping techniques, 39 39
or O/RM, can make a big difference. 40 40
But what is O/RM? 41 41
Well, going back to our Spotify example, 42 42
imagine your application has been created 43 43
using an object-oriented paradigm, 44 44
and therefore it includes objects 45 45
to represent songs, artists and playlists. 46 46
And, if you are working with a relational database server, 47 47
like SQL Server, there's a good chance your database 48 48
will have corresponding tables for each of those objects. 49 49
Now, instead of having to write custom code 50 50
to map these objects to tables each time you need to send 51 51
or receive data to or from the database, 52 52
you can setup a map between them 53 53
so that your program can keep working with objects 54 54
while another component, an object relational mapper, 55 55
takes care of transforming objects to tables and vice-versa. 56 56
So, in essence, object-relational mapping is a technique 57 57
for converting data between a relational database 58 58
and an object-oriented program, 59 59
and, as you can imagine, 60 60
it brings many benefits to application developers. 61 61
And fortunately, ASP.NET Core 62 62
includes a great ORM framework called Entity Framework Core. 63 63
So, what is Entity Framework Core? 64 64
It is a lightweight, extensible, open source 65 65
and cross-platform object-relational mapper for .NET. 66 66
Entity Framework Core will sit between your Web API 67 67
and your SQL Server database. 68 68
Besides mapping your .NET objects to SQL Server tables, 69 69
it will translate your C# data access code 70 70
into Transact SQL statements 71 71
that the database server can understand, 72 72
and it will also translate any resulting data 73 73
from the database into objects 74 74
that your Web API can easily manage. 75 75
Using Entity Framework Code in your .NET applications 76 76
brings in multiple benefits. 77 77
There's no need for you to learn a new language. 78 78
You can perform all data access tasks 79 79
using your familiar C# language. 80 80
The actual data-access code 81 81
that you need to write is minimal, 82 82
since Entity Framework takes care of most of it. 83 83
In fact, you can use Language Integrated Query or LINQ 84 84
to perform most of your database queries. 85 85
There's tooling available to keep your C# models 86 86
in sync with your database tables, 87 87
so you don't need to do this manually anymore. 88 88
Entity Framework can keep track of the changes made 89 89
to your C# object instances at run time, 90 90
so it knows what changes to send to the database 91 91
when it's time to persist the data. 92 92
Also, Entity Framework Core 93 93
supports multiple database providers, 94 94
so you can use your same C# models 95 95
with additional relational databases if needed. 96 96
Let's now see how to use Entity Framework Core 97 97
to add SQL Server support to our Game Store Web API.