C# Entity Framework - More Advanced Topics on EF Core 5

[Music] [Applause] [Music] hi students and welcome to another week of distance learning today we are going to dive deeper into entity framework last week we started with the very fundamentals of entity framework and today we are going to pick a few topics and take a closer look on how entity framework does its magic how do we how are we going to approach today's lesson just like last week you will get this video from me and you can work it through based on on your environment and your learning pace please start visual studio and follow along the examples that i show you in my visual studio so whenever i type in code try to follow along pause the video when necessary try to understand what i do and whenever it makes sense for you to understand more details about any framework set a breakpoint use the debugger to inspect a little bit what is going on behind the scenes write some additional lines of code and just explore the topics that we are going to cover today i will be present in discord and teams for individual questions so if you don't understand a certain piece of code if you want to know something that i do not cover in my example or if you have individual problems with your environment accessing a database for instance don't hesitate to switch over to this part or teams and let's discuss your problems or questions there and with that we are going to dive right into the topic we're going to cover again what with this last week you can re-watch the video if you have forgotten some things here but we are going to build on the solution that i created together with you last week for those of you who can't remember what we did or maybe who were sick and couldn't follow you can start with the exercise that i pushed on github here in let me see here in this folder entity framework intro we have a small sample app that we created last week and i will copy this sample app and together we will build on top of this existing code and try to cover some more advanced topics so let's get started and let's copy this solution to have something with which we can play here i have this entity framework introduction sample that i created last time and i will just copy it of course you can create your you can take your solution that you created last week when you were following along my exercises but i will just take the one that is on gita now let's open this solution in visual studio 2019 here we are in this solution we have a bunch of metadata sorry a bunch of demo data that we can get rid of so please leave these two lines the creation of the context up here and delete everything else up until or down until yeah the modal classes you can remove everything down to the modal classes please keep the modal classes because we will just use them as a basis for our today's experiments just to recap what we did last week we had dishes a dish has a title notes stars and ingredients and we have multiple ingredients for a dish here you see the ingredients down here you have the database context which we will called the cookbook context where we can query the dishes and query the dish ingredients last but not least last week we created a context factory a helper class which we use to instantiate a database context please remember that we take the connection string from the configuration file app settings json here you can see it let me zoom in for you so you can see it better here you see the app settings json please before you run the application take a look at the existing app settings json and make sure that this one works for your local pc if you just follow the along last week everything should be just set and everything should be fine we can immediately immediately take off where we left okay so let's assume that the modal classes are present the context works the context factory works and we will start our experiments up here where we have the cookbook factory in fact we don't need the context here this is the only line of code that we currently need now let's make a short experiment and in this experiment i would like to create a new dish and update the dish let's do that let's create a new dish new dish and set it set the title to foo and set the notes to bar that looks good i'm fine with that let's add this object to the data context for that oh i was wrong i'm sorry we have to create the data context here so using var db context equals to factory dot free hdb context just like last week nothing new so far now we can add the new dish to our database db context let's write it with an uppercase c i like that one more let's add it to the list of dishes new dish that looks good and do a dbcontext.c changes async we covered that last week so i'll not i will not repeat that and now comes the interesting part the part where i would like to take a closer look on the generated sql statements and where i would like to go into more details so you understand what's really going on behind the scenes and this part is updating the entity i mean this statement is pretty obvious isn't it the add command in c sharp leads to an insert statement in the database pretty simple pretty basic stuff but now let's change new dish the notes on our new dish object baz and then we will do another save changes lot week we learned that this save changes will update our notes in the dish table but this week we will dive right into that one before we run this program i ask you to scroll down to the nearly very bottom of your code and uncomment this line here this line says use logger factory and last week i told you that by uncommenting this use logger factory here we are going to see the sql statements that are generated while running entity framework code so it makes it easier for us to understand what's going on on the database side okay so please scroll down and remove the comment for the use logger factory and with that we can set a breakpoint here for the save changes the second one and run our code in the debugger let's try this let's run it in the debugger the debugger comes up a bunch of logging appears and now we are here in the save changes remember what we did we just updated the notes on our new dish object let's run the save changes async and then let's take a look in the generated sql code here you can see the sql code and the important thing that i would like to draw your attention to is this one somehow entity framework managed to recognize that we just updated the notes it did not update the stars it did not update the title it did not update the ingredients just the notes how did it do that how does entity framework know that we just updated the notes here that's not a trivial question as you might know c sharp does not store magically a history of all changes to a c-sharp plain old clr objects also called pulco this is a poco plain old clr object nothing special just a class with a bunch of properties no history store hidden behind the scenes nothing we just change a property and then we say hey data database context whatever i changed on any object that is currently in memory magically write everything to the database i often call this you know that already harry potter programming a little bit of fairy dust and entity framework just makes everything magically happen how this is not obvious and this is what i would like to um to explore a little bit more in the next few minutes for that we are going to write a small method a static async task and let's call it ntt states why we call it entity states you will see that in a second okay we have the cookbook context factory this is what we want to have and we want to have this as a parameter so please oh i'm missing a using statement here so control dot gives me this nice little helper intellisense here i just press enter and i have the system threading task appear installed that looks good now we get in the factory and let's all our helper or our experiment method here has in the factory as you can see it here and awaited now we can do our stuff here inside of this method here i would like to show you what's really going on behind the scenes and what entity framework is really doing in order to make this magic up here happen good let's create a new database context on which we will do our operations okay using mar db context equals to factory dot creates db context good we have a database context now let's create another instance of this new dish i will copy this line of code here this one and edit here and now comes the new part the first part of the new part if i take the db context oh this one the db context this one i can use a method which is called entry to get entries inside of the so-called change tracker this is a hint of how entity framework does its magic behind the scenes it has a change tracker a mechanism which tracks changes to objects that it already knows so we can ask dbcontext to tell us what it knows about our new dish every entry in the change tracker has a state as you can see it here let's write this state in a variable so we can easily explore it in the debugger so let's set a breakpoint here at the last line of our method we can remove this breakpoint up here and then let's run the code until this line here if i hover over state i will pin it here on the debugger side you see it i will put it here then you see that the current state here that entity framework has about our new this year is detached detached means that entity framework tells us it has no idea what this new dish is all about it didn't come from the data database context we did not read it from the database entity framework has never seen this dish and therefore the state of this object in terms of entity framework is currently called detached now if i add this new dish to my database let's do that dishes add new dish the state will change let me copy the states down here and let's see how the state looks now because now entity framework has knowledge about this object the object has not been written to the database that is important because we didn't call which method correct save changes async as long as we don't save changes the changes are only recognized in memory so with the add method we just add a plain old clr object to entity frameworks change tracker and if we call save changes afterwards we will do that in a second then the change cracker will go through all its entries and will generate the appropriate insert update or delete statements now let's try to find out what the state is after we add a new object to the change tracker of entity framework let's run it in the debugger and we will see that the state is now you see it here the state is now added so we went from detached what we had before by calling adds here this brings us to the edit state so let's note that this is now added so we know the chain tracker has a new object which is added to the change record and now we call save changes async we can copy the save changes async method from here and again we will inspect the state after we call save changes we don't know what the state is now so let's take a look what changes in terms of the change tracker when we call save changes async run it in the debugger and we are here and now we have a third state here you see the state it's unchanged so it went from detached entity framework says i have no idea what this object is all about then we call add and this changes the state to edit entity framework says okay i know this object i haven't written it to the database but i know of this object when you call save changes i will make sure that it finds its way into the database and when we call this save changes we have the next state unchanged because now entity framework tells us okay i know this object and i have written it to the database therefore it is unchanged from my site everything is fine so let's note that down unchanged good now what happens if we modify something in this object let's say new dish dot notes equals baz that's the change that we did before now again let's inspect the state after we made a change to an object which is already tracked by entity framework let's run it oh i didn't remove this one this is wrong of course this is this is wrong we are just questioning what is the state now and here you see the state the state is now modified by just changing a property on our plain old clr object entity framework recognizes that we changed something how does it do that how does it do the how does it recognize the change does it somehow magically attach a kind of event handler that gets notified whenever we do when you apply any changes no no it has a different mechanism and i will show you this mechanism in a few minutes but before i show you how entity framework does its magic i would like to stay a little bit longer here on these states because now we know that this is modified but we have not let me note it down but we have not taken a look at the third option that we have not just adding and modifying but also removing let's try the removing experiment and then i will show you how entity framework does the change tracking internally okay so with that um let's do another save changes we already know what's happening after save changes it it is changed from modified to unchanged again we already know that but now we are going to remove our record so we say really context dot dishes dot remove and say new dish so the dish that we just inserted is now removed and now it is again interesting because we are going to take a look what the state is now now after we have hold remo so let's run this guy and what is the state you see it here not a big surprise the state is now deleted so we have detached no idea what you're talking about edit oh you edit something but i haven't written it to the database unchanged okay i've written everything to the database no changes that i currently know of modified there are outstanding changes i ca i have something to update in the database and with the remove we are changing the state here to delete it so we can note that down here delete it this is the next state that our entity goes through and finally we can call the save changes again and let's inspect for the last time the state let's run this guy okay and the state at the end is detached again so we can change that one it's detached why well the new dish is no longer part of the database because we have removed the dish so if you take a look we started with detached before we added the object to the database and we ended with detached because in the middle we removed the entry from the database so these are the entity states which you can go which which which an entity within which an entry i'm sorry which an entry can go through while working with entity framework and you should remember this method entry here because it is really important and it gives you access to all the change tracking information that entity framework gathers behind the scenes by the way this behavior this change tracking behavior is on by default you don't need to enable it when working with entity framework however there are situations where you would where you might want to disable change tracking because of performance reasons i will tell you more about such situations in a few more minutes okay but for now don't worry the the behavior that i showed you now with entity framework is the default behavior which you can expect when just working with entity framework out of the box performance optimizations are a different thing we will definitely take a look at such examples a little bit later on good what else can we do with this change tracking information let's start with a new function a new experiment function where we take a closer look how entity framework really does its magic behind the scenes so let's create a new method static casing task of this one here change tracking and again we take the cookbook context factory as an input parameter exactly like before and we say using var db context context equals to factory creates db context okay just like before but let's call this guy a weight chain fracking factory good we are ready to do our next experiments here whenever you need don't forget whenever you need pause the video and try to follow along so do not just watch the video you have to type in the code you have to run the debugger yourself in order to really remember what we are doing here just by watching me typing you will not get the most out of this lesson this is the reason why i do it in a video so everybody can work through the material or her on her or his correct timing just like like it fits to your existing knowledge and experience okay good let's create a new dish we have done this before let's do it again here we have a new dish and let's add this dish to the database i'm just copying statements here what we did before and let's do a save changes this one this is easy we are just ch adding another sample role to the database nothing special then again let's do a change okay so i copy this one with the bass here this one to this one now we updated our newly added dish now comes the interesting part this is just the same code that we have written before let's do the following let's see war entry dbcontext.entry we have used this entry before this is exactly the entry that we used in our previous experiment you see it here to get to the state but now i'm storing this object into a variable in order to be able to take a closer look on what this entry can really do if we take a look at this entry we get we get a bunch of interesting properties and methods i don't have the time to dive into all of them you can take a look at the details in the documentation i would like to specifically take a look at the original values property original values and here i will add [Music] name off dish dot what did we change notes this one and then i will say to string and here i will say var original value value maybe the word original value already points you to the solution that might that that entity framework has for change detection what it does it always saves the original value that it knows that came from the database or has been written to the database previously so in the original values entity framework gathers the original values as they are in the database and when we change something on the plain old clr object and when we call safe changes later on it simply compares let me zoom in here it simply compares the original value and the new value which is in the c-sharp process it checks the differences between the thing that is currently in memory and the thing that is in the change tracker in the original values and if there is a difference it knows that the data has been updated it has been modified it can check for each property what the original value is and what the new value is and with that it can build the correct update statement that is run against the database okay let's try that let's set breakpoint here and let's inspect the original value what i would expect is that the original value is still far because this is what we saved to the database here and the notes the current value should be baz and with that you understand that it recognizes that something has been modified let's run this guy and here we are in oh i don't need this break point i want to run to the second break point and yes here we are um you see it oh that's that's not easy to see let me quickly pin this guy here and put it maybe here and zoom in a little bit here this is the original value okay bar it came from here the new value is baz and with the next save changes it will write the new value only the node's new value because here we have the difference between original value and current value i hope this makes it a little bit clearer how entity framework detects changes and generates update statements we are nearly through with this example but i would like to show you how this change tracking is very much connected to a single database context and you have always to keep that in mind you might currently not know what i mean with a single data context let's wait for another few minutes write some lines of code and then i'm pretty sure you understand what i mean because what we will do here now is we will create a second database context you see i will just set up a new database context let's call it db context 2. okay in this db context 2 here this one we will now try to um read yeah let's say we will read the dish from the database var dish from database and we will say db context to dot dishes dot single a d oops d goes to d dot id equals new dish that's the dish that we added to the database dot id okay that will read our dish from the database and i will do exactly that statement here and here but up there i will use the original data context and down here i will use the new the second data context let's put a two here too and then we will compare these two things and think about think about why the world is as it is that's right okay now here uh i will clean up a little bit the dish from database let's put it here i forgot something i'm very sorry i have to say await here of course i forgot the awake i'm sorry let's run it again good now here you see the dish and i will pin the dish from database notes here and here i will also do a pin here and i will again pin the notes here good now let's compare these two values and think about why they are as they are let's first focus on the first result the interesting thing here is that we read the dish from the database we re-read the dish from the database because we added it here and we were just filtering on the primary key as you can see it here but the interesting thing is and please keep that in mind that we although we are re-reading the dish here with this single async we are not getting back the original value bar but we get back the new value bez which is only the value that we have in memory and we didn't save it to the database so the important thing is when you ask entity framework to read data from the database and it knows that this data is already in its change tracker the the identification is done based on the primary key it will not give you back the value from the database but it will give you the value from its change tracker and therefore even unwritten changes like this one bars here are returned when you use a query which returns some data from the database in the second example we are using here you can see it as separate data context and when we do a single async here we will not get back base but we will get back bar because this data context down here has no idea that there is an object up here which is tracked by the original database context so the whole change tracker thing is not a singleton across all data contexts but the change cracker is really specific for each data context and this is important if you have in your application multiple data context you can be sure that changes are only written to the database with save changes age stunk which you did to this specific database context if you need to do something else independently you have to create the second db context and when you call save changes there it only writes its changes to the database so the database context isolates the changes and always knows which changes to write to the database that's not that easy i know that i hope you understood what i mean when we go to asp.net and web apis we will definitely go back to this and then you will have an even better understanding of what it means change tracking by database context but for now you've got a little bit of an idea of how it works some of you might even fully understand it if you haven't fully understand it but you only have a rough idea that's enough that's good rough idea is fine we have still some weeks to to work on entity framework if it's completely unclear go to the discord server or to teams and there we can discuss your questions or i can help you debugging your code so that we can find out um what's really going on behind the scenes to make sure that you understand what is meant with this change tracking and this data context isolation that's good we have our entity states this one and we have talked about change tracking i showed you that we have the original values and we discussed various scenarios with different database contexts let's go to another topic that we have and this topic is attaching entities it's we are still in the context of the change tracker so no completely new topic now but just another angle on looking on the change tracker okay now let's copy again our method here this time we will call it let's call it hatch entities let's put it that way and call this guy here a weight attach entities factory okay that's good yep i like that now let's copy a little bit of code uh we will copy the create database context the new the add and the save changes that is that is good the question that i ask here is can we make entity framework somehow forget an object that it already knows can we tell the database context hey database context the object that you have in your change tracker forget it it's not your job anymore forget about it make this object just like you have never seen it before and the answer is yes we can do that we can force entity framework to forget about entities that it's previously new so let's let's do that let's say dbcontext.entry we know that already new dish this one and then we can say state but the important thing here is we can not only read state but we can also write the state and say entity framework the state of this object is now detached that means entity framework forget about this object it's not your job anymore sometimes this is useful yes and there are scenarios where you need exactly that of course you cannot just change it to detach you can also change it to modified and things like that but that's a different story we will not go into details here so this is entity framework for get the new dish object do like you have never seen it before let's take a look whether this works okay let's set a breakpoint and run the app and here we are and if we take a look at this one here let me quickly call the uh quick watch where it is here is the quick watch then we will see that we get an experience that we get an exception exactly that is what is expected because new dish is no longer part of the change tracker so therefore it's detached it's unknown if we take a look at this state here i've not saved it here but yeah should we yeah let's let's get it so let's say state var state equals to let's read the state and let's check whether it works here we are the state is as expected detached exactly like we had it before just as if entity framework has never seen this object now imagine a situation where you have exactly that you have an object that did not come from entity framework imagine for instance you are writing a web api and somebody sent you an object a customer an order or in my case a dish via http into your web api now this object in our case new dish is completely unknown to entity framework can we still work with this object can we for instance say db context dot dishes dot update new d now that is kind of interesting because the update method why do we need an update method here previously i've shown you that we can update something by just assigning a value to a property why do we have to call update here well the point is that this update method here will take an object which is not part of entity framework change tracker and add it to the change tracker it adds it to the change tracker but if we call let's do that dbcontext.save change is a syn and if we then take a look at the generated sql statement we see something pretty astonishing you see it here because entity framework does not know that this object already came from the database it has no idea which of the properties might have changed and therefore if we give it an entity and an object that it doesn't know previously just to make sure entity framework will overwrite the entire object in the database it will not diff the changes it will not read the object and then find out what has changed and only update those things that have changed that we've seen before but because we add the entire object to the change tracker it overwrites the entire object so this is very useful and you will see this technique hauling update on an object which is previously unknown to entity framework you will see that later in the year when we build asp.net web apis where we get our objects over the http protocol in the json format okay this is important this is attaching entities last but not least i want to show you the last experiment related to the change tracker let's call this experiment no tracking okay and call this experiment no tracking factory good and inside of this node tracking we again create a database on text good now if we would like to get all dishes from the database how would we write that var dishes equals to a weight db context dot dishes nowhere class we want to have really all of them so we just say two array async that is the analogy to a set select star from dishes as we would write it in sql okay let's set a breakpoint here and let's check in the output of if i'm really correct so if i go here then you will see oh i'm sorry i did this is the wrong break point i have to run it now i'm here and if you take a look well it didn't write a select star but as you can see here it included all the columns id notes stars title so it is essentially a star select star from dishes just as i told you before so this is what's happening here but what's also happening here by running this statement all the dishes from the database table are added to the change tracker so entity framework stores all the data that came from the database a second time it is not just in your collection here but it's also stored in the original values in entity framework and that's quite a bit of overhead it's necessary because we want entity framework to do its magic and generate proper insert update delete statements therefore it needs to understand what has changed and therefore it needs a change cracker but what if in this scenario we don't need to track changes because we are not going to do any changes maybe we will just take these dishes these dishes and use them as the basis for some kind of report or just print them just generate an html page for instance with all these dishes on it we will never ever make any changes to these objects can we tell entity framework that we are not interested in putting these object into the change tracker the answer is of course we can in this case you should always just before the two array async or to list async all the as no tracking here as no tracking means that the dishes that are returned here are not added to the change record so if you would do some changes here and call save changes soon nothing or bad things will happen because they are not tracked that's the point here okay so if we take a look at the state let's do that one here and not let's not say the new dish but say dishes zero let me quickly check whether i have some records in the database yes i have records in the database so it should return something if i run this guy then everything should be fine and if i take a look at the state you see although this dish came really from the database the state is oops sorry the state is still detached because we called it with as no tracking so this is an important thing if performance matters and performance should always matter to a certain extent if you only read something from the database and you don't intend to change the object always add something like as no tracking there are additional methods with additional parameters you can read the details in the documentation but for this course you just have to remember as no tracking in case of read only scenarios good we spent the first hour talking about change tracking what are the most important things that you have to remember in order to pass this course definitely have to understand what these entry states are all about because maybe not now but in a few weeks when we do asp.net core programming we'll definitely need to talk about these states again and you will need to be able to apply these states and to understand what the states are you should have a basic understanding about change tracking i may be asked will maybe ask you in an exam in a theory lesson how does the change tracker and entity framework recognize that a certain property on an object has changed and your answer should be entity framework remembers the original values which came out of the database and by comparing the current value of the objects with the original values stored in the chain tracker it recognizes which properties have changed so this is something where you should remember the concept of how the change tracker works with original values the attached entities area here you don't need to remember that yet but we definitely will need that when we come to asp.net core but i will remind you don't worry the last thing which is more important currently is the as no tracking because in the next exam i will maybe ask you to write a little bit of database code and then when i take a look at your code and i will calculate your grade i will definitely check whether you applied as no tracking for those queries where you just have a read-only scenario and you do not need and don't want to write anything back to the database if you forget the as no tracking it's fine it will work but it will not be as performant as it could be so remember the as no tracking with that we have covered the first topic of today's lesson so now it's a very good time to take a short break so maybe pause the video if you have some problems understanding the material come to discord or teams and ask me otherwise fetch something to drink take a short break and in a few minutes come back and we will continue with the next topic welcome back welcome back after the break we covered before the break the change tracker and now we are going to start with a completely new topic related to entity framework executing raw sql queries for that we will write another experiment method so let's just fold these methods and add i will add it up here add another experiment method let's call it raw sql and let's call this experiment a weight raw sql factory so we can do we can have a little fun here with entity framework first thing again we will create a database context just as like as we did before and now i would like to show you how you can execute raw sql statements because sometimes you need exactly that entity framework is pretty smart in generating sql statements for you but sometimes you really want to go down to the metal you want to write every single letter of the sql statements on your own because maybe you have to use a specific feature of the underlying relational database management system something like i don't know a with loss in a sql query with microsoft sql server for instance something like that is not or not completely possible in entity framework and therefore you have to have a possibility in those rare cases to write the sql statement on your own and entity framework fully supports that let me show you how this is done what you do if we want to query some dishes with the raw sql statements is you go to the usual dbcontext.dishes here and then you have a method which is called from sql raw this is the first method that we are going to take a look at and now here i can write select star from dishes i can write a handwritten sql statement and from there on everything works as usual so this is the beautiful thing here i am still in the world of entity framework so i will still get back some vicious objects i can still use async programming i also still have the change tracker so the objects that are returned here are tracked by entity frameworks change tracker but i can tweak i can optimize every small little piece of sql by just specifying the raw sql here don't get me wrong this should not be your preferred way of writing sql queries your preferred way of writing sql queries is still link language integrated query but in every non-trivial application that i know you have some situations where link is not sufficient let's set a breakpoint here and run this guy and let's see whether it really works we are still here on the wrong break point let me get rid of this break point and continue on and if you take a look now here in the uh log here you see that we have now hand written the sql statement so the the characters that we typed in found their way into the sql database and if i take a look here in the dishes you still see that we get nice little plain old clr objects with all the things that we specify this is what we get from sql raw first option next option sometimes you cannot just have as a constant sql query sometimes you have for instance some parameters let me show you what i mean var filter equals to percentage z and then we will copy the statement from up here but this time i'm going to say from secret sequel interpolated now what is sql interpolated it expects an interpolation interpolation is the c sharp feature with a dollar up here where you can use these template string templates so you can say where notes like and then you can specify here the filter parameter understand what i mean so in this case the filter parameter it might be an input from the user so maybe the user has selected that the user wants all the notes that end with the c because the percentage is a wildcard character here it means anything any characters before and at the end i want to have a z okay and this from sql interpolated it takes the filter that the user maybe has entered in a web form or maybe in a win forms or wpf app or console app and inserts it exactly here let's try that and let's take a look at the generated sql query good let's zoom in and if you take a close look then we see exactly here that we have the like statement and this time it didn't take the the constant percentage c and built a kind of string representation but it still used a so-called parameter and this one this one here is super super super super important this is the correct thing let me show you the worst thing that you could do the worst thing ever so if i take this command here and if i say from sql raw and i say from oh sorry not from where notes like and then let's let's stick with the uppercase letters here something like this this is bad bad bad bad bad bad bad bad this is bad bad bad this is called a so-called sequel injection you should never never ever write code like that yeah a kitten dies whenever you write code like that i mean thousand kitten dies if you write code like that so don't do that let me show you why if i run this code and we take a look at the generated sql statement here then you will see that we now have the parameter which might have been supplied by the user so the user has an entry field and the user can enter something there and we end up with the user entry injected into the sql query you know what i would do as a user i would simply say let's do something like that drop table xyz this here is now injected into our database understand what i mean so i can try that i can say delete from dishes something like that so if i run this guy here yep let's take a look so this is what we now executed so i just deleted a lot of data from the table called dishes and that's definitely not what we wanted i understand what i mean and this is why we call this a sql injection if you take a look at that one from sql interpolated it works completely different because here the filter becomes a parameter a sql parameter if i specify something like this it looks for a note containing the text delete from dishes delete from dishes is never part of the sql statement so remember that it's bad bad bad bad bad and you should never ever do that okay of course all the other things work just as expected so don't forget that you can do something like as no tracking if you don't need tracking information and things like that last but not least let me check that one okay everything's fine let me check this one i would like to show you a raw sql statement writing data not just reading data that will be our last experiment here with the raw sql with writing raw sql it works like that you say a weight dbcontext.database.execute sql raw and then you can specify any writing statement something like delete from dishes where id not in select dish id from ingredients something like this so we are using a delete statement here to write data and finally what's the problem here let me see int does not um execute data context but let me see something is not good here and i can't see it we don't have any parameters execute secret oh i sync of course of course i'm very sorry this is what i what i've got here now it should really work but uh yeah let me get rid of that one it was just for demo purposes and you should never never tell anybody that i've written that code um we don't want to do that so let's get back to this execute sql roy sync if i run it if i go down here you will see that we now executed a writ a write statement in this case a delete statement to the database so it was pretty simple to delete all the data from the database and if i run this again data is gone just as i expected so what should you remember from this short demo remember that you can use from sql raw if you have a completely constant sql query use from sql interpolated if you have a query which contains some parameters and last but not least use execute sql raw if you want to execute a statement that writes data but does not return rows like in a sql query and never forget this is very very bad sql injection it has to stick in your mind that you never ever try or do this this mistake it will be it will lead to very serious consequences if you do good that is what i wanted to show you with raw sql so let me put the code here take the time write the code do the experiments maybe take a sip from your glass of water and be back in a few minutes and then we will continue with transactions see you in a minute welcome back let me quickly remind you if you have any problems or questions regarding the concepts or the code please don't hesitate and come to the discord server or to teams meeting and we can't discuss your problems or questions there i'm waiting there for you the whole idea of this way of teaching is that you can work through the video on your personal and your personal pace and whenever you have problems i can concentrate on helping each one of you individually okay so don't forget that we will now continue with the topic of transactions transactions are luckily not that complicated with entity framework so we will be rather quick and let's see what's what's going to happen you know the drill so far we will create another helper method i'll copy these three lines of code close it here rename it let's call it transactions and all this guy awaits transactions with the factory good this is now our playground for playing with transactions let's do something which is very simple let's add a sample rule db context dot add sorry dishes add new dish and let's add title again foo and notes is bar okay good and add let's save our changes db context dot save changes async and don't forget the weight statement here at the beginning good after this save changes we do another statement and i can quickly take a copy of this execute raw sql something like this and now i will do something really bad i will say select 1 divided by 0 as bad 1 divided by 0 is not a good idea it will obviously result in an error but the point is why i'm doing that is that i would like to have a database transaction across the entire up sequence of operations issued against the database here so either everything now marked in blue is written to the database or nothing that's the point of a transaction okay and i would like to show you how you work with transactions with entity framework the first thing that you do is you put your entire hold here into a try catch lock in order to catch the exceptions sql injection exceptions if we have a problem we will just print something on rapunzel era the right line um something like something bad happened and let's put the message here exception message okay looks good good now we have a try catch block but this does not prevent us from writing this row here into the database even though afterwards an exception happens okay so what we would like to do is we would like to span a transaction over the entire tri-case over the entire tribe look and we do that like that we say using var transaction equals to ddcontext.database.begin transaction a soon see don't forget to wait of course this will start the transaction and when we are done we will see we will say await transaction.commit async of course now let's zoom in a little bit and discuss that here we are starting the transaction this is our transaction block we want to either write it completely to the database or not at all and here we have the commit statement so if the code with the commit statement is reached everything is written to the database but in our case something bad will happen so the code will jump out here oops sorry the code will jump out here it will go down there wrong direction it will go down here and continue with the exception that you see here so the code because of the exception will never reach the commit statement and therefore the transaction will get out of scope without us having called commit and that will lead to a so-called rollback action you heard about commit and rollback in your database classes rollback means that the entirety of all change operations to the database are rolled back are undone and therefore even the first add statement although we call save changes sync will never be written to the database so this is very typical code where you have a sequence of database operations which make which belong to a common business transaction and you put it into a transaction to either write it in its entirety or not at all not that difficult i hope you understood what we did here you should definitely remember how you work with transactions because it can easily happen that during one of your exams where entity framework is an important topic i will give you some kind of exercise where you have to use the transaction um to recover from errors for it good that was a quick one transactions we are good on them now we have a few more minutes time i would say half an hour maybe a little bit less so for the last half an hour i would like to discuss a little bit how querying works because we talked about the change tracker it was about writing data we talked about transactions it was about writing data parts of raw spoil was also about writing data but i would like to spend the last 30 minutes or so talking about reading data and now comes the important part the thing that i show you now you don't have to remember the details for this course it's pretty complex what i'm going to show you now you have to it would be ideal if you take away at least the basic concept of what i'm going to show you here don't worry the thing that i'm going to present now will not be part of any upcoming exam in this year maybe this is something for the pros okay i can't expect that you understand and use all the things that i'm going to show you now but it would be a good idea if you try to try to understand the concepts because if you understand these concepts it will be much clearer why entity framework behaves like it behaves okay it's also for the last half an hour not absolutely necessary that you follow along typing the code you can also watch me typing the code and try to understand the concepts but i encourage everybody who wants to become professional programmer professional real professionals a short program try to follow along try to type in the code and play with the code okay so warning um warning set with that let's take a look at so-called expression trees static asynctask expression tree you know the drill hookbook context factory there it is factory let's create using more db context equals to factory dot create bb context we are good with that one and let's call the expression3 method with our factory and we have our playground here we can start doing the interesting work again you already know that let's add maybe a little bit of data let's add our new dish and let's save the changes good uh and in the middle of course we have to add the data good looks good so that gives us at least one data row inside of our database now let's write a small little nice query and then think about how entity framework can do what it does in terms of querying data why dishes equals to await tv context dot dishes dot where and in this case i would like to say give me all the dishes where the title starts with an f okay and then we will keep it simple we can just say two arrayasian really nothing special okay let's set the breakpoint here and run this guy oh we don't need that breakpoint i am interested in that one let's take a look at the query oops that was generated by entity framework let me put it here and let's zoom in a little bit let's see whether no oh yeah i can zoom yeah please take a look especially here and here starts with and now let's take a look at this sql statement and please note the where clause here and recognize that the startswith method call here is translated by entity framework into a like clause with f percentage i told you before that the percentage value here is a wild card so like f percentage results in exactly what c sharp starts with does give me all the titles let's start with an f and are followed by zero or n other characters this is what we do here the question that i would like you to ask yourself is how can entity framework do that let's quickly think about what's happening when we write such a link query this link query here on the left hand side is translated by the c-sharp compiler into intermediate language and this intermediate language is then compiled by the just-in-time compiler to assemble a language and then it is executed somewhat in memory you could think that this is the case but in our case this is no longer true somehow magically the entity framework jumps in and prevents this statement from being compiled to machine language it doesn't make sense that this statement is translated to machine language because we need to translate it to a different language we need to translate it into sql into this representation so somehow some in some way entity framework has to have the possibility to inspect this c sharp code and from this c-sharp code understand what this c-sharp code means and translate it to sql the question is where does this happen and when does it happen you could think maybe that this is done when compiling your c-sharp application maybe entity framework somehow jumps into the c-sharp compiler and says hey z-sharp compiler i'm entity framework and i would like to translate together with you this link query chill out you don't need to translate this one to machine language i will translate it to c you could think that entity framework works this way but it doesn't it does not compile your query at compile time so the c-sharp code is still compiled by the c-sharp compiler and then at run time somehow magically i'll just show you how entity framework manages to translate the compiled c-sharp code into c how is that done let's take a look at that i will pick out just this content of the wearables here let's copy this one out in the uh a few weeks ago we talked about functions and and actions and things like that and now let's let's remember how this worked we had this type which was called thumb and if you take a look at this thing here it is called can remember it is called how uh correct lambda expression so this is a lambda expression and the type is t is the dish and the where clause needs a boolean parameter in order to do the filtering so it is a funk of tish and pool let's call it f and then we can just add the london method here this is what we typically know as a lambda expression okay and this is what we have used over and over and over again in the first few weeks of this course we are fine with that but if we run this method again around this in the debugger again and if i hover over this f then you will see oh i cannot zoom in here i'm sorry i hope you can see that too no okay i cannot draw here we'll keep it like that then you will see that this f is just a method it's a compiled it's a compiler generated method name a very strange name main dollar b and so on and so on it's a it's a method which has been compiled to intermediate language and from intermediate language to machine language and believe it or not entity framework has no idea how to translate this method into sql it cannot take a look into intermediate language at machine language and somehow magically get out the call of this starts with method and interpret it into a like query it doesn't work like that in the background c-sharp has a different concept here let's copy this line here this one and let's put an expression of fun off around this guy and rename the variable we will use a u we will need a using statement here and now we have exactly the same lambda method two times we have it here let's put it that way so you can really see that it's exactly the same where the variable is declared to be a function of something and then we happen for the second time here where this x variable is declared as being an expression of function and let me show you in the debugger what a huge difference this makes that's right let's hover over x here and now we see something which is really impressive okay i cannot zoom in here so i have to leave it like that this x compared to the f is no longer a method you see f is a method it's compiled but if we take a look at the x somehow the debugger during runtime can tell me what the original c sharp code was what i've written so with this expression thing the entity framework has a chance to take a look at the code that you have written in c sharp but take a look at this code at run time we can try to inspect this a little closer you see this x it's a so-called lava expression and it has a body you see that one we can open this one up and we will see that c-sharp at run time not at compile time tells us hey rhina this thing calls a method which is called starts with i can take a closer look and can say what is the object that i call it on well the object is in this case a member access because d dot title is a member access and which member is accessed the member holds title you see expression of function of something means that the c short compiler is no longer generating machine language but it is generating a so-called object tree describing the c sharp code that you have written and and tools like for instance entity framework they can inspect this object tree at runtime and translate it into a different language in our case into sql so entity framework really takes a look at this x and somehow recognizes that this calls the start with method and it has logic built in that says the entity framework whenever you recognize a string dot starts with then please translate this into a like f percent like something percent that's the point here the magic are these expressions and when you take a look at how these uh for instance how the where clause here how the where statement is defined i can press f12 to go here then you will see that this where clause takes exactly what i just told you it takes an expression of function off that means that this where clause says i'm not interested in the compiled code compiled down to machine language but i am interested in an object tree where i can take a look at the object tree and translate that to a different language in our case sql if we don't have a database table behind the scenes so if i copy this guy here this one and i just bar in memory dishes and i just create an in-memory array of let's say again dishes let me quickly generate some demo data here okay now i have an in-memory representation and this time i will not go to the database but i will just say in memory dishes please note that here the lambda and here the lambda are exactly the same but here we have entity framework behind the scenes with all this expression stuff and here i have classical in-memory link with an array of something without a database behind the scenes of course i don't have async and if i now take a look at that where then you see on the right hand side that this does not take an expression off function but it takes a function so the c sharp compiler knows that in this case there is no database nobody is there who could execute sql so it doesn't make any sense to compile this to sql the only thing that makes sense is compiling that to machine language and executed in memory while when we have up here ntt framework and the database it absolutely makes sense to translate that into sql we to translate it into sql because otherwise we couldn't send it to the database and therefore lang therefore entity framework uses this expression of function magic complicated kind of i know it's a little bit of mind twisting if you hear it for the first time with all this funk off and expression off and something in memory and something translated to sql i fully understand that we could even go further the real professionals they build these expressions at run time and maybe if we see each other next year in the next class and we have practiced a lot many months in c sharp this year i can show you how to build expression trees on the fly at runtime but this is even more complex for now i just want you to understand the role of the c-sharp compiler when you have a database compared to when you run things in memory if you have a database it uses expression trees this object tree describing your c sharp code so that entity framework can translate this code into sql and if you have something in memory no expression trees just compiled lambda expressions compiled down to intermediate language compiled down to machine language with that we close today's lesson let's quickly summarize what we have learned today we started with some experiments regarding to change tracking in let's zoom in a little bit then we can do a better thing here we started with some experience regarding the change tracker entity states and things like that then we had two short ones the first one was raw sigma how to send raw sql statements reading and writing to the database remember here we had a very important thing sql injections never do that the third thing were transactions transactions are the classical database transactions but this time we wrote it with entity framework to make sure that either everything is written to the database or nothing is written to the database in case of errors and last but not least we used a little bit of time half an hour or so on expression trees so you get a at least rough understanding what's going on behind the scenes how entity framework manages to create sql statements out of your c output i hope it was an interesting lesson we have specified the time when we meet again in the discord server no sorry in the teams meeting then we will discuss all your questions or your understandings we will make sure that everybody um understood what we covered so far but with that for this video i want to say thank you see you next week

Channel: Rainer Stropek

Views: 6,598

Rating: 4.9804878 out of 5

Keywords: C#, CSharp, .NET, .NET Core, .NET 5, Entity Framework, EFCore, Databases, HTL Leonding

Id: o9XoiPPP2Lw

Length: 85min 2sec (5102 seconds)

Published: Wed Nov 18 2020