Entity Framework Best Practices - Should EFCore Be Your Data Access of Choice?

if you listen to Microsoft it seems like entity framework is the only logical choice for data access almost every data access example uses entity framework yeah if you've watched any of my videos you have noticed that I do not use entity framework in fact I actively discourage people from using it I do get a lot of pushback about the decision I also get questions about it the performance improvements of EF core make using entity framework finally worth it in this video I'm gonna review a very basic entity framework core set up and talk through some of the best practices to implement and the pitfalls to avoid when setting up entity framework by the end you should have a better picture of what it actually takes to create and maintain an entity framework data access layer in your application and here's a quick hint it's more than it seems like when you first start now I'm sure you'll have an opinion on some of the things I'll share in this video because I'll be sharing my opinion I love to hear your opinion let me know down the comments what your thoughts are but let's agree on some ground rules first and foremost please be respectful of each other angry ranting and insults will get your comments removed and you may even get banned from this channels comment section if someone's opinion of how to best develop c-sharp applications makes you angry I recommend that you really your priorities in life second if having a disagreement please state the reason why you disagree clearly we can all grow improve in our understanding of software development if we have calm informed disagreements finally be willing to disagree or even to be wrong both are okay I know I've grown as a developer because of the things I got wrong and that's someone called me on now if this is the first video you've watched of mine my name is Tim quarry it's my goal to make learning c-sharp easier one of the ways I do that is by teaching context there are logit orioles out there that teach you what to do go a few steps beyond that to show you when you should do it why you should or should not do it what pitfalls to avoid and what the best practices are this video is a prime example of me doing just that now basically I get you ready for the real world now if that's the type of training your interest in subscribe my channel hit the little bell icon to be notified my release new videos they come out about every Monday and Thursday finally in the description there's a bunch of links there's Lansky the source code for this video to sign up for my mailing list to join patreon to get access to my courses and all the links that I mention in today's video now if you're looking to make a career out of c-sharp or a better in your c-sharp career my content can launch you forward faster check out all they have to offer at I am Tim quarry comm or SAP my mailing list to hear from me directly alright let's go over to visual studio and setup our environment now we're going to start with is a web project so it's create a new project will create a SP neck core web application hit next and let's call this EF demo web how about that EF demo web and then the applique tour a solution name is EF demo app it'll hit create an reselect a web application which is the razor pages application with dotnet core and asp net core 3.1 no authentication yes HTTP no docker okay this is just to have some sort of UI to work with with our entity framework so I say create and one just loads up we can close out of this this is a very basic project you've probably seen about 184 but essentially it's just a web application if we run this we should get a start page with just a privacy policy I believe so here we go make sure it runs it does EF demo web there you go woohoo now the next thing we're going to do is add a class library so right click on the solution and say add new project era select class library dotnet standard so that's my recently used templates there for all to select it but you could search here for library and just make sure that the library choose is dotnet standard okay that's important not dotnet core not dotnet framework especially dotnet core worked just fine but where I choose dotnet standard because that's the default library right now that you should use and the reason why is because dotnet standard will work with dotnet framework projects it will work with dotnet core projects and it will work with xamarin products it will work with pretty much any project in the Microsoft ecosystem so it's it's kind of a a very very versatile class library whereas dotnet core is a little more limited it won't work with dotnet framework so dotnet standard for now is the way to go hit next and we'll call this our EF data access library hit create and we delete class one okay with that being done we have our class library let's add a reference well what wait to do a reference do we actually add some classes to it so let's add a folder to our class library and we'll call this models into the models folder let's add a class and let's call this person start you know let's start with address first is there a dismal address not address model that's a little bit different naming convention but it's more in lightly used with Antony framework I do try and conform my conventions to what I'm using okay so let's create a quick model so prop tab twice to get our property and where I start off with an ID and then we'll have a string for a street address and we'll have a string for city a string for state and a string for zip code now you may wonder if you're in the u.s. a zip code looks kind of like this you know five numbers and so it seemed like that'd be a number not a string but a zip code can also look like this and that is a string and then if you are in Canada I believe you have letters in it so Japan we are at this is not a number but even the US it's not a number because we have things like that is a valid zip code or close to it so leading to zeros that's not how a number looks that would be something like that but that's not a zip code as it goes five numbers so you have to pad it the first two so therefore each stores zip code as string we also don't do multiplication on zip codes or addition or subtraction therefore it's not really necessary as store as a number we store it as a string okay little side note there all right so there's an address model it's gonna hold one address let's add another class and let's call this email and they get public and an ID and we'll call us email address that's all it's got in there and it will have one more model and this is gonna be the person model it's a public class person and we have an i or an int here that's an ID and we'll have our first name last name an age and then a list of address model called addresses I'll make sure it stands that so you start adding to it right away and then a list of email called email addresses instantiate that as well okay so there's three models that hold related information so you have a person and a person has a first name - last name an age which typically wouldn't store age as an integer in your database this is for example purposes and then I have a list of addresses why would a person have a list of addresses well if you have a summer home and a winter home or if you have if you live with your your your father for half a year and your mother for a half a year or you know there's a bunch of different reasons why you might have multiple addresses your work address your home address again there's just a lot of reasons why you might have multiple addresses email addresses I have probably twelve of them that I actively use and I have more that are occasional use so one email address wouldn't be enough but a list of the email addresses would work out just just fine for me okay so pay attention those kind of things when you have your setup make sure you don't just have one address I've seen this done before we have one address and then later they go oh this person has two addresses so we have to add another property called address two and then another person has three addresses and before you know you have added us three and four and five and six and that just gets silly so don't do that that's where you have the lists for okay so let's now start setting us up to use entity framework so let's right click on our dependencies of our class library gonna manage NuGet packages and we're going to install microsoft entity framework dot sequel server I'm sorry Microsoft entity framework or got sequel server so we want to install this we'll set up to use entity framework with sequel server now there's other options as well if you want to use sequel Lite or my sequel or other options that EF core supports that's fine but let's go ahead and install entity framework core okay now as we're doing this I do want to point out that the the tooling here is really impressive and that's one of the things that people often get confused on when they hear me talk about entity framework they often think that I'm not a fan of the tooling and that's that can't be further from the truth but tooling is excellent it allows it to quickly stand up powerful data access code with very little work it provides great features such as database source control rollback and a lot more those kind of features so when we talking the tooling here I am really impressed the tooling that tooling is not what I have the issue it so as we go just try and separate your mind the tooling from who uses the tooling that's the issue that I have a problem with it's not just about the fact that what the tools do it's about how they're most often used and how much you have to note it make them use make them use in the right direction or make them used as efficiently as possible let's put it that way okay sorry my tongue a little tied okay so let's right click on our class library create a new folder we'll call this just data access was and in here i'ma create a class I might call this class the people context and context is a pretty common keyword for entity framework so public class people context and that's going to inherit from dbcontext control dot to add the using statement there so we have our our people contacts which is inherited from dbcontext now if first going to do is create a constructor so public people context I'm going to pass in DB context options I'll call them options and then we're going to call base options okay and so what this does is it gives us a constructor if you want it and it also calls the base constructor with those options have been passed in okay so we're leave us alone I'm not gonna actually fill that in with any code but I do want to have that set up now here's where we set up our essentially our tables so public DB set for type person control dots and our user to it there so for person it will call this people and this is a property so I should in a prop but get set like so so prop DB set and this is for address and we'll call this addresses and DB set for email will call email addresses okay so there's our three essentially our three tables you can think them as these are our sets of data so again it didn't take much get this set up and this is pretty much all we have to do on the code side too have entity framework configured well almost let's do that last code piece which is go over to our front-end and this is where I have my first squeamish moment my first moment going II I'm not sure I like that and that is we have a separate front end in order to finalize the configuration of entity framework now there have been some changes recently I believe have made this better and it might be you can get away with not having a front-end anymore they've been working on that but I'm not I have not seen where that is if you do know how to do without the friend I love to hear it okay but the way to do this is though add a reference to your library in the front-end and then down in startup we're going to configure this so in the configure services we're going to add our our DB context so up here we're going to say services dot add DB context and then we're say people context control dot to add that using statement so the people context and then we're going to say options and our arrow function here and inside here I configure options so options dot use sequel oops use sequel server control dot notice here using Microsoft entity framework core this has to know about entity framework or this is a front-end again not a huge fan of that the front-end knows now that we're using entity framework core and how we're using it so configuration dot get connection string and the connection string will be I'll just use default okay so what is this doing well this is configuring our or adding our DB context to our dependency injection system and it's configuring it to use sequel server and the sequel sir connection string is getting pulled in from our configuration which is this app settings JSON now you need to add that connection string connection strings plural and then here we can say default and have our connection string now I'm gonna use a local DB which you go a sequel server object Explorer you can probably find local DB and the cool thing is if you already had database you can grab that database let's just grab Oh doesn't matter delete use fine if I select delete me and say properties you already have a database over here one thing that does is you can copy this connection string Seaside's connection string right here you can double click copy and then paste the connection string in and it gives you all the good stuff here you need in fact more than you really need you don't have to have all this stuff the key that's what all you need is those three pieces of information the data source the initial catalog and the integrated security equals true okay so let's talk about these three things the data source is the location where your local DB is you can hand type this you don't have to do the copy and paste like I showed you you can hand type it but it is going to be different based upon your environment and this is mostly that trips people up they copy my connection strings and then wonder why they don't work well the connection string is based upon your installation of local dB if you don't have local DB and installed this won't even work if you go to tools get tools and features I'm not going to do it here it takes a little bit to load but that does is allows you to configure your Visual Studio and look for sequel server data tools as one of the options one of the big check the box and that will install local DB for you from there go to your View menu and make sure you select the sequel server object Explorer to open that up which that's right here for me and then you can connect to a server see this little add server button here and you get the punch in the credential it's going to look for items locally if I'm going to select this CMS local dB I can hit connect and it'll connect to it automatically or you can find other ones on the network or even a sure so once you connect then you see this name right here where it says local DB and parens / ms SQL local DB that's the name of my server that whole thing from the parens over to dB so that's the data source right here that's going to put there next we have the initial catalog this is the database now entity framework code first which is what we're doing or in the code first and have you generate a database code first starts with no database you don't have a database and so we don't want to use an existing database like deleteme we want to create our own so let's create our EF demo DB let's just check to make sure there's no EF demo DB there's an EF contact DB and EF data and that's it so there is no EF demo DB which is good because we're going to created alright let's capitalize that B so we have our new database will be called EF demo DB finally integrated security equals true this is saying that whoever is logged onto the computer use their credentials to log into the database automatically now it does not know your password it's not using your password it's using your authentication token essentially that's kind of the boiled down version of it so it's saying hey you've already logged into Windows and therefore going to use the fact that you've logged in you've told Windows who you are in Windows says yes that's what you are we're going to take that as a good sign that's who you really are therefore we use that person as the personal log into the database that's great for a demo environment but unless you're on a a local network that has an active directory you probably can't use that in production in production you'll probably use a username and password to your sequel server now I'll get back to that in a bit but that's a pretty common occurrence in fact you have to do that almost always when it comes to connecting to a database you usually you're connecting using a username and password not integrated security so we'll talk about the the downsides of Anthony framework specifically when it comes to that using a password but just note that all database connections use that username and password unless they using this integrated security on a local network alright so with that we've got a connection string we've got our DB context in our services and we're pretty much ready to go from a code perspective to start using entity framework but we're not done yet we now have to create our actual migration scripts and then create our database now what are these migration scripts let's make one quick modification let's actually get rid of age from it let's just say we've forgot to add that ok we'll come back to that in just a minute but now let's create our migration script for our data access library so you've got a package manager console if you don't have package manager console you can go to the View menu or and then go to other windows and down here is package manager console so open that up and this has access to entity framework and you can do things like this where you say add - migration and then give the migration a name so this first migration is initial DB creation ok I hit go and it's a oops not yet let's find out why and it says the ad migration is not recognized as a main of a commandment function script file or operable program why is that well if they have an ad the tooling to my project so on the web project again here's under willows places where I twitch a little bit right click on dependencies say manage new get packages go browse and search for Microsoft entity framework core dot tools this is the tools for the new get package manager console and visual studio alright so we will install these tools and what this will allow us to do is from the package manager be able to do the the add migration ok now that we're done that you go back a package manager run that same command again it's gonna build our project make sure it builds so if your project doesn't build this won't work so now it succeeded it says nope and it's saying hey your target project of EF demo web which that's our startup project doesn't match your migration assembly which is EF data access library this the next area where there's a little bit of a at which factor on my part by default entity framework really loves to be in your user interface it loves to be right next to your UI right embed in it in fact a lot of tooling around Web projects talks about Anthony framework now if you've ever worked through the idea of separation of concerns or loosely coupled applications or if you've gone through any of my content you will know that that's not really great because then your your project is your UI is your entire project it's everything and so if you decide to change your UI you pretty much have to change your entire project and that's not great now you can probably reuse some code copy and paste whatever but it's it's a major overhaul to replace your user interface where if you have that separation of concerns where you user interface doesn't really care where your data comes from it just says give me data and your data access layer says oh here you go and it gives it to it then if you go to replace your user interface it's just that top layer you're placing you're not replacing your business logic you're not replacing your data access code and that's where I'm not a big fan of how this was originally designed and built now there have been improvements around being able to separate these two notice I have put entity framework in a class library and as soon as I changed this drop-down to say default project is EF data access library I believe this should now work but there we go so it's done but it's those little things where it's it's actively encouraging you to put in the user interface because not putting a user interface is just a little bit harder and that's one of the concerns I have and here's my biggest concern with entity framework it's that you have to know a lot in order to be an entity framework developer in fact I would say that you have to be advant you should be not have to be you should be an advanced level user before you start using entity framework the problem is is that entity framework is so easy to start that it becomes a beginner level data access ability and that's dangerous and that's where I have the problem remember before I said that tooling is excellent it is the thule is excellent the problem is you it's design it really needs to be in the hands of an advanced developer not a beginner level developer and that's not to be a gatekeeper I do not like keeping I don't want to ever say you can't because you're not a special club what I'm saying here instead is that's very very dangerous to use entity framework until you understand some of the advanced pieces of it so let's dive into one of those advanced pieces and that is the migration script so what a migration script is is it's the it's the way the entity framework builds out your database so you start from no database and then you migrate to a database and then as you have more changes you migrate from your existing point in time of your database to a new point in time and that's what these migration scripts are notice that never we have migrations folder now and in here we have this new file that starts off as 2 0 2 0 0 1 1 1 0 3 3 6 1 8 underscore initial DB creation notice the end part there that was my name that I gave the migration that's the name right there so this is a migration and this is automatically generated code based upon our DB context our people context right here and the objects that our DB sets inside of it so what is this well there's two methods in here and this is pretty cool this again the tooling is great I love the abilities as tool he has it has two methods ones up and one is down now just at a at a random guess what do you think these do well the leap up migration allows you to go from where we're at to where this wants to take us the down migration is a rollback so if you've gone if you don't need up command and you go OOP that didn't work you can roll up back using a down command and that will undo what you did and that sounds awesome and reality is pretty awesome however just know that that down command isn't all rosy because in our example the down command for this migration script is to drop this table drop this table and drop this table basically delete all the tables in our fledgling database which is fine if you just created them but if you already had data in them that is gone so it's not like these roll backs are without cost there can be a time when you lose data because you do a rollback so just know it's not a silver bullet you have to be very careful doing a rollback it's not something to do lightly okay so that's the down command where it says drop these three tables that's the rollback let's look at what we do to create those three tables well we have create table the people table and it says it columns now all this code gets written for me which is great I don't have to write all this syntax or even know all that syntax and sometimes people say well you don't even have to look in here because who cares but here's the deal this goes into source control do you know why it goes into source control because you have to maintain it yes something else wrote this code for you but is your responsibility to understand it and know it because this is your data not Microsoft data this is your database if something goes wrong you're on the hook not Microsoft so let's read through it the people table we have an ID cool int excellent not null does Knoblauch is false cool annotation says sequel server identity one Kyle one what does that mean well by default what's gonna do is it's gonna make this an integer column which we already called an inch that's what just took from us but then it says this is an identity column because you call the ID and this identity column will start at one and count up automatically that's perfect that's exactly what I do so the fact that it did it based upon the fact that I just had int ID in there is excellent I love it that's great next one first name table column it's a string it's nullable okay that's an ideal because first name and last name can be null I don't want them you know will address have fix that in a minute but just note that's the defaults now constraints well we have a primary key and that primary key is based upon the ID so made an ID column a identity column and it made a primary key cool that's exactly what I would do address table well I got the ID same deal their street address it's a string column its nullable same as city state zip those are all string columns and those are all nullable again I would make these not null but besides that it looks good then we have this person ID well we don't have a person ID in the address column no there's no person ID here so where'd that come from well in the people or the person the person table or object we have a list of addresses so it's oh you have a list there well we're going to put the ID of the people table in our address table so it links cool and that's an ID and it is nullable meaning you don't have to have a person ID well that's kind of a problem why we ever have an address that didn't link to anything so that's another problem right there then we have under the same still addresses table we have a primary key cool let me have a foreign key the foreign key says from the addresses table link to the people table the person ID this is the almost exact same naming convention I use which again is awesome now part of that comes from using a standard naming convention because then your convention matches up with everybody else's but what saying is linked the person ID column in the address table to the people table the ID column excellent if the foreign key it's saying on delete restrict what that means is you can't delete a person if they have a linked address again perfect let's move on to the email addresses it's the same basic story we have our ID that's identity we have an email addresses which is a string it is null which again makes no sense because if you have a null email address then why you you can have an entry that's the only real piece of data here the rest is the ID of the record and the ID the person we're linking to so there's no reason to have a null email address but yes marked as nullable okay again primary key and a foreign key because this links back to the people table then we also have a index for the address person ID so we're indexing the person ID column in the addresses table and the email addresses table and what this does it creates an index so that it's quicker to query data based upon that ID so that's pretty good as well so that's what gets created in our first migration script now before we go any further I'm gonna come back over to the person table and say oh you know what I forgot I wanted to add age let's put age back in now what do I do because age is not in the table well I buy a package manager console I say add migration but give a new name add age column I create a new migration all right and so now I have a new migration so now we have a second one right here and this one says up is to add a column called age to the table people it's not null and the default value is zero down is drop the column age from the people table so now I have two migrations and we'll apply these in just a minute I just want to show you that you can have multiple these and you will have multiple of these as you make changes so whenever you make a change to one of the models that is one of your tables then just do a new add migration at the package meta consul and create a new migration for whatever changes you've made now there is one more value that has not or one more class we haven't talked about and that's the people context model snapshot now the very top it says this has been auto-generated so this is auto-generated c-sharp code and this is a model snapshot and so it has some set up here again you don't necessarily have to know all the information that's going on here but it is pretty important to understand how to read it because if we look here let's start with the entity the address model in it what's coming down here it says the property ID value generated on ad well that's the identity value in sequel that's saying the it starts at one accounts up every time that's the value Jenner Ana add method that's what's doing has column type of int cool has annotation and there's the annotation that says its identity column okay so that's at least understandable next we have the city property which is a string has column type here's we have to specify the actual sequel column type for city that column type is n VAR char max now if you're not familiar the sequel server and how it does the different column types let's talk through what n VAR char max really means so first of all n VAR char what is that well that is a Unicode variable length character field so Unicode is a wider character set than we have typically in the u.s. so in the u.s. this is our character set we used in this screen so you know a through Z upper case lower case and a few special characters but in the rest of the world there is a wider range of characters that are used for example any of your Arabic languages are don't do not use the ABC alphabet the same of the Asian languages and there's a whole bunch of other cases where the the characters go beyond ASCII ok ASCII I believe is 65,000 character to be something like that and that's just not enough and so what we do we use Unicode instead because Unicode can it's I believe millions of different characters including emojis if you want to have emojis so Unicode is much more popular for storing data that might have to do with any type of international or even just non ASCII character set so that's a good thing but does take up twice as much storage as ASCII stores or varchar' because unicode takes up two bytes per character whereas ASCII takes up one byte per character so just note that n varchar' will take up two bytes per character that's fine because really when it comes to storage anymore storage is usually not the limiting factor now typically you put a number in here from one to four thousand four n bar charm and that would indicate the number of I guess roughly equivalent to the number of characters that it can store it's not quite a one-to-one relationship there's a little bit of a a difference there but think of it as characters so one to four thousand characters would be in n varchar' column but what if you wanted more than four thousand characters well then you would have n varchar' max n varchar' max can hold I believe it's two gigabytes worth of text someone's put it's something like a hundred and forty one copies of war and peace written out something like that so very very very very very large however sequel server has a limit on the number of bytes it can have in a given row that limit is eight thousand and sixty so once you hit eight thousand sixty bytes in a row you have an issue and so what they've done is it said okay for n varchar' max and for varchar' max what we're going to do is if those are large values we will store them on the disk instead of in the database and we'll put a pointer to where they're at on a disk this makes it very easy for sequel server to stay in that a thousands 60 byte limit per row while still having massive amounts of data per row however it does cause some problems I know I'm getting a little deep in a sequel here but this is important to understand when working with entity framework so if you store a value that's a max value or a large value in this field it's going to store in a different location which means the querying for this row is going to be different in fact it's much much harder to do things like finding values inside of an envier r max because it's going a disk and so it may even tell you can't do it or it may be a much more expensive process for lookups now most likely for the city you will not put more than 4,000 characters in which case sequel server will treat it like it was however many characters you put in there so if you put 20 characters for your city it's equivalent of having n VAR char 20 for that particular row and column and you may say well then great Tim what's the problem well there is a couple of problems the first problem comes down to indexing remember back here we talked about the fact that down here it create a couple of indexes for our address table and for our email address Tayla based upon the person ID and the reason it did that is because is it makes it faster or can make it faster that's let's preface this it can't get faster to put an index on something you're going to be using in a query where your query had a where statement or something of that nature or you're joining on it what if we were going to query based upon the city a lot so that was in the where Clause a lot was it oh well let's create an index for that not so fast because it's an n VAR char max a non-clustered index has a limit of 1,700 bytes well let n varchar' max is well beyond 1700 bytes therefore you cannot put an index on this therefore you may have an optimization problem you may be able to optimize your database lookups based upon your columns because your columns are too big to be indexed even though the data inside them isn't too big so that's problem number one problem number two is one that it's not I don't I definitely give credit where credit is due I was doing some research on this and just asked the question hey is there a performance problem with n varchar' max because if we put 40 and you know 40 characters in there it's no different than n VAR char 40 right well the answer is no that's not correct so right down here Eddie did a little bit of work on this where he he figured out how to set up a bunch of different columns with a bunch of different sizes so we have n varchars 64 through 4000 and then max and I same as varchar' through max and then he did some query lookups and found something interesting and what was interesting is that the storage space is based upon however much you actually use however when you do a lookup it has to know how much memory to allocate before it gets your data so with this he found that n varchar' and varchar' columns are assumed to be half-full imagine how much storage space and capacity in memory we're going to take up for a column that is n varchar' max especially if as you go back to here we'll notice we have state as n varchar' max street address zip code and he come down here so is email address all n varchar' max essentially any string that we do will be n VAR char max by default how much memory are we going to use up well he did a little bit of searching and did some querying now I will provide us link in the description you can go and look and try it yourself and see if you get different results but he did it multiple times and even rebuilt it with row level compression and the results were the same so if we look at the NVR char max down here and do the query look at the memory usage in kilobytes 272 even though up here where we have the n VAR char 64 now a data is the same there's not a difference in what data is in each of these columns but just because a column type was n VAR char 64 we get a null value for memory usage that's because it's less than 5 kilobytes this is actually K beena MB up here but it was less than 5 kilobytes of memory usage but down here for the same exact query just a different column type its 272 kilobytes now usage percentage 2% so it allocated 272 kilobytes of memory on our sequel server for this one query and only use 2% of that memory that's a real performance issue because remember this is not very something you run once this is something that everybody that uses your application is going to be running so 5 kilobytes or less actually versus 272 kilobytes that's at least what 50-plus times more memory usage the same exact query for one field imagine what would happen if you had oh I don't know one two three four fields but had n varchar' max what could the performance implications be of this so that's the next thing I want to point out is that by default entity framework does not create the ideal table design for you it's expensive the table it creates and why is that well this is not because entity framework is dumb in fact it's just the opposite and the framers are very smart so let me say string street address how much data can you put into this this property as much as you want there is no limitation here it's it's massive the amount of data we can put in here we can load an entire JSON file into one string variable so if I were to store that in a sequel database how is it know what to assume that size should be it doesn't there's no way you can know how much that that property is going to hold therefore it has to cover all the possibilities and that means it has to say and varchar' max well that's not ideal so we're gonna have to change that so that's one of those things where I the Box it's going to burn you and it's going to be less performant and one of the things that I if I have taught it before and people have pushed back done is the idea that entity framework is not efficient when it comes to data access I get a lot of pushback on that and one of the big things was well with NT frame our core that's really changed it's it's really efficient and my response is no it's not it can be and if you write a really good optimized bit of code you absolutely can make this a really performance system however that indicates that you are an advanced level user that knows how to diagnose and improve queries in entity framework your prove my point to me that entity framework is not for beginners or intermediate developers this is for advanced developers who have a firm grasp on the ins and outs of entity framework because pretty much every demo you'll see online it'll look like this as I there you go creature model and then creature migration script and boom there you go you got your your application running and you have data access but again all four of these and varchar' max not a good thing okay next let's walk through here person ID is a nullable int don't like that now it does lack the has the key for ID field has index on these two fields that's cool down here provides the same story we've got n VAR char max we have our identity column here we've got our nullable integer and we're creating indexes our index our key and we're a science that able email addresses down here person again same thing pretty much and then down here we have our our linking so has one in the person it has many in the addresses it's the one-to-many relationship here which is good that's that's ideal so you have one person can have multiple addresses but each address is only linked to one person okay that's a one-to-many relationship all right same with email addresses okay so we've found some issues in our code but but that least walks us through now you have a better understanding of what this migrations folder holds and how it works okay for going any further we are gonna update these models make them make them better but let's go ahead and create our database we have done that yet so the package manager console again this time has say update - database that's it hit enter and what this will do it'll create our database now what database does it create well it creates the one that we specified in our startup class I'll go that in just a minute once this succeeds so the build has succeeded now it says done so we create our database so it came through in our user interface and it said hey your dbcontext oh you said you use sequel server cool and there's the connection string which is default so let's open up app settings dot JSON find default ok there we go we're going to create the database named EF demo DB in the local DB / ms SQL local DB database our server and where I use the integrated security which means whoever's logged onto the computer so EF demo DB let's refresh our databases here and find the EF demo DB and notice we have four tables we have people email addresses and addresses those look familiar in fact if we were to open one of these up let's go if you did here we'll look at that in just a minute but you have this EF migration history which actually starts with an underscore and what this does is it gives us a history of what's gone on in this database and it said ok I have run this migration and I've run this migration so it's run two migrations this happens to correspond to the two migrations I have over here and so based upon that it knows where it's at so if I were to run an update database again nothing would happen nothing would change in my database because it's already run those two migrations and so it knows that so it knows ok if we run off to Davis again look for any migration after add age column again really smart tooling but people table knows ID first name lasting than age again all nulls we have no records in this this table yet alright so we now have our tables but they're not ideal they're not designed really well in fact if we look the address table we go to the view designer we'll see that there's n varchar' max for all of these actually depressing let's change that from n varchar' max so let's start modifying this to work better so first of all street address this should not be null let's make up market required control dot to add our using steam up here for system component model data annotations now this will not be marked as knowable that's a good start now we need to mark it as not an n/bar char max so let's say max length is going to be and let's just say 200 okay so I start street address let's copy that and let's go to a city sage we required but it should be a max of 100 for our length state a state should be more like I don't know 50 you the the states that use for my demo or a sample data they're not state abbreviations they're actual states or you know names longer names so 50 is probably fine and then let's do the same for zip code and zip code let's the max length is 10 because we're use the US notation so it'll be 1 2 3 4 5 - 0 0 0 0 something like that that's our zip plus 4 syntax which that's 5 characters that's 4 characters and that - does count so that's another character as well so 5 plus 4 plus 1 equals 10 characters as the max our length should be now a zip code is not Unicode doesn't need to be Unicode there's no reason to store two bytes per character because it's going to be again reason the US based here but it's going to be a number from 0 and 9 per character slot or a - that's it those are all ASCII characters so let's change it to varchar' well how would you do that well yes a column control dot add using system component model data annotations dot schema and then you're gonna say type name equals VAR oops varchar' and then let's give it our length of 10 so now you're putting into your c-sharp code sequel code our sequel commands is that ideal no but that's we have to do if you want to be more efficient in your database what you do because your database the faster it becomes the faster it runs the faster your users get served and here's one of those things that often bites people with entity framework or entity framework or doesn't matter when you're using this in development it is all very very very responsive in fact we looked at that that web page before and I'll bring back over here and we talked about 272 kilobytes of memory you may have laughed at that because come on Tim 272 kilobytes of memory I have 16 gigabytes of memory on my personal development machine that's absolutely true if you have that much you will not have a problem in development and in fact your server probably has even more than that however how many users do you have or do you want to have in your application if at first you have 5 10 15 20 not a problem and so these problems will lie below the surface until your application becomes popular or until it goes in a production in environment that uses it has lots of users and by lots I mean dozens to hundreds now not even talking about thousands and millions just dozens or hundreds but once that happens once it goes into production and let's just say a couple hundred people are using your application a couple hundred people times two hundred and seventy two kilobytes of memory for one field times four fields that's a lot so all of a sudden now you're talking about maiya bytes and gigabytes of data being used by your users memory is a limiting factor in applications so now you've designed application and it's running and you've built it all around entity framework and it's worked great up until a certain point in production at that point now what do you do it's too late to re-architect your application because it's already in production it's already been written against it's probably grown beyond just a simple little applications a lot bigger now because it spent some time before it really ramped up in its usage now you're up a creek now you've got a problem because your application is based upon a foundation that worked great in development they were great in test and it worked great with small sets of users where they got big that's when you ran into problems and that's we have to come back and do all these optimization steps to hopefully get your application even more performance for your user load and just hope the user load doesn't get to the point where there's other performance issues that that come in as well and we'll see those in just a minute so with this now we have our address configured to be a little more optimal we go email address and do the exact same thing so first required control dot to add our user to it and the ostia say that the max length and let's say our max length for this is again 200 we'll still do n varchar' I don't know the email addresses can have a Unicode character in them I believe they have to have ASCII characters I'm not positive but if I did we'd have to add something similar to what we did here in order to save half that memory usage if we could set a varchar' instead of n varchar' but we'll leave it at n varchar' but I did want to have at least one of these in here to show you that this is what you have to do in order to make these modifications ok person so required actually I've got to paste in here yep I do so control dot here to add a user's event so now first name let's say that that is Oh 50 and we'll do the same thing for last name it's me 50 age it's just required and then addresses and email addresses we'll leave these alone now I've made some modifications to our models so we have to come back to the package manager console and say add migration added validation and hit go let it build once a build is going to create a new migration skipped our migration script and it does give us this yellow warning an operation was scaffolded that may result in loss of data this is no big deal if you are still in the development process and still building your application out however if this was in production and you had an issue a performance issue and you found out about and go oh and varchar' max let's change that to and varchar' when hundreds and let's change it to you know that zip code of varchar' ten that's cool until you get that yellow message is then you start freaking out because you can't just run this update migration if you do you may lose data so because you allowed an varchar' max for all these different columns if let's just say the first name which we've set down it'd be 50 now let's just look at it the max length is now 50 well what if you already had somebody's name in there that was 60 characters long maybe it should be there May it shouldn't be whatever it's there because you allowed it use the end of our char max well what its gonna do it's gonna truncate that name to only 50 characters it's gonna cut off the last 10 and discard them you will lose data what if for our zip code we change to varchar' when we were allowing n VAR char max so if we have a zip code that has Unicode in it or it has 100 characters in it again we're going to eliminate or replace the Unicode characters and we're going to truncate it only 10 characters losing all the rest that's why you need to check the stuff before you get production because otherwise you're going to do a lot of testing first to see what data you will lose and that means a lot more sequel work so let's look at this migration script alter column last name in the people table the max length is now 50 it's now not nullable some Nobles false and the old type is n VAR char max so saying as it was knowable so is what it was and this is what it is now and notice up here does not describe the type because the type was n VAR char max the only change was the length therefore it will still be n VAR char but the length will be 50 now let's look down at that the zip code again there you go zip code this did change the type so the type is n varchar' max the old type says whoops I am me I'm in down sorry I skipped down too far I wondered there we go so the type is varchar' 10 that's the new type and the max length is 10 the old type was n varchar' max so notice that the new type is varchar' max where we see that the change in type not just the length so you don't see the type change that means it's implied there's no type change up here that's implied based upon the old type of n varchar' it's just that the length has changed so again we have our up script which is going to do these modifications and are down script which is gonna roll them back now again roll back sounds great it sounds like we'll go back to a point in time where you're worried for well no a roll back means it will change the schema of our database the architecture of our database it will not put back the data they deleted so if you do an up here and you delete data let's say you have names that are a hundred characters long and you cut half of those off the roll back will not get those 50 characters back they're gone you have to go to your backups your sequel backups to get those data that data back ok so this just be careful when you're thinking about roll backs it's just a schema roll back it's just rolling back our sequel scripts he is not putting data back so if your migration deletes data it's gone if your roll back deletes data it's gone whatever you do make sure that if you're going to lose data that you know about beforehand and you either change what you're doing or you're okay with losing that data because once you run the migration either way that day is gone you have to hear your seagull backups to get it back okay so now we have this let's update our database and yes we don't care about our data it's not really anything there so we're good to go we did an update database and now our database if we go and look at it let's look at the addresses is fine let's go designer and our designer says n varchar' 200 150 and varchar' 10 cool we still have that nullable int we can fix that I'm not sure if he will it's video or not basically you have to do to do that is you have to come in here to addresses you'd have to create a an ID and say this this is the ID for the the person ID and then you have to make it not nullable but we're already long out when I go into doing that as well this is not as much about teaching you how to use entity framework as it is what the best practices are for using entity framework what to look out for how to work with this okay so now we have our our database in its it's set up it's ready to use let's actually use it what I'm gonna do is down in our let's kind of minimize some of these things so they're not making too much of a mess then pages here for our web demo in the index page we're gonna put in the page model we're gonna put some code to actually work with entity framework just to show what would happen okay the first I'm going to do is I'm going to import some data in here some sample data just to make sure that we have something in our entity framework database now what I did was I used a service where I can create temp JSON data I'm gonna bring that into our application right now this generated JSON you can have this this is generator the web and what this is is a schema that's based upon my models and it's just random data so I said give me a a random first name last name an age and give me three random addresses and four random email addresses per person and I asked for 100 of these objects so now we have a hundred people with all their information okay that's just to get us a little bit of sample data hundreds not a lot but something so it's up privates void low sample data and this method will be run pray all the time but it's only going to run if the database doesn't already have data in it but that means I haven't know about the database well I can come up here I can say give me a people context or that's the the context for our database control dot to add our using statement I'll call as DB and control dot to create initialize my field so now I have from dependency injection I have my people context remember back over and start up that we added the DB context to our dependency injection and we say it's a people context so never you asked for people context you get back our our context which has access to our three tables so now download sample data I can say if and this is why aunty framers so popular DB dot people dot count equals 0 what did I just do I just queried the people table and said hey if you don't have any records do this ok with that one line of code I didn't say open a connection nothing aya said you know what go to the context go to people table and give me a count and that count equals zero we're do stuff so that's really convenient I'll show you the down side in just a minute so let's start a string file equals system dot IO dot file dot read all text from generated dot JSON and that's this generated JSON file which in the route let's go to our properties to make sure that it is in fact copying it is copy of newer so this will be in our output our build folder so we now have read the file all the text into this variable again remember a string variable could hold as much as you want so that string variable file has all of our generated JSON I'm gonna save our people equals JSON serializer control dot add using system text JSON JSON serializer dot deserialize to a list of person from the file and person control dot add our using statement there so it's good to get a list of person from this file gandhi serialize it which means it's also going to deserialize the addresses and email addresses because the person has a list of address and a list of email so now we have hopefully 100 objects that have been loaded with all their email addresses and addresses so now i can say on its core DB d range people and don't forget the last thing to do is say DB dot Save Changes now I'm not doing a sync I'm just gonna do straight up this is just a quick and simple demo so what will happen is we're going to load this temp data from the JSON file if there's no people already in the database which is not right now let me just create it then we're gonna deserialize that into a list of person model messing in people we're gonna add that list to our database and then from there we're gonna save those changes now how does it know where to add these values well person I know that person is and person has inside of an address and email address so it's gonna take care of adding all that for us so I as ran an insert query on a whole bunch of items and going to save us all to the database so a get query a hundred insert queries actually more than that because each person has three addresses and for email addresses so that would be three hundred four hundred and one hundred that is a hundred inserts all with linking back to you know so the addresses get crib but then they have IDs that link back to the person that's a lot of complicated stuff that was a really simple way of doing this okay so let's call this method we can call it every time we load the paid the index page because it's gonna do us check now I'm gonna show you sequel server management studio why am i showing us now well because I want to monitor that database so I'm going to go to X event profiler standard double click it and it's going to load up a profiler and start listening to any event that goes on on my server so there's a whole bunch of stuff here in fact I'm gonna limit this I'm gonna say you know what give me only batch completes so let's right-click here say filter by US value and hit OK so now it's just batch completes and now notice there's a whole bunch of entries right now but if I would go over to let's go to the let's refresh this in case it's not refreshing it is our EF contact dB notices some more stuff going on here and I were to do a new query window and I were to say select star from DB o dot contacts that's not right it's EF demo DB that's that's why EF demo DB how'd that there we go people so select star from D do not people now to execute this statement come back over here and if we look in our our list here let's scroll down the bottom we're gonna see this entry right here now it's already trying to get away from me let's hit stop real quick and we're going to zoom in on this because it's a really small you see select star from DB without people so it captured the command that I just ran alright so with that we're gonna be able to do a little bit of sleuthing as to what's going on in our database now I'm gonna start this up again I'm gonna filter it by the database and by sequel batch completed so let's start this again and again this is a little more advanced sequel maybe you've never seen the X event profiler well if you want to use entity framework core or entity framework you prize you get to know it because the important when you're diagnosing what EF is doing on your server to know what queries it's runs this is going to tell you so let's set it back up it's starting to run and it's a little slows little behind the time that's okay we're going to execute this query again and there we go so now we're going to change change the columns here so remove this column and we're gonna add a column remove client name and we're gonna add select a record down here you can come down to the values down here that's like database name right click show column in table and then I'm gonna do a filter I'm gonna say filter by so right click on this and say filter by this value and now it is EF demo DB okay there's just the doughnut EBS and let's get rid of this select @ @ SP ID so filter buzz value and grass say not equal so it's again it's a little small sorry we're gonna say the text is not equal that @ SP ID but the database is demo DB and or eft-1 DB and the name is batch complete that's the one event i want to listen to okay and now it's gonna filter down to just demo DB and we've got a few less commands at least and we can we can clearly data and start over but let's close this first and let's filter a few more these out because they're gonna we want to make this as as clear as possible and not have any additional stuff in here if we can help it so I'm just kind of filtering out the the ones that are pretty common they keep getting run over and over again and say you know what no I don't want those who don't want those don't want those until I get a more reasonable list alright so still a little long and let's get rid of this one let's get rid of this one and let's get rid of this one obviously not equals for all these so there's my and transaction count will leave l um okay so there is what I have right now just the commands that I have made now what I'm gonna do is I'm gonna start up my application now I'm going to start it up off screen because I want to put a breakpoint right let's just start on screen we're at a breakpoint right here so let's start this up and now what's gonna happen is when we first load the page it's going to hit that break point okay now to come down here notice there's no events so far they're showing up with a screen we hunt dine thing yet but let's step over this people count okay took a couple seconds there and now if we look over here we'll see we have a new command and this new command says let's zoom in here so you can see it there's the command select count star from people as P well that's the the command the entity framework just used to find out how many people or how many rows are in the people table that's a really efficient call now you may say well slice count star don't use select count one well and that's my default did I do select count one but actually because sequel has gotten better and more intelligent select count star is no different than select count one so therefore it's just as efficient so we can now leave this and we do have some entries here logical reads rights and duration these numbers are they're not really as intuitive or as helpful as you'd like them to be but it does give you some reference when you're comparing to commands so it may not be they can go oh that's a you know that that took this much resources it's kind of made-up number usually those numbers but they are really helpful for comparison sake comparing one command or one call to another so now we have that let's go ahead and run all this come and hit f5 and run all this we're gonna watch what happens to our command window so it continued its going to create 100 records all right and let's see what happens in our command window and nothing happened and I believe the reason why if you're a filter right now is filtering on just batch completed let's let's put this one on pause for a minute and actually let's remove let's delete that clause just for a minute okay and now we have some extra commands we have some RPC complete and the reason why is we have some store procedures so entity framework is calling store procedures you may say that's cool because everybody says that store procedures are great or at least Tim does and so what's it doing well let's find out so let's copy the statement and a copy must copy the value so copy the sell and then I'll open up notepad notepad my trusty if you were here I'm paste in there you go so what are we doing here well we're inserting into a table we're merging on the people table and then a whole bunch of other commands down here and and these names are not human friendly so values p0 p1 b all the way through what 280 nope 299 as values so not ideal what this is doing is this batch inserting and the way it's doing that is it's creating a store proceed or a call that has a lot of parameters and then it's it's sticking all the values 299 of them or actually 300 ways I think the first one 0 yet p0 so 300 values in at a time and sending it through to sequel but it didn't actually create I store procedure for it it's and that's what normally we do would create a store procedure to do this bulk insert but this is a one-time thing possibly or maybe it happens all the time entity framework doesn't know therefore what's gonna do instead this is what happens for all inserts this is another thing I do not like it says execute SP underscore execute sequel this is a store procedure you should never use ok don't use this and I say never and I that's a little tongue-in-cheek because yes there are exceptions to every rule but I want it to be an absolute exception don't use this this is dangerous now Anthony framework is not stupid the developers are not stupid this is a very smart tooling system I said before the tooling is excellent so why is it using something that Tim says don't ever use it's dangerous well because entity framework has a closed system so we talked with the idea of sequel injection and not letting users write directly to our sequel database because they can inject their own sequel many drop tables or change permissions or cause chaos in our database this execute sequel does exactly that it allows whoever passes in any kind of string they want it allows them to just run that string and it will run that as a sequel statement now since Anthony framework has created this call it is safe to call this execute sequel because Anthony framework knows what the data is it's protected itself against injection attacks it's protected itself against bad data coming through and really we're creating us in a dev environment hopefully and testing this too however in order for sequel or an order for entity framework to call this sequel this that's SP underscore execute sequel the user that is in your connection string remember I said right now it's a trusted connection meaning I'm logged in that's my credentials but in production we'll probably a login and password those credentials will it's a logged in user or let us the login and password you put in the connection string those credentials must have the ability to run this SP underscore execute sequel what does that mean well it means that if you have a desktop application that uses entity framework and you have to put the connection string on their computer somehow I mean it's almost impossible not to you can talk about encryption all you want but the user owns one end of that encryption therefore it's practically worthless to encrypt it not worthless practically so with that user owning one end of the connection they have the ability to use those credentials without going through your application therefore you have given that user that has your WPF application or your you do you P application or whatever you've given that user access to run SP underscore execute sequel on your sequel server and do whatever they want I don't like that I hate that so just note the that's a danger right there that's one that my big push backs an entity framework is you really cannot lock your database down it's pretty much open those credentials are pretty much full access to your database I don't like that I don't like giving the user access to my full database and saying please don't mess it up okay now obviously your typical end user will have no clue how to get those credentials especially if you encrypt them but not everybody is a typical end user if you have a person a disgruntled employee someone who is just trying to play around a hacker or try and be a hacker they can mess you up not a fan okay now if you way about web application it's a totally different story because web applications you put the connection string on the server and typically your server is totally locked down from standard user access yes your server administrator still has access to your server but your sir administrator probably already has the keys to your sequel database anyway so it's different story on web but for desktop apps especially this is a little more dangerous so if you watch my store procedures video I talked about using dapper with store procedures and how you can lock down database access to only your store procedures not SP execute sequel not select star from table not even giving a table list just calling the store procedures that your application needs so if the user gets your credentials no big deal they only have access to what the application already gave them access to now they might be able to bypass some of the you know some of the restrictions your your c-sharp code puts out like a first name must be five characters long okay they can probably bypass that they call the stored procedure directly but that's not nearly as big a deal as being able to drop tables or read personal information that is not theirs to read it alright so that's the bad side now the good side is we've lowered 300 parameters what are those 300 parameters well if you if you actually look down here you find them so here you go so p0 is 33 p1 is Stricklin p2 is Boone and so on and loads all is up and with us doing it is doing a a bulk insert so it's inserting multiple rows at a time instead of just one row per call this is more efficient so there is an efficiency benefit by doing this but it does require using SP underscore execute sequel now now with just 1 we still have the other two so we have this call right here which if we copy this cell and we bring the our text file back up we control a and remove everything paste it back in it looks somewhat similar we have a whole bunch of parameters in fact parameters starting up number 300 and going through oh goodness this one goes through looks like 2397 again a bulk insert and so it looks like for email addresses I believe the other one was addresses and then we have whoops we have one more and it's this one right here and this one if we control a and paste them in this looks like this is the people nope this is email addresses merge email address still there's another merge there and yeah you know this is ok so again lots of inserts here lots going on make up the wrong ones hey of it let's close it out there's a three calls it made now if I were doing us in dapper I probably loop through all eight hundred rows and do one in at time therefore 800 calls versus these three calls is three calls better than 800 calls yeah probably is it massively better not as much as it sounds like because I would send about the same amount of information across the wire a little more because let's be honest I'm gonna do insert into and that statement and put the values we had called that statement over and over and over again well I'm calling insert into person these four columns I'm calling that that string and pass that string a you know a hundred times and they're the same thing for the four hundred times four addresses and the three hundred times for email addresses whatever it was so yes there's any more data I'm going to transfer across the wire to create 800 people versus this call the that's the the downside of come to dapper I do more work now I could create a store procedure that allows for bulky inserts that would greatly improve my efficiency and I could do a bulk insert and do something similar to this without the SP underscore execute sequel but it does require me to write that insert statement at bulk insert statement and they create table value parameter as well or maybe three of them one for addresses one for email addresses and one for people so yes it's more work going on in using dapper so in this way this is a little more efficient now how often do you do bulk inserts near c-sharp applications probably not very often if you are I would suggest writing optimized store procedure inserts with bulk insert ability and they'll be more efficient than even the entity framework code so it's up to you how you do this but in this case if we as compare apples to apples the simple way of doing things entity framework would be more efficient on the insert but open us up to more security implications by using the SP underscore execute sequel and requiring that ability now all of our data is there so let's bring back the visual studio and let's stop our query right here and afterload sample data which I'll leave there I'm going to do a very simple query I'm gonna save our people equals DB dot people now this people is only going to load the people table but I want the people table to include these addresses and email addresses which means going to these other two tables and loading that this is where an T framework tooling release really shines because I can just say include control dot to add our using statement here I want to include I can say a the era function a died addresses and then include e e dot email addresses and at the end I can say to list what this will do is what go out to the database it will fetch all the people it will wire up or link up the addresses and the email addresses and give me the whole result set as people now I'm just doing I'm not doing any filtering here I'm just saying everybody give me every which me a hundred people with their associated addresses and email addresses the to list is where you actually execute the query so a to list says ok this is a query so far but now we actually want to bring it down to the client we actually want the data on the client side so it's trying me the again the tooling is amazing it's training as efficient as it possible and so if I did a where statement here it would run the where statement on the sequel side not on the c-sharp side even though it looks like on the c-sharp side so we're using link here but link is working whereas is building a query for the server but let's start with just this really simple query I'm gonna say load this okay I'll put a breakpoint right afterwards and let's run this again now again it's gonna hit this people account and it's going to move us down a little bit if they hit the people account and say nope there's more than one therefore don't run this again so again select count from people you know as that call right there let's zoom in here there's that count again and it said we don't do an insert again because we don't have to we've we've already inserted enough records we don't hey we have more than zero but now we're gonna get this batch complete maybe use batch complete because it when it's complete is when it gives us all of our memory information so a batch complete on this query which I'll copy this and we'll paste it into notepad in just a minute but let's come down here and look at what we have for there we go we have for the information for this particular realm so we have the duration is eighty nine thousand 533 seems high especially compared to our select star which was only forty seven hundred and ninety two again kind of made-up number but it does give us a comparison sake this is a lot more work logical reads 331 and row count the row count was twelve hundred and four what does that row count talking about well that's the number of records that it returned to our sequel server or to our c-sharp entity framework and if you've been doing the math you may say no Tim it returned 100 so why does it say 1200 and four well because I've actually returned 1204 let's first start by looking at the people itself and making sure we actually have a data we need so let's pull one up at random just the first one we have ID as one first in a Strickland last name is Boone email address there's four of them in there and let's just pin a value here as we have something there we go there's a for email addresses that's cool and our addresses we have three of them let's just pin the the city listen estate okay so we have Martinsville Waterford and Waikele so again federated states of micronesia okay not in the US but that's okay but that looks like it's all the correct data it was so easy to do I mean my goodness that's all I had to do I got 100 records back with populated models that's awesome again that's better than dapper Dapper we'll take a few different calls to populate all the values here I probably done a call to people another two addresses another two email addresses and then link them up afterwards using link and then return the dice so we've been three different calls and we've gotten all the records possibly or done a little more complicated queries but I could have probably targeted a little better but them in three calls and then some work on the c-sharp side before I could get to this point whereas this is just four lines of code awesome let's it stop here and let's talk about this query I'm gonna copy it again and I think I'd copy already once go a new query window and I am in the wrong database let's go to EF demo dB there we go let's paste in our query well it's all in one line that's not great for formatting but let's fix that so we'll format like so make it pretty okay so here is here's our query we have select they do the square brackets a lot that's okay that's the recommended actually but asked for ID age first name last name ID city person ID state street address all the bits key all the columns from all the tables and it says from people as P cool it rename it P to make it shorter that's awesome I told you this tooling was great let me have left joint now if you're not from you a sequel a left join says give me everything from the people table and if you find matches in the address table give those values to me otherwise leave them as null okay and it links on the ID equals the person ID cool and the left joint here as well now do you know what the problem with this query is gonna give you a little hint one person so one person here how many addresses do they have is it one or is it more than one well it's a one-to-many relationship therefore the many addresses for one person so what happens when you match up the ID or the of the address of the person I'd be the and you say okay ID number one I go to the address table goes oh I've got three addresses that match that person ID so how does it know which one to put in the row what it does is it duplicates the person it puts the person three times and it puts each different address on a different row the same thing happens with email addresses but what happens because now we've got multiple addresses and multiple email addresses how does that all work let's write and find out 1,200 rows let's zoom in here okay so we have Strickland Boone there's 12 records for Strickland Boone so it's sent back the ID for Strickland Boone 12 times it's sent back Strickland Boone's age 12 times it's sent back Strickland's first name 12 times his last name 12 times okay now we have his city four times a dip is our city four times and his other city four times the same state person ID street address a zip code they're all duplicated why are they all duplicate well different email addresses so we have four different email addresses for Strickland through those four line up with every address okay so there's four there's four and there's four so that's what happens as you have more relationships with this left join where it's a one-to-many relationship what it's doing is creating duplicate records now you did not see this in c-sharp did you so what happened well what c-sharp did was I said you know what I probably can't write a more efficient query for this so I'm going to do then I get there's all this information I know it's gonna be tons of duplicates and I'll just eliminate the duplicates so it says oh you know Strickland Boone I have that person 12 times that's probably one record and stricken Boone age 33 ID one cool we're going to make that one object and then I see three different addresses I'll create one for each of those and I see four different email addresses so I'll create one for each of those and so on so it takes all this data and compresses it down into 100 records with attached objects now if I were to do a separate query where I said give me all the email addresses give me all the addresses and give me all the people separately in three different queries like I said I'd have to do with dapper I would have 100 people I would have 400 addresses or ze no I'm sorry 300 addresses and 400 email addresses as a total of 800 roast entity framework returned 1200 rows it may say well that's not a big deal it's it's yes it's a little more it's what 50% more but it's it's worse than that because when I would do a query for the people I would get this record once ID age first name and last name I'd get that record once and the framework has transmitted that value twelve times across the network so even though it only transmitted 400 rep more rows it has transported a lot more data so if my little example we went from 100 records to 1,200 rows fully populated with all the data ok so notice noise nobody know well these are all full so 1200 full rows instead of 800 smaller rows okay there's a lot more data transfer going across the wire now again this is a small example and it still is very performant this is a very quick to run and it's not causing my network to have issues because 1200 is no big deal but imagine what would happen if you had five or six different left joins here because every one of them multiplies against all the others so let's just imagine for a minute that this is a I have notes about people so every time meet up with a person I attach a note to their account so I'd say okay you know met with Bob had a great talk you know doing great and then the next time you Bob Bob really struggling with you know if statements in c-sharp and I'd put it down and I have you know hundreds of notes for Bob now I do a quick look up and I say okay give me all people and let's go ahead and attach our notes as well that could be thousands tens of thousands or even hundreds of thousands of Records for just a few hundred people in the actual database that's a real performance issue now again you may say well yes Tim but there's ways around that absolutely there are in fact if we go back up to our query if I don't need the addresses and email addresses I can comment those out and not include them if we run this now and we look at the list once it loads up it's not gonna have any email addresses or addresses in our list okay so let's look here the address count zero email address count zero it didn't load those now if we go and look at the profiler and come down here and look at what did okay down here it said row counts a little small but I tell you row counts is one hundred so it only grabbed the one hundred rows it only grabbed let's just even copy it and do no query and run this Early's show it it just grabbed this stuff from people table there is no inner joins that's much more efficient so if you don't need those addresses and email addresses don't include them that's kind of big deal things with entity framework but if you don't know and say hey I want that when I fully populate object now I do stuff with it well that's a serious performance hit so we talked about the difference you know again a big dapper fan because dapper performance one of the reasons why is performant is because it puts in front of you every single time exactly what you're doing the only way you make that call that gives 1200 rows back is if you intentionally do it you have to say yes this is what I want with entity framework it's kind of under the covers you don't necessary know what's happening unless you really understand how entity framework builds its queries and this is a very very very simple example so again this comes down to this will work just fine in development this will work just fine until you put it against a production database with a production environment where you have multiple people rymus because even if you have a production database where maybe you take a the production database you make a copy of it you clean out all the sensitive information and use that as your development database which is a good thing to do and if you do that you'd have a lot of those links you have you know a realistic picture of what these queries are going to do but if you are in development you're probably only running one query at a time you're probably only one person at a time but if this is on your index page of your your site maybe your your you know main page set has a list of all the users that are using the system and they can click on one it gives you more information with that user well that means that everybody that hits your site is running a query this is why you will see big load times crash web applications so if you are a university or if you are a school and you have a registration period so registration opens on Friday make sure you get the class you want okay guess what's gonna happen everyone is gonna get honored website at the same time so maybe throughout the year no big deal works just fine if you have you know a small percentage of your students on at any one time during registration time you have all of your students on at the same time so if you have 10,000 students as school normally only have I'd say three to four hundred users at any one time but during registration you have let's say 8,000 students at the same time if that queries a little bit performance heavy like it is right now at uncomment these two lines that is gonna crash your website or possibly see it raise the likelihood of crash your website add that to the n varchar' max and you've really got memory issues okay so this is where you have to understand how Anthony framework is building the migrations and you have to understand how entity framework builds these sequel scripts in order to make sure your application is performance I can't stress it enough this will all work in development just fine it's only when you get to production that you're gonna have a problem and let's just say you are University I used to work in university if my web application crashed during registration I don't have a year to fix that I have to fix that within minutes I have to do something to get registration running or we're losing thousands of dollars we're causing massive issues we're jamming up the the help lines we're causing students be frustrated which may mean they don't return there's an awful lot of repercussions that happen because my software failed at a critical time that's a big deal that's a career-threatening big deal so plan for those instances don't just plan for the fact that it runs great in development as one more thing I want to show you and I've just scratched the surface of how to use entity framework and what things to look out for but there's so much more cover but I think we've gone pretty far into this video I do want to show you one more thing that is an improvement in a tooling again the tooling is amazing this is improvement a tooling in entity framework or I think 3.0 was when it's improved and now it's 3.1 so let's just say I have a method and let's call this method private well it's a yeah pet bool approved age int age okay and it's going to return age is greater than and equal to 18 and age is less than or equal to 65 okay and that's return basically so is the age between 18 and 65 or not if it is return true if it's not return false so that's the approved H now this could be put into a query parameter but I'm going to put it into a where Clause like this where X arrow function I'm going to say approved age for X dot age okay so essentially what Milon do is I want to limit this list of people to only people to have an approved age between 1865 now again I could have said X dot age is greater than equal to 18 right in this query but this is just a demonstration purpose what's the difference between those right out bill away where X arrow function X dot age is greater than or equal to 18 and X dot age is less than or equal to 65 what is the difference between these two lines okay this seems like it's a little bit more easy to read instead of putting our evaluation right in line we have broken it out into a method cool not cool not for entity framework purposes for c-sharp not a problem for entity framework this is a prop and why is that well this where Klaus can get converted into T sequel and run on the sequel server remember that all this code up here is trying to run a the sequel server and then a to list actually brings down the results however this code right here is c-sharp code how would you run c-sharp code on the server you can't not really and so what would happen is that's what happened an entity framework six and this is actually a big problem is this is say yeah that's no problem let's go ahead and do it and it would run that approved age in the where clause and so usually go cool those two lines are equivalent nope what would happen is they got here and said oh I have to run c-sharp code against my query therefore do this part of the query download it then run this method here too to filter out the list so if you were expecting to get back let's say 500 records or I'm sorry 50 records on the hundred users if you're spending 50 users and you get back 50 users however the sequel call and the download would download 100 users all these are and then filter them so instead of let's just say 600 total rows remember it does 1200 rows for 100 years instead of 600 rows for those 50 users you're downloading all 1,200 rows for only the 50 users because you're faced with deleting half of data Anthony framework 6 wouldn't tell you that's a problem let's see what happens now he's right now it's not telling me this is a problem ok I believe it's a poster I believe there's a way to turn it on so it does tell you and it may be in the tools options let's go search for entity framework nope premium features nope not here it may be in my preview version of Visual Studio I'm not currently using a preview version this is what's a check this is sixteen point four point two okay so it currently is not telling me there's a problem here but let's run this and see what happens there's no breakpoints here is it's gonna run actually you know I'll put a breakpoint right at the end hopefully right there before even gets the breakpoint goes up nope the link expression could not be translated either rewrite the query and a form that can be translated or switch to client evaluation explicitly by inserting a call to either as enumerable as a sinking in we're a little to list or to list a sink okay what's a that can't translate this and as a a user you might not know what that means you may say what do you mean it can't translate this well let's do this I'm gonna put my semicolon here on a comment this out now I'm gonna put a - list right here that's what said I do do the to list before your where clause right let's just let's just clear this out clear to you okay we're start over so let's run us again my breakpoints in place I've moved my to list and let's run this and see what happens okay I can I break point cool it worked awesome come over here and we've got a batch here knows he have our counts so select count then we have a single batch and notice right down here row count is 1200 so what it do well it did just what I say I would do it downloaded all of this data and then it did the query based upon this where class therefore people okay people has a let's expand this out people has let's look at it looks like 55 people in it something like that it's ienumerable I can do two lists so I could have done a to us at the end of that's a price should but it's only got 56 people including a zero 56 people in this record well that's different than the 100 I was expecting I'm sorry that's different then the 100 I downloaded but it is what I expected I expected 50 some people because that was my range that I filtered it's probably the middle somewhere and by half of them or so are in that range so I got the values back that I expected and so if I didn't look at the sequel query and see what was doing I wouldn't know that I just made this query terribly inefficient and I even did what the exception said to do and therefore it made it terribly inefficient okay now let's get rid of this to list here let's uncomment these two right here and let's comment out the bad one okay let's run this again so I'll run again and I did to list at the end this time so I should have my my count so count is 56 cool if I go over to sequel come down here and let's zoom in on my my row count my row count is 673 that's much different than 1200 okay it's almost half why is it almost half because we had only 56 people come through and those 56 people had the four email addresses and three addresses so it's less records than all 100 growth had so by either changing my where clause or by not doing it I'm able to have a more efficient query so just because the exception says hey put to list before this where clause don't do it not if you can help it because you're bringing all these records down to the C sharp to then do the work if you can help it let seek will do what sequel is best at sequel is best at storing and retrieving data yes c-sharp can do it and yes there are times when c-sharp needs to do some filtering on its own however the bulk of the filtering and querying work should be done on the sequel server so that's why if you can write your query like this as opposed to a separate method it's important that you do because therefore it will do this query on the server in fact let's go look at that queer real quick I'm gonna copy this and then I'll open up notepad again I closed it so let's open back up let's paste this in and let's say have word wrap now Warcraft or not so there's our let's just kind of wrap it up ourselves like so and there's a select statement there's our from there's our left join there's another left join here's our where clause and there's the order so where clause where age is greater than equal to 18 and age is less than or equal to 65 cool so it took our c-sharp code and translate it into 80 sequel query on the server so that we didn't have to download all those records so a key thing that the best practice here is when you've got your linked statements don't call c-sharp methods put the code right into the where clause if at all possible so that it can run on the server now it should tell you in EF core that there's a problem if you try to do this code in Antony framework it won't tell you it will just run this code by first downloading this data and then running it against this method so that's where you really have to understand how to look at these queries see what's going on and make sure that that's the query you want run so to recap you need to know how your migrations are working and make sure that the migration data is correct and it is efficient so you have to make sure that you decorate your models appropriately things like changing to varchar' and sub and varchar' changing the max length making them required these things are important to do ok that's from the design perspective the next thing you need to do is make sure that when you're writing your queries that you don't do these includes unless you absolutely have to and that you know the implications of using that include it was going to cost you you also need to make sure that you don't call c-sharp methods in your query until after you download the data but note that once you've downloaded data you're downloading larger chunks if you only filter after you download so if at all possible filter before you download all right so you also need to know how to use the X event profiler or something similar in sequel server or some way of knowing what commands are being run on your server how efficient they are and what you can do to improve them because it's very very important now let's talk about as we wrap up here why would you use entity framework what are the benefits of entity framework so no prep here in the comments benefits of entity framework touchable core I mean it's ng framework or entity framework core ok it's up to you so the first benefit that I hear a lot is faster development speed ok I get that obviously this was super fast to do ok and I got all of miles populate it just worked it was quick to develop but it was slow in production yes there are things you can do to improve the performance but that means either writing your own store procedures which negates the purpose of doing this or you can not include some ladida which again negates the benefits you're getting from entity framework or you just live with the fact that yes it's more data but hopefully we won't need that extra performance okay so that's you never want faster development speed absolutely it is quick to get entity framework in and running however as we've seen it slows way down if you want to make sure it's performant so yes you can shoot yourself in the foot fast but maybe you shouldn't shoot yourself in the foot maybe you should review what your creation scripts are like what your table design is like and make sure it's correct that takes some time maybe you should decorate your your objects and make sure that they have the right decorators that takes some time you've now lost some of the benefits that you got from entity framework because you're trying to be performant here right so faster development speed yes but if you want to be performant then that development speed is greatly reduced so the next benefit I see from people is you don't have to know sequel okay I think I've proven that's not the case in this video because if you don't know a sequel you're absolutely shooting yourself in the foot because would you have known to go in here and it would you have known what n varchar' max meant or would you know it works he probably know it works would you know what this table even meant and know that Oh 272 kilobytes is pretty bad and we shouldn't make a change so we were not using that anymore and use instead you know n varchars 64 or 256 instead he probably wouldn't because he wouldn't even know how to do that because he wouldn't understand the different data types in sequel server so would you know if you don't know sequel would you know how to use the X event profiler to see how many records you're downloading from every query or how to optimize your query by looking at this and making tweaks to your c-sharp nope wouldn't know how to do that either so therefore if you don't know sequel you really shouldn't use entity framework because you will burn yourself now again if you're going to do small applications if your applications aren't going to production if you are building demo applications or applications for very small companies that have very few users then you probably ever see a problem to entity framework okay but if you ever intend your application to grow in any size it will be a problem you will have to optimize it which means you will have to learn sequel and you will have to slow down your development speed now I mean talk with the fact that our scripts let's say his validation script here remember we ran this in fact I instantly here yep an operation with scaffold might result a loss of data please review the migration for accuracy how would you do that if you don't know sequel how do you know what's happening you wouldn't may if he do know a sequel what do you have a do to evaluate this loss of data you go into some queries we're truncating these 10 columns is there data in those 10 columns that is growing at trunk head you had to go to sequel server man studio run some queries find out figure out to do with the data and maybe it make sure you have good backups have you done any of that probably not if you don't know a sequel and even if he did you've now slowed your development speed down because you have to be responsible with your data all right so I really don't see benefits number one into being that valuable for entity framework it is absolutely faster develop and you don't have to know sequel but as we've seen if those two things are true then your database is in danger I don't like creating databases relegate that are in danger production databases can have thousands hundreds of thousands millions of Records if you are not very careful with how you do queries if you're not optimizing your queries for performance then you're gonna slow your application to a crawl and it's gonna happen in production at the worst time whether you're an e-commerce site and you put have a sale going on and that's when it crashes your website or you're a university and you crash during registration and there's so many other possibilities based upon your industry that's what's gonna happen or most likely gonna happen and if that happens then you're on the hook and there's not a quick fix now you may say Tim I've been working for 20 years I've never had a problem great that means one of two things one you're throwing lots more resources at it then you need two or two they're not that big okay one of the two is a case either you're not doing tons of queries and you're not bringing back lots of data or you're throwing lots of resources at your servers in order to keep them performance neither those is terribly efficient okay either one you're not doing a lot of work in which case you know do not work or two you're throwing lots of resources at it that you don't need to and therefore you're paying for them okay you're paying for whatever resources you have if you have your application in the cloud then you're a little more visible in how much you're paying for your application if it's on-premises then you're probably paying by buying bigger servers and you really need with lots more memory than you really need and you're having a lot more energy usage from those servers so you may get by and never see an issue but if you do it will happen at the worst time now you've heard me say that I like dapper and if you've watched any videos I prefer dapper okay so you let's tell the benefits of dapper goodness there we go all right so benefit number one faster in production that's just gonna be a case okay dapper is practically as fast as a do dotnet a do dotnet is pretty much bare metal calls the database so you're not gonna get faster then then dapper in almost any case now there there might be case where you get entity framework as fast or practically as fast but again that's if you slow your development speed down and you do no sequel and know how to optimize your entity framework or if you are an advanced user of entity framework core again if you're an advanced user of entity frame at core and know how to optimize it cool absolutely use any framework or it's just that people don't Kamala gate knowing entity framework core there's massive books there's tons of video content there's lots of blog posts on how to make entity framework performance how to use entity framework in general and how to to work with this inside your application the reason why there's a lot to know if you know it if you're great at it if you're an expert cool you will do a great job and make it performance with dapper yes development he is going to be slower but these faster in production you'll know that you've optimized for production therefore you don't have to worry about or worry as much about the scale that your application hits because you have optimized the database portion of that scaling already you've pre optimize your application for database performance because you want fast in production fast development speed happens once production speed happens every day I value production speed much more than my development speed I love how fast entity framework core can be developed I want a faster production speed for my application okay damper is easier to work with for a sequel developer you can in about four lines of code call a store procedure with parameterize sequel for your data really quick I have a little snippet that I've created that actually turns into one line of code one line of code and I get data from the database I put it into a model and I have that list of model on my computer that does mean I have to rest or procedure or I have to write a T sequel statement and pass that instead yes that means I have no sequel but with that I can be faster in production and I know exactly what day is coming across the wire I know exactly what my query is doing performance wise and I can make a change that very very easily if you have to change this query be more performant what do you do I mean seriously you'd have to rework how this works you'd have to make some kind of weird linked changes to get this to be different that's not ideal because you have to really dive deep into into link and to entity framework to understand how it creates queries and make some changes if I'm Raina store procedure I can just change the TC quotes inside of it simple as that okay the third benefit I see here is this designed for loose coupling now you notice I put my entity framework in a class library but it still kind of fought me and in fact if we look at our dependencies and our new get packages will see that I have entity framework core tools installed and if we go to start up you'll see that I'm actually using Anthony framework or in my in my friend and knows about sequel server and the DB context it knows what I'll let data access stuff and entity framework stuff in my front end that's a little less than disconnected for me this damper I can create a class library that just sends back models you don't know how it gets them it ascends I'm back and so that's a little bit more it's a lot more loosely coupled as for an application which means that I can create data access library and pass that around more easily to different applications and to be framework a little bit more complex to do that possible just a little more complex now this is not a EF core versus dapper video I'm just kind of point out the highlights here of why I still focus more on dapper that I do EF core the other part of that is this channel is focused on helping developers learn c-sharp I do teach entity framework or in my foundation and c-sharp course series but I wait until module eight and that's only after I have taught data access directly using dapper for sequel sequel Lite MongoDB and others then I teach entity framework core and I walk through some of the examples and and talk through why we don't just use this tool all the time because this entity framework or this is dangerous and that's that's what I taught my illustration in the course is that have you ever seen those big earth movers there's really big machines that's what entity framework or is it's a really powerful awesome machine but to get in the cab of that machine and drive and operate it you don't just decide one day I'm gonna do that you get trained on it you get license to use it you you have a instructor teach you how to use it it takes time to get in that cab and operate it that's kind of like what anti framework core is is that big earth mover it's dangerous to use if you're not really experienced in it and it can really bite you and the worst part is the danger doesn't show up until it's too late so that's why I say anti firm is a great tool my issue is who uses it and I say that this is an advanced developer tool that only advanced entity framework core developers should use again not trying to get keep here I'm trying to protect you I don't use it I prefer to have that control with dapper like yeah I am a sequel developer I do no sequel I do have a course on teaching how to become a sequel developer not hard but that way I see what's going on immediately I can make changes directly and is designed to be faster in production and it's really easy to be loosely coupled and really my dapper code one line for writing or for reading from one line of code and then just your your sequel call so with that it's not that much slower than anti framework or but the results are faster in production easier to work with and more loosely coupled okay so that's my take on entity framework that's my my issues with it and so the best practice that I've seen you using even there's a basics of it and also the pitfalls you need to avoid so really understand how your sequel works really understand how entity framework creates your sequel and how to diagnose that sequel is being called and make modifications to it okay so if you use entity framework I definitely encourage you become an expert in practice it a lot really look at what's going on here sequel statements monitor them understand the performance benefits and drawbacks of everything you do and know how to work around some of those performance issues they're gonna happen so if you stuck with me as long I would love to hear your thoughts in the comments I love to know what you think I missed as far as the performance or the the the issues that occur in entity framework or the benefits that I missed entity framework let me know what you think of you know using dapper versus any framework again just please keep it civil this is here for us to grow I love for all of us to get better again I'm not a huge entity framework person so I'm sure I missed stuff so if you want to say hey this would make your query better or this would improve thing that's great yeah I would really appreciate that now that is prove my point a little bit because again if it's not obvious that this will improve your query then it's not something that a beginner or intermediate developer will do it's okay I love become an expert into the framework or because I'd love to you know use some of us power I mean this is pretty powerful stuff it's pretty easy to do so definitely if you have suggestions that'd be great I'd love to hear them okay now I do have also a blog post I'm gonna bring it over right now I wrote this back in August I rewrote it I had a my old site I had a post and I kind of rewrote it and this is my thoughts on should I use entity framework and my answer typically is no so now I do point out hey real programmers they often think I'm an idiot and DBAs database administrators often cheer okay I've been to both kinds of conferences I've spoken at conferences in front of developers and they're like why don't you use entity framework that's just obvious that's simple and I've been to conferences speaking front of database developers and they're like oh great like thank you don't use entity framework please okay and so there's bull sizes but there's much more nuance than either camp wants to admit so I talk through that nuance okay so give us a read let me to think it asked some good questions here some questions like does your team understand how to use entity framework well that's more than just knowing how to write linked queries does your Tina had diagnosed issues with a code or under performing queries okay and do you have protect those client credentials if you're using do PF or wind forms or console you're possibly giving away your credentials that could be an issue and somebody do some comparison here I do talk there's that video on store procedures where I talk through the security of your database and how you can lock down the security even without encrypting your credentials which I said for a desktop application doesn't do that much benefit okay and I talk through the connecting a sequel using dapper right so that article I'll link down below in the in the description give that a read if you have want more information I didn't cover all this in the video but but that might be of benefit as well so again give me your thoughts I love to talk through this with you and here you have a say alright thanks for watching I appreciate the hanging in there for this longer video and as always I am Tim quarry [Music] you

Channel: IAmTimCorey

Views: 440,585

Rating: 4.8802705 out of 5

Keywords: efcore, entity framework, entity framework code first tutorial c#, entity framework core, entity framework tutorial, entity framework c#, entity framework core tutorial, tim corey, iamtimcorey, best practices, pitfalls, ef core best practices, entity framework best practices, entity framework beginner tutorial, entity framework benefits, entity framework drawbacks, c#, .net, .net core, .net core 3.1, .net core 3.0

Id: qkJ9keBmQWo

Length: 153min 32sec (9212 seconds)

Published: Mon Jan 13 2020