 On today's Visual Studio Toolbox, part three of our overview of Entity Framework Core, Phil's going to show us querying data. Hi, welcome to Visual Studio Toolbox. I'm your host, Robert Green, and joining me today is Phil Jepixi. Hello, Phil. Hey, Robert. How are you? I'm doing good. This is part three of our multi-part series on Entity Framework Core. So far in part one, we looked at connecting to the database. We looked at scaffolding. So we took an existing database, ran a single command line, and then got classes or objects for each of the tables in the database, and we looked at what to do with those. Then in part two, we looked at change tracking, which is important. You're going to be making changes. It gives you the ability to save changes all at once. Then today, we're actually going to start querying data. Yeah. Just to be clear, this is part three of the intro to EF Core. So if you're an experienced Entity Framework user, this probably you want to jump in a little later, and up on the site, we'll tell you which episode starts with the more advanced features. Once we do them. Once we do them. Once we record them, we'll let you know where they are. Yeah. We'll do a little time travel editing here. Right now, they're all up here. Correct. Actually, they're in your head. Yes. Your head. Okay. Let's go. All right. So like Robert said, we talked about getting a database scaffold up, so we have the code around it. Change tracking, which really provides the power of an ORM so we don't have to do all that plumbing work manually. What I want to talk about today is query execution, and not just how to query, but when queries happen. This is extremely important, especially in an Entity Framework world, whether you're doing EF Core or not, or any ORM for that matter. All right. So let's start with just a very simple query to talk about how query execution happens. An important thing to know before we get into the code is that there are certain triggers that will actually have a query execute against a database. It doesn't happen immediately. So I have a little sample here and we'll put the link up to the GitHub repo in the show notes, and it's just methods called link execution timing. It's very simple. When we create a query in link with EF Core, nothing actually happens at the time. We're just building up an Iquirable op. Here I have the person table, which we're going to be working with throughout this series. I'm just returning it as queryable. This tells EF Core that at some point in time, you're going to want to run a query against this. Now, when the query executes, is if it gets iterated over, if you call to list on it, or if you bind it to something like a data grid. So at this point here, when I call query.toList, it goes ahead and executes that query server side. Also, if I for each over it, then it executes and then there's certain constructs where I say, give me the first one or give me the single one, and those will execute at that time. Now, here's why I am stressing this because as we go into a further code samples, we want to make sure we control the timing of when the query executes. I've got a customer I'm working with right now, they were building up this big long fancy query through multiple steps, but what they were doing on the very first call was they were doing the equivalent of context.person.toList. What this was doing was bringing back every single record in the person table into memory, and then they were adding link where clauses and order buys, and so that was all happening client side. So the number one place where I see people really shooting themselves in the foot performance wise, is not understanding how this whole thing happens. So does query represent an actual query or does it represent a promise that you're going to be querying the person table? So the answer is yes, and the reason I say it that way is because the term query here is overloaded. So we have the link query, which is defined by the I queryable, and that is just like if you're doing link to objects, it's linked with some extensions for entity framework core, but when you actually go to execute that query, the DB context then sends that link statement to the database provider, or the link translation engine to turn that into SQL, or no SQL depending on your provider. But in our case it's SQL Server, so it'll take that link statement, however complex it is, and turn it into SQL to execute in the database. Okay. So put another way, if I'm going to run multiple queries against person, one to bring down last name, first name to put in a list, one to bring down a particular person that gets selected from the list, et cetera, one to bring down persons in a particular state, would I have one query object, and then use it three times, or would I have three I queryable of person objects? Well, because of the scenario you just gave, you would have three different I queryables because they're very different scenarios. Okay. So let's, before we get into building up query statements, let's just talk about how they work, and if you're familiar with link to objects it should be very clear to you. So we can add where. So here is, I'm selecting a person where the person type is employee, and they have opted into the email promotion. By the way, if you're not familiar with this font, this is a new font by Microsoft that actually probably should have changed it for this. So there's a single equal, double equal. It actually changed it. It's the- What font is that? Cascadia. So the other thing that's cool about it, and it's because I'm a nerd. So if I do not equal, it actually does that. But then I always forget when I'm presenting that sometimes it can be a little confusing, but it's too late to change it now. So know that that is a double equal and not an assignment. So here I am saying where person type equals employee, and they are opted into the promotion. Now because queries don't execute until I ask them to, whether I know that I'm asking or not, I can build up queries dynamically. So I can start with this where clause, and then I can add another where clause. So a good scenario for this is, you're building a dynamic search screen, and you want them to be able to pick different fields that limit the search. So they have to be in the state of Washington, they have to be an employee, and these different things. So instead of building this huge if switch statement, I can just say, let me do this, query equals query two dot where, I can build it up dynamically saying, okay, here's the first, yep, they want to know the employee type, they want to have them opted in. And so those are equivalent queries. So what's the difference between that and just doing a simple and? Nothing, it is an and. So when I chain these together, but what it allows me to do is, let's say we had 10 different things that they could choose from in their search, right? And this is really where the most power comes in, because if I'm just coding against a particular screen as a developer, I'm going to know what I'm searching for. So I'll probably use this syntax here, because it's more readable. But if I have to say, if I'm looking at a form collection coming across, let's say it's an ASP on a core application, and they have picked four out of the five fields, I could say, oh, if person type is not null, then add a where clause for person equals the person type that comes across. If email promotion is not null, then add a where clause for email promotion, as opposed to having to look at all of these things together. And I mean, if you've got 10 items, that could be 10 factorial changes that you would have to write if you had to build these up in one fell swoop. So I'm going to add it. Now, the thing that you have to watch out for are ORs. So when I chain them together, like I did here and here, then those are ands. So chaining is always an and. If you want to build in an OR, you actually have to put that in here, right? So there's an OR. So I want person type equals EM, or they've opted into the email promotion. So a little less chainability with the ORs, but total chainability with the ands. Now, none of these will actually execute because I've never called two lists or bound it or iterated over it, right? So none of these have actually fired. So I have not hit the database at all with any of these samples. And I just want to keep stressing that because so often I see performance problems in, let's be frank, right? Entity framework, class had got a really bad name for performance issues. Some of that was deserved. A lot of that was because people were using it wrong. EF Core, and as we get into the more advanced shows, the show performance demo between EF Core and EF6, to show you how much faster it is. Like it's an order of magnitude faster. But if you are still pulling back 100,000 records in your first call and then sorting and filtering, then it's not gonna be fast, right? So I really want to stress this in this episode. Okay. Ordering is very, very simple. We do an order by, and then if we have multiple, then we can do them by. So here's our first order by, then by, we can also do order by descending, then by descending. So you can chain as many of these orders, order bys and order by descendings as you want. And do you have to use an I ordered queryable as opposed to an I queryable? Well, so what we would normally do in real life is use var for this, because var was really designed for link. As we get into more complex queries, the I ordered queryable actually based on multiple types can be much more complex. So what we really want to use is var. I'm not using var because I want your viewers to see what we're actually creating. So yes, once you add an order by statement into it, it actually returns an I ordered queryable as opposed to an I queryable. I see. As a developer building line of business applications, for the most part, you don't care about the type that's coming back. You really just want the data and you want to bind it to something, whether it's a view model or a grid or something like that. Sure. All right. So there are some C sharp like functionality that we can use within queries, link queries to be specific. Now, this is much improved in three dot x. Two dot x will up until two two, you could make C sharp and link code and really cause performance problems. They corrected that problem in three. So I don't want to go into great detail in three. But you can use, right? EF core three and later, right? So we're in 3.1, which is the LTS release. So I know the versioning thing is all kind of catty want this. So there are certain functions that you can use that are C sharp functions that translate perfectly into SQL, by the SQL translation engine within the SQL server provider. There are some that don't. So I have some examples here of ones that do and I'm gonna show you later a couple that don't. So we can use, for example, we want to get a person where the last name contains the letters UF and remember it's case insensitive in SQL. So we don't have to worry about case changing as we would in C sharp. Now this translates into a like statement, but we also have some EF functions. This is a static class that provides a like function that makes database people more comfortable. And so here we have EF dot functions dot like and then last name, now we have to add in the percent signs ourselves, but this makes it, starts with ends with, you know, all those things you can do with a like operator in SQL. Another built-in function is is date, which this is really nice because it translates to the T SQL is date function. So if you have a string field and you want to know if it's a date, typically you would have to pull that back into your client code, run some C sharp against it, but the helper function is date will then translate to the is date function on the SQL server side. We can also do the aggregate functions that you would expect. So some counts, average, max, min, any and all all translate to the correct SQL on the server side. Now the watch out here is if you don't know whether or not it's going to translate or not, go ahead and write it, but make sure you have an integration test around that so you can exercise that code because if it can't be translated, then you'll get an exception saying this doesn't work. Right. So an example of that, let me scroll down here to my problem queries. There's a first or default, which we'll talk about shortly, but first or default is a link thing that says get me the very first one or if there aren't any, return me the default for that data type. Now these are all objects, so that would be a null. So it would make sense that we have a last or default. We don't. So if I were to run this code right here, we would get an invalid operation exception and the message says this can't be translated into SQL by the SQL Server Translation Engine. So it's very clear at runtime, what works and what doesn't work. However, it's not clear at design time. I can't stress enough how much you have to have some sort of testing. Technically that would be integration testing because we're going to database, but unit testing, testing, whatever, just have tests around these so you can know whether or not you're pushing the limits of what link can do. So is there a way to see what the translated SQL is for each of your queries? In EF Core 3.1, not really. You can, we can talk about it when we get into later episodes. It's a lot of unicorn fairy dust magic to get it to work and it doesn't work all the time. However, in EF Core 5, which is the next version because we're skipping for kind of like we skipped through to hold the hand grenade. In EF Core 5, there is going to be an extension method off of your query that will then output the string. Okay. The other option is with logging turned on. EF Core will log the SQL that is built at runtime when that SQL is executed. So you can see the query in the log. So you have console logging turned on or things like that. The other option, the traditional way is pull up SQL Server Management Studio, turn on Profiler and see what comes across. Right. Now that will only work if it can be translated. If it can't be translated, it'll never come across SQL Server Management Studio. You'll get the invalid operation. But that's kind of a good thing to know, right? Yes. If you're new to link or you're just not sure if you got the statement right, but you know SQL, right? Then that would seem to be a good way to check. Yeah. What's going on? You should be checking anyway. Now, these are very simple queries and it's really hard to mess them up. But when you get into a really complex... It's not that hard to mess things up. Right. But when you get into much more complex, real-world situation, sometimes you're not going to like the query it generates. Most of the time, I think you will. I've talked to a lot of different, a lot of my friends who are hardcore DBAs and we've looked at the output and more often than not, and I won't put a percentage because 90% of all statistics are made up anyway, but more often than not, the query that gets generated is really, really well done. But sometimes it's not. Right. There are some things in the 2.x world that cause M plus one issues. So you do want to examine the queries that are generated and what I would recommend in the 3.x world or 3.1 world is go ahead and fire up profiler and just watch your queries come across. Okay. If you don't like the query, we'll talk about it in a later episode. There's ways that you can build your own query and get the same benefit of using Link. Okay. So now let's get into some more samples. I'm going to jump down here to line 101 in the sample if you're following along at home. And I want to talk about getting by primary key. There is a find method. Now I could always do, as we saw before, person dot where primary key value equal equal, you know, the number we're passing in. Find is a special function. And what find does is first looks in the change tracker to see if you already have this object in a DB set. And if it does, it returns the object that you have. If it doesn't find it already retrieved, then it will go retrieve it. So it's, it can be a slight performance boost. And we can also use find with complex keys. So we just have to pass in each of the properties in order, right? So we have to get the order, right? And that's again, just a shortcut for doing a where clause with first or default, right? So we can do first or default, which says get me the first one that matches this, or if none of them match, then return null. There's also a first, right? Which if it doesn't find one will then accept or throw an exception. Now we can shortcut the syntax. These two lines, 113 to 114, are exactly the same. It's just shorthand. If the where clause can fit into the first default, we can do it right there. And then single or default, I don't recommend using. What single or fault does server side is select top two, pull those two records back if there are two records and if there's more than one, then it throws an exception. So it's actually doing the checking for the single on the client and pulling back two records from the server. Now is that a huge performance problem? No. But again, it's all about critical thought. And if you need to have that type of accepted behavior, then go ahead and use single or single or default. All right, last thing I want to talk about is paging. Paging is extremely simple. Now this is using skip and offset in SQL, but I can say skip or type skip, and it's going to skip a certain number of records and then take the number of records that I want. Obviously you probably want to have an order by to have that make sense. Otherwise it's going to skip and take in database order. So that's a quick whirlwind tour around querying records and entity framework core. So it's link. It's just link. It's link. Yep. Sweet. The important takeaway from this is when it executes. And I can't stress that enough because it can really crush your performance if you don't understand that process. Right. And the docs, there are pretty good docs on this? True or false? There are pretty good docs on it. I don't know if it calls out as strongly as I do about when the execution happens, but the docs on on querying data are really, really good. All right. And this is the same link that we've been enjoying for many, many years, or is this a new and improved link? It is the same link with extensions built in for entity framework core. Okay. Awesome. All right. So that's a good overview of querying. Yes. And that'll cover us for this episode. So in part four, we're going to look at what? We're going to look at getting related data in queries and creating projections. Fantastic. So I hope you guys are enjoying this, and we will see you next time on Visual Studio Toolbox.