 On today's Visual Studio Toolbox, Phil and I start to take a more in-depth look at Entity Framework Core. Hi, welcome to Visual Studio Toolbox. I'm your host, Robert Green. Joining me today is Phil Jepixi. Hey, Phil. Hey, Robert. How are you? I'm good. How are you? Good. We just finished up, at the time we're recording this, we just finished up a five-part series on Introduction to Entity Framework Core, and that seems to have been pretty well received. So we thought we would kick off another series and go a little bit more in-depth on Entity Framework Core. How does that sound? It sounds great. Yeah, that first series is really meant for people who have never looked at Entity Framework in any level, whether it's EF Classic or EF Core. We didn't go nearly as deep as people were asking for. Here we go. We went a little deeper in a bunch of different shows. All right. At the moment, as is typical, we have no idea how many of these we're going to do, but we'll get started. So what are we going to look at first? So we're going to look at performance first. Well, actually we're going to look at performance second, because the first thing I want to point out is I saw in the comments that some people were having a hard time scaffolding AdventureWorks 2016, and depending on when you downloaded it, there are some quirks to that sample database just because it has the same name. So in the repo from the last show and the repo from this show, I've copied that code over from the last show into this repo, there is a SQL script that will make your database match my copy of AdventureWorks 2016, and a little read me that says, hey, run this SQL script, and it will then match what I have, which I was able to scaffold as we saw when we did the show. So I just wanted to call out that we are reading the comments, and I wanted to give an answer to that that hopefully that will fix the problem. Yes, and I read the comment, I think it was episode part three, where one person thanked me for all the times I interrupted you to ask good questions, and someone else told me to shut the heck up, so I'm going to try and strike a good balance. Yeah, just somewhere in the middle. I think we have a good banter when we go through this. I know this really well, what I'm talking about, and so sometimes I'll gloss over something that is obvious to me, and it's great that you come in and say, well, wait a minute, time out, go a little deeper in that. So I think it works. I do too. Anyway, enough about me. Let's talk about you. Well, we're not going to talk about me. We're going to talk about the NAD framework core, and just to be clear, the version I'm using is 3.1. Five will be coming out later this year. That's a preview three, I think, by now. There were a bunch of comments, and I hear this all the time when I'm doing trainings and when I'm at shows about performance problems. And people coming from EF Classic, that's not the official name, that's what I call it, you know, EF 456. I agree. There were lots of performance problems. What I want to do is I want to give a demo that I originally got from the EF Core team a long time ago, and Diego had written this and given it to me for the VS 2017 launch event. I've been updating it over the years, and I want to compare EF Core 3.1 with EF6 that has been ported to Core. So now there is a version of EF6 that you can run in .NET Core. So it's as close to an apples to apples comparison as we can get. Before I run the demo, I just want to tell you that the bad props to the EF Core team and all the community contributors, one of the biggest changes that I've seen with the entire .NET Core suite is that instead of just focusing on how do we make it easy for the developer, they're still doing that, but part of their definition of done is performance, and they're not letting things get into the product that don't perform well. So we're seeing an order of magnitude increase in performance with all the .NET Core, especially EF Core right off the bat. Let's get it out there and show the differences. Okay, let's see. All right. So what I have, again, AdventureWorks is a needed chunk of data. This is the only show where you have to have AdventureWorks, the rest of it, we're going to be doing code first. But I wanted to have a bunch of data. So what I have here is a series of tests, and I have three projects, if we can go to my screen, and so one is called Performance, and then the other one's called Performance EF6 and Performance EF Core. In both cases, I have scaffolded, excuse me, scaffolded AdventureWorks into these projects, and then I have a series of queries that I run. I run the exact same query against EF6, and I time it, and I run the exact same query against EF Core, and I time that, and then we just do simple math to show the differences. So the tests that we are going to run are pretty simple, and some would argue not necessarily the best practice, but I have to add magnitude to the test so we can see the results. So here I am querying 19,000 records out of the customer's database and calling toList. If you remember, Robert, from our last series, the query doesn't actually execute until we call toList or iterate over that list. So calling toList, make sure that we execute it. Then we're taking advantage of calling it as untracked. So if you didn't listen to our first set of episodes, there is a performance enhancement you can do to not add your objects into the change tracker. So we don't have that overhead, we just want to get the data back. So now I'm doing the EF6 exact same test against EF Core, but calling as no tracking. We'll talk about this in the next show in more detail. But here is an EF Core test against EF Core, and one as no tracking and one using a query type. Then with EF6, I run a complex query, and complex query needs to be an air quotes. I don't think you can see me on the screen right now. All it is is a whole bunch of link statements. So you can argue that it's probably not all that complex, but it is doing a bunch of joins. The final test is that we are creating 1,000 records and calling save changes. Now, again, I would argue that creating 1,000 records at a time and calling save changes is an anti-pattern in any ORM. Unless that's one giant transaction. It's one giant transaction. So it's- They all got to go in or none of them go in. Right. Then to run the test themselves, run the EF, the first one, run the second one, put a timer on it, and then run it. Then we start off by just warming up the database so that it's not a cold start. So what exactly are you doing to warm up the database? So the reset and warm-up is just running a simple query. Okay. Right. So if we go into here, it's just learning out and then just calling first at default. There's as we talked in the last series, there's a little bit of overhead in creating an instance of a context. That's why the on-text pool in ASP.NET Core is helpful is it's like using a connection pool. So we're going to have a pool of these things sitting around waiting for us. So it's just trying to get rid of some of the variables that might happen of creating the context before we actually start using it. All right. So is that font big enough for you? Yeah. Okay. So we're querying 19,000 records calling to list. EF6 is still fairly fast. I mean, it's less than a second, but it's twice as slow as EF Core. We run the test three times, so we make sure that we don't get some anomalies. When we move to untracked, so we're not bringing them into the change tracker, we get even more improvement out of EF Core than what we saw in EF6. Again, this is EF6 running on.NET Core, not the EF6 running against.NET Classic. So now if I use a query type, which we don't have to call no tracking, EF query type is a special type where it says, hey, we're not going to ever update this. You see that we're getting down to 95, 90, 80 milliseconds to call all these records back. The complex query, we see a little bit of warm-up. So what is happening here is that link statement is being prepared and then executed. So we have two sides of caching going on. The link translation engine is caching that link statement, at least in EF Core, but also SQL Server is running the cache for all these joins. Here's where the biggest difference is, and it's adding 1,000 records and saving changes. We see it's two seconds, warms up to about 1.4, 1.6 with EF6, and we're down to less than half a second, 0.2, 0.2, saving 1,000 records. Now, again, I have to stress because somebody's going to mention this in the comments, creating 1,000 records using an ORM and saving them, probably not what you want to do on an everyday basis. The sweet spot for an object relational mapper like EF Core is I'm doing crud work. I'm creating some related records. We get that built-in transaction, we call save changes and those things, but I want to show the performance benefits. But if you had an online offline scenario where somebody was doing a bunch of data changes offline and then connected, you could potentially just batch them all up and save them at once, right? Correct. Yeah. There's another huge, I'm glad you, that's a great segue into the next point I want to make. A lot of the performance benefits that we see here are due to just the great work that the EF Core team and the community contributors have done in paying attention to the performance details. It is a complete rewrite of EF6. I'm sure they use some clipboard inheritance to pull some code in. You're familiar with that pattern, right? Robert, I know we did a design pattern show, right? Clipboard inheritance. You copy to the clipboard and you inherit into another project. Exactly. Not really a design pattern, but hey, the jokes don't get any better than this, so that's where we are. All right. So there's two things that are really boosting performance. The first is, again, it's a rewrite of the entire code base paying attention to performance. But another huge change between EF6 and EF Core and this is even EF6 and running in Core is a concept of batching. When we run 1,000 insert statements in EF6, we get 1,000 calls to the database. What EF Core does is using a batching algorithm, and you can set it manually, and we're about to do that so we can see how batching works. But it will then use some wicked math to figure out how many of these records, whether they're updates, deletes, or inserts should go in one batch across the wire. Now, this does a couple of things for us. First and foremost, if you are in, for example, a SQL Azure scenario where you're paying per transaction and transaction not being a database transaction, but a call across the wire, if you have 1,000 individual transactions or individual calls, then that can be prohibitively expensive, whereas the batching will batch those up into a certain number. Now, what I am doing in this example is a very small insert. We're only inserting a couple of fields, and I'm running locally my machine against local DB. The batching actually just comes across as one batch. But let's see how this works in action. Let's start with going back into the domain program. I'm going to comment out these. Actually, we're going to go here. Now, run and save changes, no batching is still going to create the 1,000 records in EF6, but let's look at how this method has changed, or how I've changed it for EF Core. I have changed it so that the batch size is one, so you can configure the batch size yourself. I don't recommend it because it is configured by how wide your table is, how many records you're updating, and things like that. But for certain queries, and we'll talk about this in the next show, when we talk about configuration of the DB context classes, you can set it up and say, in this case, I'm doing 1,000 records, I want the batch size to be 250 or something like that. Let's first take this out, so I'm not passing in the custom. It's going to use the default batch size, and I want to put a breakpoint in the run tests. Let's put a breakpoint right here, and then another one right here, and I'm going to pull up SQL Server Profiler. Let's go ahead and run this. I just got myself a 43-inch monitor, so I'm a bit spoiled, but I just want to clarify. Let's start Profiler. Let's clear it out. We are about to run, and it will go down here, and here are all the inserts. If we want to scroll through, there are 1,000 of them. That's EF6. Let me pause that, let me clear it, let me run down to here. Profiler is going to struggle a little bit with this, because what it is doing is creating a very big string that it's sending across the wire with all these updates tied into that one trip across the wire using some SQL to add them all together, as opposed to individual SQL statements. I will show you the top of it once Profiler wakes up, but let me go ahead and stop debugging over here. The default is that it actually batches everything up because we said that you wouldn't necessarily edit 1,000 records and save them all at once, but that's what the default behavior is. Is that right? The default behavior is not that it will batch up all of them. The default behavior is that we'll use batching, and words matter in this industry. I just want to be very clear that the default isn't that every single update you do will go over. It's going to determine a runtime based on the size of the updates, and it has determined to do on my machine with very small records do all of them at once, and it's probably looking at how much memory I have and things like that. I'll be honest, I don't know the algorithm it uses. It's just magic. Let me run this test again, and I'm sorry, Visual Studio is just not making me happy in split screen, so I'll just bounce back and forth. If I go back into the tests here, what we'll do is we'll pass in a batch size of 1. We are configuring it, and this is again, we'll go deeper into this in the next show. One of the huge changes between EF6 and EF core, EF6, you passed in a connection string into the base DB context, and it was a struggle to do more configuration. With EF core, you're configuring everything. We're passing in a batch size in this case. Let me make sure I still have my break points, and I do. Let's run this again, and we already know how EF6 behaves, so we'll run past that. We'll get to the EF core, flip over to Profiler, stop it, clear it, run it. Again, this is a batch size of 1 in EF core. What you will see is the exact same behavior from EF6. You can adjust this. You can adjust it on a per-query basis. You can adjust it across your entire application. One of the things I wanted to point out, in addition to all the great work that the team has done just on the base class libraries within .NET Core and EF core, there's additional things like this. We'll talk more in the next episode about using store procedures and from SQL calls and limiting data packet size and other optimizations and customizations. But from a performance standpoint, hopefully what I've been able to convey in this episode is just the, and I'm going to call it blazing fast, and I know somebody who's writing straight up ADO is going to take my sample and write code between it and say, hi, straight ADO is faster, but I want to go to time-to-market is also an important metric and instead of me having to write all the stuff we talked about in the initial series of change tracking and configuration and a lot of type of stuff, the plumbing is done for me, but right now the plumbing is really fast. Awesome. Any questions? I think this is when you're supposed to interrupt me. I'm trying to keep that to a minimum. So it's definitely much faster. Yes. We're starting to see it's more configurable, and we're going to look at that in future episodes. I think that's a good place to stop. A great way to kick off our next series, so we will see you next time on Visual Studio Toolbox. Hey, thanks for watching.