this is pat, in today's video, I am going to
show you how to query an existing database using Entity Framework Core. Entity Framework allows
interacting with a database using Csharp object instead of SQL. There are two approaches when
working with Entity Framework. Database-first and code-first. In the code-first approach, you start
by creating classes that describe the database then Entity Framework generates a database based
on your classes. In the database-first approach, you start from an existing database, you
generate classes from your database schema. This tutorial is about database-first. Let's
get into it. I am going to start by showing you the database that we are going to interact with,
this database is located in my local SQL express and is called AdventureWorks LT 2019. This is
a database provided by Microsoft, it showcases the design of a database with SQL server, it
has a few tables. For simplicity we will use only the product table. Let's create a console
application in Visual Studio. I select the console application template, the project name will be
EF database first . I select the.net 5 as the target framework, once the project is generated
we will install the package needed to work with Entity Framework Core. Let's launch the package
manager console, we start with the NuGet package for Entity Framework Core, the package name starts
with Microsoft.EntityFramework Core and the last segment of the package name identify the provider.
I use SQL server so the provider is SqlServer. Now, we have to install Entity Framework Core
tools for the Nuget package manager console in Visual Studio, this package will allow
execution of commands on the database from the package manager, it is an alternative
to the .NET Core command-line interface. Both packages have been installed. Now
we are going to generate entity models and the DB context from the database, this
process is also called reverse engineering, to achieve this goal we will use the
scaffold-dbcontext command, the first argument to the command is the connection string to the
database, let's specify the connection string. The next argument to the command is the provider
name, this is the same as the provider's NuGet package name. Next, we need to specify which table
is going to be involved in the reverse engineering process, by default all tables are reverse
engineered, I am going to specify the product table. the next argument will allow me to set
a proper name for the DB context. and lastly, I am going to specify the output folder for the
generated files. now I can execute that command. Let's see what has been generated, the first
item is the DB context, you can see that this class inherits from the DB context this is
a requirement for Entity Framework Core, the DB context will allow to query the database.
The database connection string has been placed in the OnConfiguring method, in a real-world
scenario you don't do this, you need to store your connection string in a safe place. Let's take
a look at the product entity, this represents the product table. Let's make some operations on the
database, I create a method named ShowProducts I need an instance of the DB context I can use the property products
which is mapped to the table product in the database, the ToList
method will fetch all the data from the product table, we display only
the product name and the product price. Let's call this method in the main method if I run the project you can see that I am able
to fetch all the products. Now, we are going to change the size of the first product in the table,
now the size is 58, we are going to change it. We need the product id which is 680. so I
create a new method to update the product size. We are going to use an instance
of the DB context again. First, we are going to look
for product we need to modify, if we find the product in the database, we set
the size to 60. We display a message stating that the product has been updated, it's important to
call the SaveChanges on the DB context, this will propagate changes to the database. Let's call this
method in the main method, let's run the app, the message says that the product has been updated...
let's check into the database... the product size is still 58, Let's run the select method again...
If I check now, the size has changed to 60. if you don't like using the command line,
you can reverse engineer a database using a tool called EF Core power tool,
this is a Visual Studio extension. In order to demonstrate the usage ,
I am going to delete the DB folder EF core power tool is already
installed in my Visual Studio, now if I right-click on my project name,
I can see the EF Core Power tool menu, I can select the reverse engineer item, I need
to provide the connection string to my database. Because i have already tried the tool before,
the connection string is already set. If I click on the ok button I have another window that shows
up... I can select which table I want to generate, in my case I select the product table... in the
next dialog, I can set the DB context name... I can also set the path to the folder that will
hold the entities... I set that path to DB... I also set the path for the database context
to the same folder. I pluralize the generated object names to match the Csharp code conventions
in this case for the product table a products object will be created... I include the connection
string in the generated code. if I click on the ok button, the tool will generate the object
just like with the scaffold-Dbcontext command. Let's show the product again to make sure the app
is still working, as you can see I'm still able to query the database. That's it for this tutorial,
if you enjoy it, like the video and subscribe to the channel because it motivates me to make
more videos. Thanks for watching see you soon
