Introduction to Entity Framework Core 7 - Free Course!

hello my name is Felipe avilang and in this video we are going to see an introduction to Entity framework course 7. we are going to see how to download it and install it we are also going to see how to model a database from some c-shar classes also we're going to see how to read update insert and delete data from a database using c-sharp code and also we are going to learn what transactions are and in the end we are going to publish our web API with Entity framework or to Azure to see how easy it is to have a database in Azure working with Entity framework core for this video I'm going to assume that you already have installed either Visual Studio or Visual Studio code in my case I will be using visual studio 2022 also I will be using SQL Server though in your case you could use either SQL Server local DB or whatever or database engine that is super supported by Entity framework or finally if you want a complete course where we will learn Entity framework code from scratch please buy my uremic course today I will leave a link with a discount in the description of this video alright so let's get started Entity framework core is a library which we can use to access databases in technical terms Entity framework core is what is known as an orm orm stands for object relational mapper and orm basically allows us to represent the tables of a database in classes that is if we have a table of genres in a database then in our c-sharp application we can have a genre class then through our genre class we can represent records from the genres table Entity framework core is multi-platform it can run on Windows Linux and Macos and the different workout can also work with different databases such as SQL Server sqlite postgresql MySQL Oracle among others and while the main focus of this video is going to be SQL Server the vast majority of the lessons we learn here can be used with Entity framework core for other database engines like postgres when should we use Entity framework core these types of questions are very difficult to answer because each project has a unique context that makes it special which means that it needs to be carefully examined to find out which tools are suitable for it we typically use Entity framework core because it makes us more productive that is it allows us to develop applications more quick AirSpeed is also a good Factor since with each new version of any definable core it gets faster the cross platform support is also useful because you can write a code once and run it in different environments the first reason not to use Entity framework or is if you must use a technology or any different work or is not supported the second reason not to use Entity framework core is if it doesn't support the database you want to work with another reason may be again the issue of a speed even if Entity framework core is very fast it will never be faster than using pure queries for example with store procedures since Entity framework core represents an abstraction layer which has a cost in terms of execution time finally Entity framework core was not very good with bulk operations but that has changed in any different way core 7 where we can easily and efficiently do bulk operations let's start by installing the Entity framework correctly with this tool we can execute Entity framework core commands it is recommended to install this tool because there are scenarios in which it is mandatory to have it such as when we deploy to Azure and we want to have the migrations to be applied in our Azure SQL database we will see that at the end of this video so let's install this tool let's go to entity Network core tools reference.neck or CLI and let's come down here and you are going to see that we have this command that we can use to install the Entity framework or CLI of course you need to already have the S7 installed on your machine so I'm going to assume that you already have the ne7 and also that you have either Visual Studio or Visual Studio code installed and also in our case an instance of SQL Server so I copy this I will open a terminal for that I can press window key R and in here I will write CMD and I will get this terminal and I will paste this code here I can press enter and of course you are going to see that I already have the tool installed but in your case just to make sure that you have the latest version of the tool you can come here and say.net Tool update let me copy this let me paste that here just so that we can update the tool and you are going to see that in my case now I have version 7.0.2 excellent now let me close this and let's go to visual studio let's come here to create a new project we will do this tutorial using web API we are going to use an aspiro net core web API template now whatever we learn here in this video you are going to be able to apply it into any kind of.net project like a console application WPF application Blazer MVC Etc in our case because it will be easy for me to show you many examples I'm going to use a web API template so let me click on here and click on next I'll say introduction to EF core Eng next I'll use the S7 I'll use controllers please keep an enveloping API support checked and I will click on create let's configure entities our core in our application for that we have to install two nougat packages so let's go to the solution Explorer let me right click on the project manage nugget packages let's go to browse and the first package that we have to install is the package 4 SQL Server so as you can see here we have Microsoft Entity framework course SQL Server as I said at the beginning of this video we can use Entity framework core for many different database engines for example if I wanted to I can say here EF core I can say here Entity framework core and I can look for this one that says entity frameworkcore dot postgres as you can see we also have packages for postgres in our case we don't want to use postgres but I'm just showing you that you can definitely use different versions of the Entity framework or package that allows you to work with different database engines but again we're going to use SQL Server so let me put this again here Microsoft 20 different work or SQL server and let me install this package let me click on accept great now I have to install a second package this package depends on what tool you are using in my case since I am using visual studio I have to install Microsoft Entity framework or tools again this is only for visual studio if you are using visual studio code or whatever other IDE or text editor instead of installing this package you have to install another one that is Entity framework or dot Design This one Microsoft entity for Network Core Design This is if you are using visual studio code or another tool in my case since I am using visual studio I have to install Microsoft entity for Network core tools so that is the one that I'm going to install let me click on accept after that we are going to create a DB context that DB context is the central piece of Entity framework core this is where we configure everything for example that is where we are going to configure the tables that we're going to create from certain classes so let's do that let me close this let me create that DB contest class at class let me say application DB context this class is going to inherit from DB context Ctrl Dot to bring the Microsoft for Entity framework core namespace controller again because I want to generate this Constructor that has DB context options we are going to learn what that is in just a moment for now I just want you to see that this is the class that I like to call the DB context and this is where we are going to put the main configuration of Entity framework or what tables we are going to create in our database what is the configuration that those tables are going to have Etc now as you can see here there is nothing that says that we're going to use SQL Server we don't even have a connection string here that is what these DB context options is all about the idea of these DB contest options is that it is going to contain those kind of configurations so what we're going to do is that we are going to go to the program class because it is from here that we're going to configure Entity framework core it is from here that we are going to say that in the different core is going to connect to a SQL Server database so let me say here build other services at DB context I'm going to pass here my application DB contest class that we just created options and I'm going to say options use SQL Server here I am saying that we're going to be using SQL Server now all that were missing is to pass a connection string a connection string is a string that has the details of how to connect to our database so what we're going to do is that we're going to go to the solution Explorer let's go to here to upsetting the development.json and I want to say here connection strings make sure that you write it correctly otherwise you're going to get errors when you execute the application so let me say default connection here and in here I will put my connection string a connection string can have several values one of them is server which is the server in which your SQL Server instance is located in my case my SQL Server instance has the same name as my computer so that is why I can say dot here but if that is not the case for you you can always open SQL Server management studio and in here you are going to get server name and this is the name of my server so I could copy this and I can come here and I can paste that here if I want to but again in my case that is not necessary because the name of my machine coincides with the name of my SQL Server instance so I can just write that here and I'm good to go after that I need to say what is the name of the database that I'm going to use in my case it is going to be EF core or should I say introduction to E EF core Eng DB say my column here Integrated Security how are we going to log in to our SQL Server instance well for this I'll say true because I want to use my windows credentials to log in to SQL Server if in your case you want to use SQL authentication then you have to put here the username and password finally I have to say trust server certificate this is because we are in a development environment and I want to relax whatever security rules x is for our application this is not going to be used in production this is only for development this is the development file which means that in production you are not going to be doing this in production you are going to have a real certificate in your database server so this is good enough now I have to copy this default connection and I have to come to the program class and in here I will say name the name of the connection string that I want to pass to Entity framework core is going to be default connection and let me put this in another line so that you can visualize everything better and with this we have configured Entity framework core now let's create our first entity we call an entity a class that represents a table in our database since our project is going to be about movies we are going to be creating entities related to this concept such as genres actors movies among others we will begin modeling our database with the genre entity so let me create here a folder at folder I'll call it entities this is where our entities will be located right click add class genre and in here we're only going to have two properties the first one is going to be ID and it is going to be of type int and the second one is going to be name the name of the genre such as action drama comedy and so on right now this is just a simple class this is not an entity in order to make it an entity we have to go back to the application DB context and we have to come here and we have to put a property a DB set property of type genre and the name of the property is going to be genres and let me click here Ctrl dot to bring the name space of entities and now genre is an entity because here we are configuring that we're going to create a new table in order ways using the genre class as its model which means that we're going to have two columns an ID column and a name column and the name of this table is going to be genres why well because here we set genres although there are other ways to configure the name of the table a really simple way of doing this is just putting here the name of the table that you want in your database we are now going to generate our database with the genres table for that we need to create a migration a migration allows us to express in code the changes that are going to be made in our database migrations are classes that will be created when we run a command then the migration will serve as a history of the changes that our database has undergone over time let's create our first Migration by the way the commas in visual studio and Entity framework correctly are different so for those of you you who are following this tutorial using visual studio code I'm going to write on a pop-up the equivalent command for Entity framework or clean so let's go to the package manager console and if you don't have it here you can always come to tools nugget package manager package manager console and I'm going to say add migration this is the command for adding a new migration here in visual studio and now I have to write the name of the migration which in my case is going to be initial again as you can see I have here in a pop-up the equivalent command for Entity framework or clean so let me press enter and this is going to create a new class in a migrations folder which we're going to see in just a moment here is that class but before we examine its contents we are going to go to the solution Explorer and we're going to see that here we have migrations this is the migrations folder and in here you can see that we have two files and the first one is is the migration that we just created the second one is just some configurations for Entity framework core but we're just going to focus on the migration as you can see here we have two methods up and down the up method is the one that is going to run when we want to apply this migration in the database as you can see here what we have here is a create table command so this is going to create a table what is the name of the table genres what are The Columns of this table well they are ID a name and what are the characteristics of these columns well for ID it is going to be of type int it is not going to be nullable and also it is going to be an identity and its initial value is going to be 1 and it is going to increment in a one by one manner the second column is going to be name it is going to be of Type n Bar Car Max and it is not going to be nullable and also as you can see here we're going to have have a primary key of name PK genres and that primary key is going to be the ID column also we have a down method this down method is for when you want to remove a migration besides adding a migration you can remove it and what this is going to do is that it is going to reverse whatever happened here in the up method in our case we have here a drop table command which is going to delete a table and what is the table well the general stable now let me apply those changes in the database we still don't have any database yet what we did was to create a migration that Expresses in code whatever we want to do in our database but we still haven't applied the migration that is what we're going to do next so let's go back to the package manager console and let's say here update database don't worry even if in our case we don't have a database yet entity worker is going to make make sure to create one for us and with this we have our database with our genres table we can go to SQL Server let me connect to this SQL Server instance and we're going to see here in here that we have introduction to EF core engdb and if we expand here we are going to see that we have genres we have our genres table which has two columns ID and name and not only that but we also have EF migrations history what is this well if we right click on it and we select top 1000 rows we are going to see that we have one record and this record is the migration that we just created the idea here is that Entity framework core keeps track of which migrations we have applied in our database and it does that by adding those migrations into this EF migrations history table you can safely ignore it but I just wanted to show you that it exists now you may have a question how it is that this ID column was configured as a primary key as you can see here it is a primary key and as we saw here indeed the primary key of this table it is the ID column why is that who configured that and also the name field is in Barca Max who configured that how can I change it what if I want in Barca 150 well for that we have to talk about configurations in Entity framework core in Entity framework core there are three ways of making configurations by convention by data annotations and by using the fluent API the reason why and let's go back to the genre class the reason why ID is a primary key is that by convention when a field is named ID it is said to be a primary key so conventions are all about not having to write configuration code yourself if I want to have this as a parametric key all I have to do is to name it ID an entity work is going to take care of the rest if we want to be explicit when doing this configuration we can use data annotations or the following API with data annotations we use attributes for example to indicate that this is going to be a primary key I can say here key control dot to bring this namespace and now this is going to be marked as the primary key of the genres table the advantage of doing it like this is that we can use whatever name we want for the property for example if I don't want to name it ID but let's say identifier this is outside of the convention but with this configuration I was able to say that this is going to be the primary key another way of Performing this same configuration is by using the fluent API the fluent API are methods that we can use to configure Entity framework core properties and relationships in one place outside of the entity so let me delete this and let me go back to the application they be contest because this is the place where we can configure our entities and relationships by using the fluent API for that we have to say overwrite or model creating please please please leave this here do not delete this so let me just come down here and let me say model builder model builder is this parameter that we have here model builder entity I want to work with a genre entity what do I want to do well I want to say that the genre entity has key has a primary key that is the identifier column and that's it this is the same that we did before with a key attribute but in this way we do it outside of the genre entity however this was just an example of showing you the three types of configurations that we have in Entity framework core in my case whenever possible I prefer to use configuration by convention so let's go back here and let me say ID so now let's configure our name field so that it only accept 150 characters because remember that right now it is an nbarkar Max for that we have to use either data annotations or the fluent API however I want to mention that out of the three configuration types the following API is the most powerful one this is the most powerful one because it has all of the configuration options available in second place we have data annotations like the key attribute that we had here before which have a fair number of configuration options but not all of them finally we have configuration by convention which is the one that has the fewest options look for example that with conventions we cannot configure the name field so that it only accept 150 characters so here we can see that indeed there are configurations that we cannot do in all configuration styles to configure this name field to only accept 150 characters we can use their annotations specifically we can use the string length attribute so let me say here maximum length 150 but we're not finished yet because what we need to do now is to create a new migration to indicate that we want to modify our database but before that let's learn how to do this configuration by using the fluent API so let me comment this out and let's go back to the application DB contest and now I want to say again model builder entity I want to work with a genre entity and I'm going to say property G name I want to edit or configure the name property and I want to say has max length of 150 this is the same thing that we did back in the genre class it is the same it has the same effect you can use whichever way you prefer in my case I will use the fluent API way now because we want to make a change to our database we have to create a new migration so let's go back to package manager console and let's say add migration genre name enter and let's see that here we have a warning that warning is normal because before we used to have nbarkar Max and now we're going to have nbarkar 150 which means that we could lose data now in our case we don't have any data in our table so we can safely ignore this warning let's go back here because I want to show you that in this migration we again have a op method that has a alter column command as you can see here by the way we don't have to repeat that we want to create a genres table that is already indicated in this first migration so this second migration doesn't have to repeat that it only have to work with the Delta with what we want to change in our case we want to alter a column what column well the name column of the genres table and the new data type is going to be 150 characters I mean nbarkar 115 and the old type was nbarkar Max now we can push the changes to our database so let's go back here update database enter and we can go back to SQL Server management studio and you can see here that we have anybody Car Max but if I refresh now you can see that we have n Barca 150. we are not limited to having a single entity we can have several let's create the actor entity let's go back here let's go to the solution Explorer and let's go to entities I want to create a new entity let's create a new class actor and in here we're going to say ID name decimal Fortune how much money does the actor have and also daytime date of birth we want the name field to be of Type n Barca 150 and also I want to indicate the Precision that we're going to have for this decimal and also I want the data type of the SQL Server column to be date and not date time so so let's do that for that let me close here a couple of tabs that I have opened so that we have more space and let's go back let me save let's go back to the application DB contest because we are going to be using again the fluent API so let me say here model builder entity we already know how to do this configuration I can say property a a name has maxed length 150 now let me configure the data type of date of birth so let me say model builder entity actor property date of birth has column type the data type of the SQL Server column is going to be date because I don't need to know the time that the actor was born only the date also we can say model builder entity actor property a a Fortune has precision and here we can indicate the Precision and the scale so what is this about Let Me Explain you in just a moment let me say that I want to have five and two this means that we want to have a number of five digits and out of those five two are going to be decimal values this means that for example one value could be one two three dot four five there are five digits and two of them are decimal this is what we're saying with A5 here and A2 here now in our case this was just an example I want to have 18 digits because some actors have a lot of money and only two decimal places now let's also add a DB set for actors so let me say DB set actor actors now let's create another entity which is going to be the movie entity so let me go to resolution Explorer right click on entities add Class movie again let me create some properties ID title bull in theaters and finally daytime release date we can also configure these properties let's go back to the application DB context and again let's go back to our model creating model builder Entity movie property m m dot title and let's say that we also wanted to have 150 characters and I want to do the same that I did here on the data type of the daytime column but here I want to have movie and here I want to have release date release date and date here finally we're going to add a final entity which is going to be a comment which will represent a comment that a user makes about a movie so let's come here entities at class comment and let me put here ID I want to have a string but it's going to be nullable because not always the user has to write a comment about the movie because they could only say if they recommend it or not so like a thumbs up or a thumbs down alright so now we can go back here to the application DB contest and let's write a DB set for both movie movies and comment and comments now we can add a migration let me add a migration add migration several entities and let's see that indeed we're going to be creating an actors table a common stable and also a movie stable before we continue I want you to see that we're not limited to using the default Entity framework or conventions we can configure our own conventions one way to do this is by using the configure convention method on rdb context for example let's say let's go back to our application that we content let's say that we're tired of having to do this we're tired of having to write 150 characters for a lot of our strings in our entities so I want to centralize that in one place so what I want to do is to create a new convention so let's come here and let's say overwrite configure conventions we can delete this and now I can say configuration Builder properties a string so this is going to be a configuration for Strings and I'm going to say half max length of 150 now I can remove these I can remove this I can remove this because this is now the convention so I don't need to explicitly configure this if we want to configure a string with a different value we can do it for example remember that we have a Content string field in the comment entity let's say that I want to have an n Bar car 500 for that field so that what I can do is to say here model builder entity comment property content has max length 500 now with this I can add a new migration at migration I'll call add content comment 500 enter and let's see that now here I have anbarker 504 the content column if we go back to the previous migration to the several entities migration we can see that we indeed had nbarkar Max but now because we have a new convention that says 150 and we said that we want to make a specific configuration for the content column of the Common Table we can see that we have n barcar 500. now let me close this and let's see that here in this on model creating we have configurations that belong to different entities although it is not a lot of code this may grow in the future an alternative that we can use is to put these configurations in their own files segmented by entity let's see that let me create here in entities a new folder I'll call it com configurations I'll right click and I will add a new class I'll call it genre config as its name implies here we are going to put the configuration for the genre entity so let me say here I entity type configuration control dot to bring this namespace genre Ctrl dot to implement this interface and in here what we're going to do is that we are going to go over here and we're going to look for whatever configuration we have for our genre entity now of course in our case we don't have anything because we are using here convention based configuration because this is ID and not identifier anymore and here we also have another convention but just for completeness I'm going to put this here now let me do the same for the other entities let's go with actor now so let's say here class actor config enter I entity type configuration actor control dot Implement interface and now let's go back to the application DB context and let me cut this from here and let me paste this here and I will say Builder because I want to change this for Builder like this and also here now this is the thing for the movie Let's Make a movie config file movie config again I entity type configuration movie control dot Implement interface and let's bring here the configuration for the movie entity let me paste this here and let me say here Builder finally let's do the same for the comment entity so configurations add class comment config identity type configuration comment Implement interface and let's go back to the application DB contest and let me cut this from here and let me paste that here and also Builder and let me paste this here now we must configure our application DB contest so that it reads all of these configurations that we put here in this configuration folder what we are going to do is that we're just going to read all of the configurations that we have in this assembly so what we're going to do is that we're going to go back to the application DB contest let me remove this empty space and in here I will say model builder dot apply configurations from assembly and I will pass the executing assembly so I will say assembly control load system reflection get executing assembly with this the configurations will get applied and now we have all of that configuration in those classes and we have this application DB context cling we have several scatter entities it is time for us to configure the relationships between them we will start with a one-to-many relationship between movie and comment this relationship is a one-to-many relationship because each movie will have a collection of comments while each comment will correspond to a single movie to configure relations in Entity framework or we can use configuration by Convention of course we can also use the floor API but we're not going to do that in this video what we will do is configure navigation properties between our entities a navigation property allows us to obtain the related data of an entity in a simple way thus if I want to load a movie along with its respective comments I can do it using one of the techniques that we will see later we'll start by adding a navigation property in comment so let me go to the comment entity and in here I will add our movie navigation property and also I need to add a movie ID let me say here movie ID this movie ID is going to contain the ID of the movie to which this comment belongs to and now I will do the same for movie I can go to movie but since a movie has a collection of comments I will not use a single comment but a collection of comments specifically a hash set of comments we used a hash set because it is more performant than other Alternatives like list now let me say here new new hash set in this way we have configured a one-to-many relationship between movie and comments I can add a migration let me say add migration movies and comments enter and we're going to see that indeed here we have that we're adding a new column to the comments table and that is the movie ID column but not only that we're creating an index in the same column so that it is easy for us to look for comments of a movie and also the most important thing is that we are adding a foreign key so we're linking together those two tables the command stable and the movies table and of course the principal table in this relationship is the movie stable which is the one that has the principal column ID and we have on delete Cascade this means that if we delete a movie that deletion is going to Cascade over our comments table and it is going to delete those comments that belong to that movie another type of relationship is the many-to-many relationship we are going to establish it between movie and genre that is each movie will correspond to several genres and each genre will correspond to several movies just like in real life for example The Avengers movie has several genres such as action and science fiction while the action genre corresponds to several movies such as Avengers Spider-Man or Batman so it is a many-to-many relationship in Entity framework or 7 there are two ways to configure this relationship those with Escape navigation and those without the escape the idea of the navigation Escape is that the entities are directly related to each other and not through an intermediate entity let's look at an example of both ways we will start with the configuration with skip navigation here is where we will place a navigation property from genres to movies and from movies to genres so let me go to genre let me close some of the tabs that I have opened and let's go to genre and here as we said a genre corresponds to several movies so I can use a collection like hash set so let me say here hash set movie movies and new hashtag movie alright so now let's go to movie and I'll do the same I'll say hash set of genre because a movie have several genres so genres are now let's say new hash set with this we can add a migration so let me say package manager console at migration movies and genres enter now let's explore this migration and let's see that we are creating a new table what is this we are creating a genre movie table and this genre movie table is going to contain the ID of the genre and the ID of the movie so if for example we have an Avengers movie and it has two genres like science fiction and action then we're going to create two records in this table one for saying that we have the Avengers movie with the science fiction genre and another record for saying that we have the Avengers movie again but with the action genre so as you can see we have an intermediate table a many-to-many relationship requires an intermediate table to work and besides this as you can see here we have that this table is going to have a composite primary key which means that the primary key is going to be composed of several columns in this case the genres ID column and the movies ID column but again what I want to point out here is that we have an intermediate table as I was saying we have another way to configure many-to-many relationships and it is without escaping the intermediate entity the idea is that as we saw in this migration in order to set up a many-to-many relationship we need an intermediate table now we have the option of creating an entity that represents the table we can have for example in this case we could have an entity that represents this genre movie table and that is what we're going to do but with another many-to-many relationship I want to set up a many-to-many relationship between movie and actor her it is a many-to-many relationship because of course a movie can have several actors and an actor can act in several movies but also I want to save some custom data in that intermediate table like for example I want to store the name of the character and also I want to store the order in which the actors will show up why because when we list the actors of a movie we always want to have the protagonist to be the first one to appear and for us to guarantee that we need to store the order in which we want our actors to appear and we can use the intermediate table a column in that intermediate table to do that so that is why because we want to have extreme control over that intermediate table I want to have an intermediate entity so I do not want to escape that intermediate entity so what we're going to do is that we're going to create that entity let's go to entities at class movie actor and we're going to say here prop movie ID prob actor ID string character and also order also I want to have some navigation properties like movie and actor actor all right so let's come here let's forgive the null also here and now let me go to the application TV context and let me add a new DB set for that intermediate table so let me say movie actor and movies actors and now as we said before an intermediate table in a many-to-many relationship has a composite primary key so we have to configure that so what we're going to do is that we're going to create a new configuration file for our movie actor entity so let's come here configurations at class movie actor config I entity type configuration movie actor control load Implement interface and let me say here Builder has key movie actor new movie actor dot actor ID movie ID semicolon here now that we have this configure I can go to the movie entity because I want to configure the navigation property that is going to go not from movie to actor but from movie to movie actor so let me say here hash set because it is a collection movie actor movies actors new this and the same goes for actor I want to go to the actor entity and I want to add this navigation property that goes from actor 2 movie actor now what we're doing that again is that we are not escaping this intermediate entity anymore now let me insert a new migration at migration movies and actors enter and we're going to see that indeed we're creating this intermediate table but we have more control over the columns for example we have the character column and the order column but everything else is the same for example we have our composite primary key been defined here so let's go back to the package manager console and let's say update database enter so that we apply those changes into our database now let's talk about inserting data we already have a database created and configured now we are going to insert data we will start by inserting data into our simplest table the genres table the first thing that we're going to do is that we're going to create a new controller in a web API and in an external net core application in general a controller is a class that allows us to process HTTP requests it doesn't matter if you don't know how to use a web API just think of a controller as a class that will have some method that will be executed when the user interacts with our web API so let me say controller base API controller this is just some ceremony code that we need to have so our controller Works genres and now let's get to work on the controller the first thing that I need is to have an instance of the application DB contest class because it is through this class that I will be able to insert read update and delete data from our tables so I will put a Constructor here and I will say application division contest because I am able to use the dependency injection system to access an instance of the application DB contest that is because if we go back to the program class let's go to the program class remember that we did a DV contest application the context which means that we are configuring our application DB contest to be able to be used use independency injection so let me close this and let's go back here to the genres controller and let's say HTTP post public Asin task action result which is the typical return type from an action and an action is a method that gets executed in response to an HTTP request to our web API so let me say post genre control dot entities genre and now let's add a genre into our genres table for that we're going to use our application DB contest so I will say context dot add genre this doesn't add the genre the record into the table what this does is to mark this object with a status of added which means that next time we save changes with Entity framework core then it is that the record will get inserted into the table and how do we save changes well by executing the save changes AC method so let's say await context save changes async we use safe changes async because this is an i o operation and it is good practice to use asynchronous programming when we do IO operations i o operations simply means that our system is communicating with another system in our case our web application is communicating with a database and finally we're going to say return okay with this we can test let's press Ctrl F5 to run our application and we have here our application this is Swagger this is a page that allows us to interact with the actions of each of our controllers here we have genres and we have this post that we created API slash genres this is the route I can click on here I can say try it out we are going to see that we have a lot of text here but we can ignore it for now we're going to fix this in a few minutes right now I just want to delete all of these make sure to delete this last comma and also I want to delete this ID from here and I only want to leave this name I deleted the ID because the ID is going to be automatically created for me remember that we said before that that primary key of ID is going to have a numeric value that is going to start from 1 and is going to increment one by one so let me say here action I'm going to create a genre of name action and let me before I execute let me go to SQL Server management Studio I just want to go to genre selector 1000 and let's see that it is empty but now if I click on execute we are going to get back after a few seconds A200 okay and now if we go back here and press F5 we are going to see that indeed we have the action genre in the table a good practice in web apis is not to expose our entities to the external world and by that I mean that if I come back here we are exposing our genre entity to the outside world that is why here before if you remember Let me refresh the page if you remember we have all of this text here because it is taking into account all of the relationships that our genre entity has but maybe I don't want to expose that because maybe I want my entity to be able to evolve and that doesn't have to break my clients the clients of my web API so what can I do about it what I can do is to create a video dto stands for data transfer object and it is basically an object that allows me to represent the data that I want to transfer from one place to the other in this case I want to be able to transfer the data from the client into the web API of the genre that I want to create so let's do that let's go to resolution Explorer and let me create a new folder add folder dtos and I will create a new class in it class and I'll call it genre creation DDO this is a dto for creating a genre and as I said it is going to contain the information that I need to create a genre which is the name property so name and here I can put this but not only that I can also specify that it is going to have a string length maximum length of 150 so that if by any reason I try to create a genre with more than 150 characters the web API itself is going to return a validation error so now let me copy this and let's go back to genres controller and let me paste that here but now genre is of type genre creation dto but we cannot do this because John Recreation dto is not an entity generation entity not John Recreation video so we have to map this joint Recreation video to a genre data type now we can do that manually but we can also automate it using a library like Auto mapper so let's do that let me come here let's go to the project right click minus look at packages browse paste click on here install and now let me close this and let's come here and I need to configure Auto upper so let's go to the solution Explorer let me create another folder I'll call it utilities right click on it add class Auto mapper profiles Auto mapper profiles are basically the configurations that we have to have in place in order to be able to map from one type to the other so if we want to map from your Recreation dto2 genre we have to configure that so let me say here profile which is a class that comes from our mopper and now I want to put here a Constructor and I want to say create map and here I will say I want to be able to map from John Recreation dto to genre and that's it with this we are able to map from the recreation detail to genre now I have to configure Auto mapper in the program class so let's go to the program class let's come down here Builder services at Auto mapper and I want to say type of program so that automaper scans the assembly in which we are in so that it detects that it has this configuration here now I can go back to genres controller and now I can use automaper for that I can inject imapper mapper console dot create an assign as a field now let me say here genre creation dto and now I will say about a genre let me say genre mapper map let's map to genre and I want to map my genre creation DDO variable and that's actually it with this we can press Ctrl F5 one more time to run my application and let's go back here and now let's go back to API slash genres and now see that there is not a lot of information here because now we only have the name property because that is what it has major recreation.to so now let me say comedy let's see that this works unless they execute and now we can go back here and let's see that indeed we have comedy it is not mandatory to use DDOS but it is a good practice especially in a web API we can also insert multiple records using add range here's an example I can copy this I will basically repeat this example but now I will receive an array of John Recreation video and also I need to put here a name like several so that we can differentiate these two endpoints and here I want to map to an array of genres so this is going to be genres and instead of add I want to use at range because I want to add several genres and genres here and that's actually it let's now run our application again and we're here and let's go to several try it out and as you can see here we have an array so I can copy this put a comma here and paste this here and here I can put drama and after that I can put biography all right so let's execute this and let's see that indeed we have a 200 okay and if we go back to SQL Server F5 we have drama and biography also by the way I think I haven't mentioned this so let me do it you can open the console here you can open the console that gets created when we run an Xperia netcode application and you're going to see that here we have the code that gets executed into the database so I just wanted to show you that all right so let me close this and let's continue now let's insert an actor so let's go back to visual studio and let's repeat the process let me close a few things because we have too many things open let's go to the solution Explorer let's go to controllers right click add class actors controller and just like we did before we're going to have some ceremony code API controller route API actors controller base and let me put here ctor because I want to have the Constructor application DB context context I mapper mapper control Dot create an assign remaining as fields and let me control this and Import Auto mapper and now again let me make an HTTP post we are basically going to write the same code that we did before just so that you can see that we can reuse basically the same logic between different actions because it is the same API now I will create an actor creation dto so let me go here and create an actor creation DDO class and what I'm basically going to do is that I'll go to the actor class and I'll just copy these three properties and I'll paste them here in actor creation dto if I want to I can put here this data annotation all right so now I can go back to actors controller and I can say actor creation dto control dot to import the corresponding namespace and you are going to see that basically we are going to use the same code let me do this again because it didn't go through the previous time so now it's done so basically what I'm going to do is that I will use the same code that I used here let's see that let me go back to actors controller let me say about actor equal to mapper map we're going to map from actor creation dto to actor and of course we have to configure that in the auto mapper profile class so let me do that let's go back here and let me just clone this line and say actor creation dto and I will project to actor and then let's go back to actors controller and let me say actor and I'm going to pass actor creation dto semicolon here and again I have to import in this case the entities namespace and then let's say context at actor say my column here await context save changes async return okay with this we are good to go again this is basically the same code which is something that I like a lot about in the different record because once you learn something you can use it in many places so let me press Ctrl F5 to run our application let's go back here here we have actors and API actors try it out and I can put whatever name I want like Tom Holland Fortune 500 Grand and the year is going to be 1998 one two execute and we have a 200 okay and if we go back to SQL Server Let me refresh here on tables so that we get all of the tables actors select top 1000 and here we have our friend Tom Holland now let's insert a movie this case is interesting because we will learn how to insert related data that is we remember that a movie has genres and actors so we're going to create a movie and we're also going to link it with existing genres and actors so let's go to visual studio to do that let's go here solution Explorer controllers at class movies controller and again the ceremony that we always do is this one so I'll just copy and paste it we have movies controller controller base API controller route API movies application DB content and imapper being injected into our controller now let's create our dto let's go to DDOS at class movie creation dto and we're going to create two videos this is the first one so let me say here let's go to movie I want to go to the movie entity and I will copy title in theaters our release date and let's go to movie creation video and paste this and also I'm going to create a list of genres but for genres the only thing I need to be able to link a movie with a genre is the ID of the genre so this is going to be a list of IDs so let me say here genres new list all right and also I need a list of actors because remember that a movie is also linked to the actors table but besides needing the actor ID I also need the character name and also the order in which the actor is going to appear that is something that we are going to do in the controller I mean the order part but we need to receive the character so for that we're going to create a new video I can just put it here or I can create a new file just for this dto I'm just going to keep it simple and have it here so movie actor creation dto actor ID this is the ID of the actor and also a string character all right this is the name of the character that the actor plays in the movie so now list of movie actor creation DDO movies actors and then new list let me put this in another line so that you can visualize everything better now let's go to movies controller and let's work so HTTP post public async task action result post movie creation DDO movie creation dto I'm going to map so I'm going to map to movie so movie equal to mapper map movie movie creation dto and let me import the entities namespace and now I need to configure this mapping so let's go to automaper profiles which I have here and now let's configure this mapping let me say here create map from Mobic creation dto to movie now remember that here in movie creation video we have genres as a list of int and in movie we have genres but as a collection or hash set of genre so I need to figure out a way to go from an INT into a genre for that what I'm going to do is that for each int I'm going to create a new genre object with set in as its ID alright so let's go to auto mapper profiles to do just that let me say for member for a member for the genres member this represents an entity by the way comma what do I want to do well I want to go to a dto so dto and I want to map from as I said I want to map from the genres field so genres which remember is a list of ins so let me put this here is a list of ins so for each end I want to create a new genre right and let me say here ID new genre I'm going to instantiate a new genre and I'm going to make the end in genres diary of the genre alright let me close here and finally we also have to map from movie actor creation dto to movie actor so let me configure that let me say create map movie actor creation video to movie actor and now let's go to movies controller and let's get to work let's say if movie genres is not null here what I'm going to do is that I need to change the state of the genres that I'm receiving why is that well because Entity framework core doesn't know that these genres that I have here that I'm creating here Entity framework or doesn't know that they correspond to existing genres in our database so we need to tell two Entity framework or that they are already 16 genres and there is any status that we can mark the genre entity with so that Entity framework core knows that they already exist in the database and that is unchanged that is the name of the status on change so let's go back to movies controller and let me say for each I'm going to do a for each genre in movie genres and let me say here context entry this entry is going to allow me to modify some of the metadata of the entity and one piece of metadata that I can configure is the estate and now I can say entity estate dot unchanged as you can see here right away we have a States like added which if you remember several minutes ago I mentioned that by using this add method that we have in the genres controller by using this add method I said that we are marking this entity as added what this method is doing is modifying the state of the entity to add it so that same array is what we have here as an entity estate we also have deleted detached modify and in our case we want to use on change which basically means that it exists but no change has been done to it so let me delete this Ctrl load to bring this namespace so that we don't have that many code in this line and that's it with this what we are doing is that we are linking a new movie that we're going to create to an existing genre now for actors remember that we said that we want to store the order of the actor and we say that we were going to do that in the controller this is the moment that we're going to do that so let me say if movie movies actors is not null if it is not null then we're going to do A4 and we're going to say here movie movies actors count all right so I'm going to say movie movies actors dot order equal to I plus 1. so basically what I'm doing is that I'm iterating over the actors that I'm receiving from the front end and I'm marking them with the same order as they came in now sadly because we're using a hash set for performance reasons we cannot use this code like this so what do we have to do well we can go back to the movie entity and we can change now we have a good reason to not use hash set and to use list Which is less performant but we actually need to use list in this case so we are allowed to do this so let's go back here and as you can see there are no errors here anymore so now let me come here and say context at movie semicolon await context save changes async return okay and with this we can test let me press Ctrl F5 to run our application let's go back to Swagger and let's go to movies let me create a new movie so I'm going to say here Spider-Man far from home in theaters 2 release date again I'm just going to put here the state genres so for genres I have to be very careful because this needs to correspond to existing genres so let me go to genres and let's see that we have here these four IDs I can only use these values I cannot use any value outside of these four so in my case I will use one and two so action and comedy so let's come here and let's say one and two and four movies actors the same logic applies I have to come back here and see What actors do I have and in my case I only have one actor which is Tom Holland so I can only use him so id1 right so id1 and character is Peter Parker now let me execute and we're going to see that we have a 200 okay and now let's see what we got let's go to the movies table and let's see that we have Spider-Man far from home in theaters through a release date but that is not the most exciting part the most exciting part is that if I go to genre movie select a 1000 we have movies id1 which is the only movie that I have and also John Ross ID 1 and 2 action and comedy excellent and not only that if I go to movies actors who are going to see that we have movie id1 actually D1 character Peter Parker and order one because it is the only actor that I have so it is of order one now let's insert comments thus we will learn how to insert data for a one-to-many relationship what we need to have at hand is the idea of the movie two ways we want to attach the comment in our case as we can see here we only have one movie which is the movie of id1 so that is the one that we're going to use so let's go back to visual studio and I want to create a new controller a controller for comments so class comments controller and this one is going to be a little bit different than the previous ones at least for the route so let me say API controller and now let's put the route here and what is different is that I'm going to say API slash movies slash movie ID and slash comments this is basically a way to indicate that there is a parential relationship between movies and comments because as we can see we will always have a movie ID when we want to request some comments now besides that everything else is the same I can put here our application DB contest and the imapper also let me say HTTP post public async task action result Post in movie ID this movie ID is going to come from this movie ID that we're going to have in the URL and also I want to have a common creation video so let me create that video right away class comment creation dto let me go to the comment entity let's go to comment and I'll copy content and recommend and let's go back here and let me just paste this and then let's go back to comments controller and let's say comment creation DDO control dot to bring the corresponding namespace command creation dto and then let's say comment equal to mapper map command Ctrl load to bring the entities namespace I will map from common creation dto and now let's configure this mapping let's go to auto mapper profiles and let me just clone this line and let's say comment creation dto to comment we want to map from coming creation video to comment so let's go back to comments controller and let's say comment dot movie ID equal to movie ID context at comment await context save changes async return okay all right now we can test this let's press Ctrl F5 to run our application and here we are let's go to comments as we said we're going to be using the movie of id1 which is the only one we have and for Content I'm going to say I love it and I will recommend it alright so let's execute this and we have a 200 okay so let's go back to SQL Server management studio and we're going to see that indeed we have the common created we have already seen repeatedly that we can insert data using the add method and by that I mean this add method that we have here with the save changes async however sometimes we don't want to insert data manually but instead we want to put the data in migrations so that it is created automatically for that we can use data setting let's see an example suppose that we want to have some default genres in the database for that we can go to the genre config file let's go to the genreconfig file and in here I can say bar science fiction I'm going to create two new genres so new genre ID equal to five let's make sure that we don't already have a genre of id5 in the genres table as you can see here we only have iv4 here as the maximum value so I can use five and let me say name science fiction I'll do the same but this time for animation let me say 6 here and then here animation now I can say Builder has data and I can pass science fiction and animation now I can add a migration that is going to indicate that we want to insert this data into the database so let's do that let me say package manager console and I will say add migration genre data and let's see that indeed we have insert data in genres I read a name id5 name science fiction and id6 and name animation however for practice I want us to add data for each table but I don't want to have it so that we have to go to different files to configure that so what I will do is that I will create a central class that is going to contain all of the data setting code so let me close everything because I have too many things open so we have more space now what I was saying is that I want to create a new class that is going to contain the data setting code so I'm going to create a folder I'll call it seating hearing entities right click on it add class initial seating and because it is a lot of code I will copy and paste it but don't worry that we are going to read it line by line so let me paste this let's come up here and let's see what we got we have a few actors we have Samuel Jackson and Robert Downey Jr as you can see they have ID 2 and 3 then I say model builder entity actor has data Samuel Jackson and Robert Downey Jr then we go for movies we have the Avengers movie and Spider-Man no way home and also we have a Spider-Man spider verse 2 and then we say model builder Entity movie has data Avengers and the other two Spider-Man movies then we go for comments for comments remember that we need to also pass the movie ID so that is why we say for example Avengers comment new comment ID of the comment recommend through content and movie ID and here we're using Avengers dot ID so that we can pass this as a reference and then we do the same for another comment for The Avengers movie and for no way home and finally we say a entity comment has data and we pass the three comments now we get to the little bit Advanced part which is the inserting data in the many too many relationship table I mean the intermediate table for the many-to-many relationship between genres and movies with if you remember it is a skipping many to many relationship in which we don't have access to the intermediate entity that represents the intermediate table so in this case we have to do the following I put here the name of the table the name of the table you can get that from here you can go to SQL Server management studio and you can see that we have genre movie This is the name of the intermediate table so I put that here and then for the genre ID property we have genres ID which again we can get from here we have genres ID and movies ID that is exactly what I'm putting here and then I'm saying that I want to use the science fiction and animation genre which we added in the genre config class and then we use model builder entity and we pass the name of the table in which we want to add data then has data new dictionary a string object and then the genres ID column science fiction and then the movies ID column is going to be equal to the ID of the Avengers movie and then we'll do the same for whatever order record we want to insert into the genre movie table I said this is a little bit Advanced because we have to use a lot of code just to insert data into this genre movie table because we don't have access to the intermediate entity in the case of the many to many without skipping it is simpler because we have access to the intermediate entity in this case movie actor remember that there is a many-to-many relationship between movies and actors and we have the movie actor entity to represent the intermediate table so as you can see here we only need to use that class as a normal class and then we can use model builder Entity movie actor has data and we can pass the corresponding movies actors and that's it then now that we have this class we need to tell the application daily context about this class so let me say application DB context and let's go to a model creating then I'm going to say initial seating control Dot dot sit and then we are going to pass the model builder and that's it with this we're applying this seating that we have here alright so let's add a new migration let's say at migration tons of data and we're going to see that indeed we have that data here and now let me say update database to push these changes into the database now that we have all of this data in the database we can work with querying data now we're going to read data from our tables so let me close this and this and let's continue what we're going to do is that we're going to go to genres controller let me go to genres controller to the genres controller class and we are going to create a new action it is going to be an HTTP get because we want to get information so let me say public Asin task action result we're going to do the simplest example and then we are going to do more complicated examples later on so I enumerable I want to return an innumerable of genres and we're going to say get and the simplest way to get data from my table is to use to list so let me say return await contest genres because we want to access the data of the genres table to list async all right and that's it with this we are getting all of that data from the genres table let's see that let me press Ctrl F5 to run our application let's go back to Swagger and let's see right now here in genres we have get so let me click on it and let me say try it out execute and we're going to see that now we have action comedy drama biography science fiction and animation and as always we can do the same in another controller for example we can use basically the exact same code in actors let's see that let me go to actors let's go to actors controller and let's see that I can paste this code here and the only thing that I need to change is actor here and actors here and that's actually it with this we have a query that gets all of the actors of the actors table so let's go back here and let's go to actors get try it out execute and we're going to see that now we have Tom Holland Samuel Jackson and Robert Downey Jr again as you can see we can basically reuse code between entities to do the same operations but what if we don't want to get all of the data from a table well for that we can use filters for example we can build a search by name functionality for actors let's do that let's come here we are in the actors controller and let's say HTTP get name because I want to search by name public async task action result I enumerable of actor get a string name the name of the actor I'm going to name this version 1 because I'm going to have another version in just a moment so let's say return away context actors where I want to filter and I want to say where the actor were the name of the actor is equal to the name that I'm getting here then I'm going to say to list async now we can test this so let me compile let's go back here and now let's see that we have API actor's name try it out I can write here a name for example I can say Tom Holland execute and now let's see that we have here only Tom Holland because he's the only one that has that name now the problem with doing it like this the problem with doing it like this is that this requires an exact match for example if I come here and I write instead of Tom Holland just Tom it is not going to return anything because it is not an exact match that is why I prefer to use contains in these scenarios so let's make another version of our search by name method I'll name this version 2 here and V2 here version 2 and instead of using this code I will say where the name contains whatever value I have here now this is more flexible because for example Tom Holland contains thumb and therefore we're going to be able to retrieve Tom Holland's record by only writing Tom let's see that let's come here V2 let's say Tom execute and let's see that now we have Tom Holland and also if I write J execute we're going to see that we have Samuel L Jackson and Robert Downey Jr both have J in their names we can also use binary Boolean operators such as and in our function for example suppose we want to search for actors by date of birth but we want to use a range so let's see that let's come here and let me say here HTTP get date of birth range and let me say public Asin task action result innumerable a factor get date of birth date time start and date time end return await context actors where and I want to say where the actor were the date of birth of the actor is greater than or equal to the start date and the same date of birth is less than or equal to the end date and then we have to say of course to list async and with this we can test let's come here and let's see that let me see the days that we have for the actors we see that for example we have Tom Holland in 1998 this one in 48 and 65 so I'm going to go from 45 to 70 so let's come here that of range and let's say from 1945 one one two 1970s one one so which actors were born between these two dates let's see so of course we have Samuel Jackson and Robert Downey Jr if we want instead of getting all of the rows of a search we can get the first one only by using first of all default for example let's come here and let's come down here and let me say HTTP get and what I'm going to do is that I will receive here an ID which is going to be an integer public Asing task action result of actor I don't have to return an innumerable because I know that I will only be getting one actor at most in ID and let me say bar actor equal to a weight context actors personal default async and in here I can pass x dot ID where the actor has the same ID that I'm passing here and then I can say if actor is null because this is first or default what does this means what does first or default means first or default means that it is going to get either the first actor that has this ID or if there is no actor with that ID then it is going to get the default value for the actor entity and because the actor entity is a class and therefore it is a reference type the default value of a reference type is null so this is going to be null in the case that there is no actor with the ID that we have here so if actor is null I want to say return not found a 404 not found otherwise I want to return actor let's test this let me compile and let's come back here so let's go to ID try it out let me say for example 2 execute and as we can see here we have a single actor now if I write for example 20 I don't have an actor of id20 so we're going to get a 404 not found also we can choose the order in which the records will appear using order y for example let's go to genres I want to go to genres and let's see that we have action comedy drama biography as we can see it is not ordered by the name but by the ID what if I want to order by the name we can do that let's come back here let's go to genres controller and here in this get method I can say or thereby by which column well by the name column and that's it Ctrl F5 to run my application one more time and let's go to genres and let's go to get try it out execute and we're going to see that we have action animation biography comedy all the way through science fiction which means that indeed now we have it ordered by name also we can order in a descending manner this was in an ascending manner we can use a descending manner by using order by descending and it is exactly the same let's see that let's come back here and let's say genres get tried out execute and now we're going to get from science fiction as you can see here from science fiction to action so it is ordered in a descending manner something very interesting is that we can order by several columns that is for example we can order first by a name field in the case of an actor and named by the date of birth field for example let's go to SQL Server management studio and let me go to actors edit up 200 rows because this will allow me to easily do this I want to put the same name here even the same portion but I want to have a different date of birth for example 1950 1221 enter so now we have two actors with the same name but they have a different date of birth how can we order them first by name and then by the date of birth column well for that we have two special methods in Entity framework core so let's go to actors controller and let's go to the name method and let's see that I can order by let me say order y we know that we can order by name that is easy you already know how to do that but how then I order by another column well I can use them by them by and then buy this ending with them buy I can order by a second column in an ascending Manner and then by descending in a descending manner so for example if I say then buy and I say here x date of birth let's see what we get let me compile my application and let's go back here and let's go to actors name two I'll just say j just so that we can have Robert Downey Jr also in the result set so we have Robert Downey Jr the first one Samuel Jackson the second one and Samuel Jackson the third one and we have it so that we have first the Samuel Jackson that was born in 1948 and the second one is the one from 1950 so let's flip them let's order by the date of birth but in a descending manner so then by descending now let's compile and let's see what we get let's go to name version 2 Try It Out J execute and let's see that now we have Robert dummy Jr first that is fine but Samuel L Jackson the one from 1950 and the second one is the one from 1948 which means that indeed we were able to first order by name but then if the name are equal then we order by date of birth in this case in a descending manner in addition to selecting records by using a word a filter we can also select which columns we want to fetch for example as we can see here we are getting the ID the name the fortunate of birth but maybe we don't want all of that but maybe we just want the ID and the name of an actor let's see that let's come back here and let's come down here for example here HTTP get IED and name public async task action result get ID and name and now let me say actors equal to await context actors and we can use the select function so that we indicate which columns we want to fetch so let me say here new x dot ID and x dot name I can use it this way to indicate that I want to project from actor this is like a mapping I want to map from actor to Anonymous type so let me say here to list async and then return actors though I should say okay so that we can return this this is because I'm saying that I want to return an action result and then in order for this to work I have to say Okay actors the reason why I can put here a data type it is because I just said that I'm mapping to an anonymous type so I cannot represent that here but we will see a face for that in just a moment for now let's see that this works let me compile my application and let's go over here and let's say ID a name try it out execute and let's see that now we only have diary and the name of the actors we have Tom Holland Samuel Robert and Samuel again because we have two summary Jacksons now if we don't want to use an anonymous type like we did here we can build a dto for example let's go to retails at class actor dto and let me put here ID a name and now I can come back to actors controller and I can say here I a numerable of actor DDO let me put this like this and now I can say here new actor dto so I will be mapping not to an anonymous type but two an actor video so let me say here ID equal to ID and the name property is going to be equal to name and then I don't even need this anymore so I can say actors now you see this purple lines that is because of a hot reload functionality that wants me to compile my application that is done now the lines will disappear and we can go back here and let's go to idea name and you are going to see that it is the same so we got the same result but now we are using a DDO we saw that we have related data for example a movie has comments how can we query the data of a movie with its comments there are several techniques for doing this we will see two eager loading and select loading let's start with eager loading the idea of eager loading is that we must indicate when we want to load the related data of an entity for example let's go to movies controller let's go to movies controller and let's go here and let's say HTTP get I want to pass an ID public Asing task action result of movie get in ID and we're going to use the same code that we use here so I can even reuse it let me look for this one so I can reuse this let me paste this here and let me say movie movies all right so let's see what we have let me compile my application let's go here and let's go to movie we remember that we have some movies let me see which one has comments so let's go to comments and we're going to see that for example the movie of id2 has two comments All right so I will use that one so let me go here id2 execute and we're going to see that indeed we have Avengers end game but we don't have the comments why is that well as I said I need to be explicit about the fact that I want to load the related data in the case of eager loading I do that by using include and then I can pass here the navigation property of comments so I can say comments and that's actually it with this I can compile I can go back here I can go back to movies try it out and then to execute and let's see that we have here an error a possible object cycle was detected what is this well the problem is the following let's go to movie and we're going to see the movie has a reference to comment and let's go to comment and we're going to see that coming as a reference to movie which again has a reference to comments so we have this circular reference between movies and comments and the Json serializer is complaining that he doesn't know what to do so it just throws an exception how can we fix this well the reason is a fixed actually so we can go to the programming class we can go here and say add Json options options options Json serializer options reference Handler equal to reference Handler dot ignore Cycles and this is going to fix the issue for me all right let me put this in this manner so that you can visualize everything on the screen now another solution if you don't want to do this another solution would be to use dtos for example to have that you will return a mobilityo and a common dto in such a way that they don't have a circular reference between them but in our case we will just keep it simple and we're going to implement this solution so let me run my application let's go back here and let's go back to movies again try it out I need to execute and let's see that indeed we have Avengers end game and we have the comments very good and I love it also we can get genres and movies actors let's see that let's come back here let's go back to movies controller and let's go here and let's say include genres and also include movies actors and let's compile and let's go back here let's go back to movies and let's see that and try it out I need to execute we have the comments of course the genres like science fiction and the movie actors like Nick Fury for the character and Ironman for the other character but as we can see here we don't have the name of the actors for example we know that Nick's Fury is played by Samuel L Jackson but we don't have his name here why is that well remember that in our many-to-many relationship between movie between movie and actors we have the movie actor entity sitting between them so if we go to movie actor we don't have here the name of the actor we have the actor ID which is what we have here actor ID but we don't have the name here what we have to do is to now load the data of the actor that corresponds to that movie actor how can we do that well that's very simple we have to go back to movies controller and after this include we can say then include which will allow me to go to the movie actor entity and indicate that in there I want to load the actor entity and that's actually it with this we can compile and we can go back here and now let's go to movies try it out id2 execute and we can see that now in movies actors we have yes character in Fury but also actor Samuel L Jackson so as you can see we are loading that data of the actor and the same goes for Iron Man with its actor which is Robert Downey Jr but let's observe that we have ordered to hear an order one here so they are not appearing in the correct order how can we order by these related data well for that we can do the following we can go back to this include movies actors dot order by or thereby and we can order by movie actor movie actor order and let me close this parentheses and let me compile my application and let's go back here and we can see that if we go back here ID to execute let's see that indeed now we have it in the correct order we have order one here and order two here so far we have been loading all of the columns from all of the tables involved in a relationship but maybe we don't want this to fix this we can use a select as we have already seen when we use a select to load related data we call it select loading let's see let's come back to visual studio and I'm basically going to clone this but I will call it here select for select loading and get select so instead of using include we are not going to be using Clues anymore for select loading but we will use select so let me say select and I'll say move of movie so new again we can map to an anonymous type or to a dto in our case to keep it simple we are only going to be using an anonymous type so ID equal to move ID let's say I want the ID of the movie title equal to move dot title genres let's say that I want a genres so let me say move dot genres dot select and let's say that I only want the name of the genre so let me say here name to list also let me say that I want the actors so actors so let me say move movies actors order by we have to order y movie actor movie actor order and then I want to do a select I want to make a projection because I only want to have the following information of the actors so I want to have the idea of the actor which is going to be m a actor ID name m a actor name and also character I want to have the character so character equal to m a character and then comma and we made a mistake here this should have been an equal sign all right so finally I don't want to bring the comments but only the amount of comments we have so comments quantity equal to move comments count and look how easy it was for me to retrieve this information I'm very intuitive also so now let me correct this let me fix this here because again this is an anonymous type so let me say here okay and movie now let me compile my application and you are going to see that now we have the same data but without all of the extra columns that we don't need alright so here we are let's go to movie select try it out to execute and let's see that now we have id2 title Avengers genres Science Fiction actors these two actors and comments quantity two alright so now let's talk about updating records let's start with the genres in Entity framework core you can update records in two ways with the connected model and with that disconnected model just for testing let's look at a connected model example let's go back to visual studio let's go to the genres controller and let's come down here and let's say HTTP put I want to have here ID end and also name two what I'm going to do is that I'm just going to add a 2 at the end of the name of the genre again this is just a simple example to explore the connected model for updating records in any different recorder let me say here VAR genre equal to a weight contest genres the first thing that I need is to bring the genre from the database so let me say GID equal equal to ID if genre is null there is nothing that I can do so I will just return not found otherwise and this is the connected model basically when we load data with Entity framework core by default Entity framework core keeps track of the changes that that entity suffers for example if I say genre dot name equal to genre dot name plus two so we're adding 2 at the end of the name and Entity framework code takes notice of this so if we say await context save changes async this change that we did to the object is going to get replicated into the corresponding record in the genres table so let's see that return okay let me test this let's come back here and let's say that we can go to genre spot try it out let me see which one we can update let me see here in genres let's go to genres select of 1000 let me update drama for example so three so three execute and this is done now if I go back here F5 we're going to see that we have drama 2. however in web environments it is normal but not mandatory to use the disconnected model the disconnected model is used like this so let's come back here and instead of doing it like this I will do the following I will say HTTP put ID int public Asing task action result put in ID and we're going to get for example a dto it could be some sort of genre of the detail but in our case just to save some time we're going to be reusing the general Recreation video so join Recreation video so in the disconnected model we are going to have a genre instance so let me say here for example mapper map genre we want to map from John recreation.to to genre now this genre instance was not loaded by a DB contest right because here we have genre and that was loaded by the DB context so this is why it is called the connected model because the DB contest that loaded this data is the same one that is going to update it but in here we have this genre object that was unloaded by a DV context and now our DB contest has to save that data into a database so that is why it is called the disconnected model because the DV context that is going to save the data is not the one that loaded that genre so let's come here and let's say genre dot ID equal to ID and now I will say context update genre and with this I'm basically marking this genre has to be updated just like before with this ad I was marking the status of this genre as added or to be added now I'm marking this as to be updated so now I'm going to say await context save changes and now it is when the genre in the database is going to get updated and now let me say return okay so now let's test this let me press Ctrl F5 to run an application let's go back here let's go back to genres and let me use this one now I will update drama one more time remember that ID3 is for drama and now I will just put drama here so that we remove these two that we have at the end of the name drama so let me say execute and let me see that we have a 200 okay let me refresh here and now we have drama another thing we can do is delete records for that we have to waste in Entity framework or 7 there is a new way to delete records I will teach you the new way and the old way we'll start with a new one in order to delete a record in a very efficient manner we can use the following code let me come here to genres controller and let me say HTTP delete I'll say here ID int modern public async task action result delete int ID let me say bar altered rows and I'll explain you what alter rows means in just a moment I'll wait context genres where let me use a where ID equal to ID and then execute delete async so what is this so basically I am doing a filter I'm trying to get the genres that has this ID that we're getting here and then I'm executing a delete operation over those records now what this returns is the total number of rows that were deleted in the database so that is what I call alter rows this is a number this is the amount of rows that were removed from the database so if this number is equal to 0 if alter rows alter rows is equal to 0 or we can just call it removed rows if the remove rows is equal to zero then it means that no record exists with this genre ID so we can safely return not found otherwise if this was not zero so it was one for example then we can return okay or no content which is basically the same it is a 204 it is still a successful operation now let me make a test let me come here I want to right click on genres edit up 200 rows and let me say test genre and now I will delete this genre so let's see that indeed we have it here now test genre all right so Ctrl F5 to run our application let's come back here and let's see that we can delete the genre of ID 7. so execute and let's see that we have a 204 no content so now let's come here and let's see that indeed we don't have test genre anymore now just in case you run into it or maybe you need it because you are using an old version of any different work or I will teach you the old way of deleting a record so let me say here HTTP delete ID int old way public async task as shown result delete old way in ID and now I will do the following I will say bar genre equal to await context genres first order default async g dot ID equal equal ID so I needed to load the record in memory and then if the record is null then we say not found and after that I was able to say context remove genre semicolon here await context save changes async so just like before when we say context.ad we are just marking the entity to be added and in here we're saying contest remove so we are marking the entity the status of the entity has to be removed so it will be removed or deleted from the table when we do a safe changes async so now let's say return no content but it works basically the same as the previous one as this one but this one is more efficient because you don't need to load a recording memory for deleting it now there are occasions in which you may need to use this for example if you need to access the date of the genre before deleting it so you may need it anyway in the future now let's see what happens when we delete an entity with related data like a movie for example let me come here let me copy this and let's go to movies controller I want to reuse that code to save some time so let me say here instead of genres movies and that's it with this we have replicated the code and now we can press Ctrl F5 to run our application one more time and let's go back to movies and let me delete a movie that has comments for example I will delete the movie of ID3 because remember that if we go to comments we're going to see that the movie of ID3 has a comment which is this one so we're going to see that when we delete the movie we are going to get the comment also remove so we can see a 204 here and let's go back here and let's see that now we don't have the comment of id4 anymore and of course we deleted the movie of ID3 if we go back here we can see that we don't have a movie of ID3 anymore another thing that Entity framework core lets us do is Define indexes for example suppose we want the name of a genre to be unique that is two genres cannot have the same name well for that I can create an index let's go back to visual studio to do that let's go to genre config and in here I can Define the index I can configure the index let me say Builder has index and I can say the property name or the name property is unique is unique very good now with this I can add a new migration at migration genres name index and this is going to indicate that we want to create a unique index for the name column now let me say update database to push these changes into our database and now let me controlify my application because we're going to see that now I can't create two genres with the same name so let's go back here and let's see that if I go to genres post here let me say drama we know that we have a genre of name drama we are going to see that we get a nasty exception as you can see here of course if you don't want to show this to the user you can do something much better which is a validation we can come here for example to this post and I want to say bar genre access with that name await context genres any anything we can use any to verify that a record exists or not so we don't want to load a record we only want to know if it exists so this is very efficient so let me say name equal equal genre creationdto.name semicolon here and let me put this in another line so that you can visualize everything better and now if genre exists genre access with that name if this is true then return but request there's already a genre with the name and let me just concatenate the name here John Recreation dto name semicolon here alright so let me compile my application and let's come back here and let's do the same but now we're going to get a mosh mosh friendly error so let me go to post try it out drama and execute and let's see that indeed we have there is already a genre with the name drama much better another cool thing about working with Entity framework core is transactions the idea of a transaction is that we can work several operations as if it were one in such a way that if there is a problem then none of the operations is carried out by default when we use Save changes we already have a transaction mechanism for example remember that we have this endpoint several that allows us to insert several genres so what I want to do is that I want to say the following test transaction genre one so I want to add this like this so I'm going to have tester in session one test transaction two and test transaction 2 again this means that we are going to have two genres with the same name now this one doesn't have that problem but because it will be part of the same transaction none of the operations will go through let's see that let's execute and let's see that we have an error again because of that unique index but if we go back to SQL Server management studio and we go to genres selector 1000 we are going to see that we don't have any of the new genres why is that well because and we go back to visual studio and we go back to the generous controller let's go to several we're going to see that we have one save changes which means that all of the operations that we're doing are part of one transaction and if one of the operations fail then all of them fail finally we're going to publish our web API in Azure so that we can see that it is very easy to configure a database with Entity framework core in the cloud so let me go to the solution Explorer and let me come down here to the upselling developer in the Json because I want to copy this default connection name and I want to have it here in a file and also this connection strings just in case I need it I'll put it here because I will need it in a few minutes so let me close everything and let's come here and let me minimize all of this right click publish of course if you want to publish on Azure you need a natural account so I already have one so I can move forward so let me go to azure Azure Windows Azure app service we're going to use an Azure app service so I will use my visual studio Enterprise subscription I'll say create new I want to create a new app service I can leave this name I don't mind and I can use a resource Group I can use a new one intro to F core Eng a resource Group allows me to group resources in Azure like a database a web application and so on on a single unit I can also Define a new hosting plan which is the one that says how much I'm going to pay depending on the characteristics of my web server I can use a B1 which is fine okay create now this is going to be creating the Azure app service this is where our web API is going to be located this is not publishing my web API this is only creating the Azure app service that will host my web API all right so this was created or I should have service was created so I can click on next give this a step finish and now we have our published profile ready so we can go down here to service dependencies and we have Azure API management I can ignore for all profiles but I need to configure the SQL Server database so let's say connect I will use a Azure SQL database create new I will go to database server and create a new server you can choose whatever location is closer to you in my case East US is fine username I will just put cabalange and for a password I will use a super secret password now I need you to keep this information at hand because we are going to use it in just a minute so let me click on OK and let me also create a database or create this may take a few minutes so please be patient now let's click on the database that we want to use and let's go to next as I mentioned before I needed the information that we have on our app settings so let me copy this and paste that here the name of the username is Gabby Lynch as you saw and also the password is my super secret password I will save the connection string in the Azure app settings next now in here please uncheck this and keep this one checked and now finish the second one that we kept checked will allow us to apply the migrations automatically into our Azure SQL database so let's do that let me come here and let me wait for this to load remember that at the beginning of this video I told you that it was mandatory to install the Entity framework or click tool so this is why it is mandatory because this step requires the Entity framework or CLI tool now something that may happen to you as it is happening to me is that I have the application running so that is why this may not load so let me cancel and let me do it again and it is done so let me come here and let me say apply this migration and publish save and we are ready to publish so let me click on publish and this is going to publish our web API it is going to also apply the migrations into our Azure SQL database and we'll be able to use our application in azure now we're going to get here a 404 because we have to go to Swagger so let's go back to here I need to activate Swagger on production so for that I can go to the programming class I can just remove this code and now Swagger is going to be on production so let me publish one more time and again we're here now let me say slash Swagger and we have Swagger in production so I can for example create a new genre let me say action execute and we have a 200 okay and if we go back to get now we can see that we have indeed science fiction animation and action because remember that these were part of a migration all right so if you want to learn more about Entity framework or please buy my uremic course today where you will learn much more about this technology link with a discount in the description of this video thank you

Channel: gavilanch3

Views: 8,569

Keywords:

Id: 7oMdDe4TIqY

Length: 128min 52sec (7732 seconds)

Published: Fri Jan 27 2023