 On today's Visual Studio Toolbox, part four of our Entity Framework Core Series, Phil is going to show how you query related data. Hi, welcome to Visual Studio Toolbox. I'm your host, Robert Green, and joining me is Phil Jepixi. Hey, Phil. Hey, Robert. How are you? Right. We are on part four of our multi-part series on Entity Framework Core. Last episode, we did querying, and simple querying, in fact. Today, we're going to look at related data and projections and you're going to explain what that means. Correct. Just to back up, in the last episode, we did talk about simple querying, but more importantly, we talked about when those queries actually fire. Yes. All of this code is available. It's in the show notes, and so I encourage everybody to actually play with this, run all the samples, see what's happening, step through them. If you're familiar with link, then if you get a big leg up on it, if you're new to it, then the samples are a great way to figure out what's going on. Correct. Let's talk about related data. Very seldom in an application, do you need just a single table? We talked about that in episode one about the ORM part, the object relational map or why we need it, because databases are optimized one way, applications are optimized another way. So databases are optimized based on tables and applications are typically optimized based on domain objects. So we map between them. So the queries we saw last episode were extremely trivial and probably won't ever be used that much. What's much more common is we want to do the equivalent of joins in T-SQL, where we're pulling back not just a person, but also their e-mail addresses or the business entities they're tied with or the employee records. So what I want to show you today is how you get that related data. I also talk about some fixes to the link translation engine in EF Core 3 over what we were seeing in EF Core 2. So let me share my screen. All right, Robert, do you see that? I do. All right. So let's just start off with a very simple include. We want to get a person record. Actually, this doesn't have a where clause, so it's going to get all person records and all of their e-mail addresses. The way we do a join in EF Core is we use the include keyword. So this will do a left outer join. They're always left outer joins when we use include to get all the e-mail addresses. If we want to include the person and their business entity, and again, this is the Northwind database, then again, we just change the Lambda in here to be the business entity. So in the first episode, we noted that even though people, persons have multiple e-mail addresses. So the scaffolding called it e-mail address. So can you show in the entities where that comes from? So e-mail address is actually a collection. So again, in the episode one, you ran scaffolding that generated all of this code for you, and it called e-mail address. It's a collection, but it's singular. So we talked about that neither of us like this naming convention. We want to call e-mail addresses, then we changed the name and we ended up breaking the scaffolding because the inverse properties are all using magic strings, as opposed to the name of construct that we can do in C-sharp, and it broke all the sample code. So we reverted. So yeah, good call e-mail address is actually a collection. And that's stored in a separate table or an object. Yeah, e-mail address, that's an object there. E-mail address is a different object which maps to the e-mail address table. Okay. Right? And then business entity, where does it go? There's somewhere. There it is, public virtual business entity. Right there, there we go. Yeah, you saw before I did, I was already off the screen. So this is, and we talked about this, maybe it's a good time to review, e-mail addresses is the many end of a one-to-many. So person is one, e-mail address is many. With the business entity, this is actually a one-to-one with person being the parent. But we can also do, for example, here is password where it is the one end of the one-to-many. If we go into the business entity table, there will be many passwords. Okay. So in this case, we are getting the collection. So this is a join, again, of person, table, one-to-many to e-mail addresses. Correct. Okay. This is also a join, they're left outer joins as they're set up this way. And that's the one end of a, this is actually a one-to-one, which I tend not to use, but it's good that the example Northwind database has one-to-one so you can see them, because just the fact that I don't use them doesn't mean that you don't use them. Then we can also chain related calls. So we have include employee, and then off of employee, we have a connection to salesperson. So we can keep walking down the line, we don't have to always phone home to start over. So this would be equivalent of select star from person P, left outer join employee on whatever the foreign key is, and then we're going to left outer join salesperson to employee. So we're not going all the way back to person. Okay. And then getting back to what we're talking about, query execution, these queries have not run yet? They have not run yet. Okay. Nothing has run because I have- You're just defining queries that are probably going to be run. Yes. Yeah. And I'm not running them here in the sample code. If we wanted to run them, we would just add, well actually I'm using a discard here. So we'll say var q equals, and then we'll say q.to list, will actually execute the query. Okay. Now I'm going to point out, you can see resharper is providing me some information about the data types here, and all along the way. So at this point, I have an i-includable queryable of person employee, but the end result is an i-includable queryable of person and salesperson. You asked me in the last episode about the types, and my point was you don't care, right? As a line of business developer, we really don't care. And this is why, because we get nested types within types when we start doing real world type queries. All right, so that is implicitly loading related data, and that is the way we really want to do it, because it's going to build that single query to go out and get it with the joints. So you said implicitly loading, what does that mean? And is that the opposite of explicitly loading? Yes, so implicitly loading means that we're doing these includes, and we want it all in one fell swoop. When we explicitly load, we're actually loading them a piece at a time. Okay. Now, one of the problems we had with these types of queries in EF Core, less than three, is there were certain situations where we could get an N plus one query situation where it would build however many joints we have, it would run that many plus one query to get the database. So to get the data back, excuse me. So it wasn't always the most efficient. That has been greatly improved in EF Core 3.1, and will be even better in three, or I'm sorry, I keep getting the versions jumbled up my head. In EF Core 5, which will probably be called EF 5 because they're dropping core at Microsoft marketing at work, I'd love it. Anyway, the next version will be even better, but they spent a lot of time into 3.x world completely rewriting that link translation engine. So it makes sense that you would drop the core and just call it .NET 5, but given that there was an entity, was there an entity framework five? I know there was a six, there was a four, was there ever a five? They skipped three, like the holy hang grenade. Right. So they skipped two. I don't remember. They skipped one of them because they wanted to, I don't know why, I'm not gonna say that on tape. Well, you skip things to make it easier to keep track of things, things get out of sync. We can talk about this. There are good reasons for skipping things. So, but anyway, was there an entity framework five? And if you call entity framework core, entity framework five, does that confuse in your opinion? There was an EF five, there's four, five and six, right? Six is the last of the classic versions. So, I don't know, I'm gonna continue calling the core things core and the old things classic. So that's the way I'm gonna roll. Okay. All right, so the advantage here with the implicitly loading, you're in a web world, right? You want to really not be super chatty. So you're going to implicitly call with these includes to go and execute the data. But we also use EF core with WPF. We do a fair amount of WPF work for things like shop floor automation and inventory control systems. And WPF and that rich client experience is so much better. So you don't care quite as much about chattyness, right? Because you're always connected. So you can also explicitly load related data. And let's say we start off with a person and we're gonna get that person back. And we don't know if we're going to need the employee and email addresses. So we can minimize the database call by just saying get me to person. And then the app later says, ah, you know what? I need the employee reference and the email collection off of that person. Again, because we're in a WPF always connected type world, there's really not a performance to doing this. It might be a little more performant because we can then go back later and say, hey, give me that employee record for this person and give me the email addresses for this person. And then we can start to scan it. Now in a web world, we're not gonna wanna do that. We're gonna wanna do more of this construct because communication between your web server and a database server should be kept at a premium. It should be held at a premium standard and not be so chatty. Cool. So we now have this data, whether we explicitly or implicit, but sometimes we don't need all the data, right? In the database world, do you do select star and join five tables? You don't need all the fields, you just need certain fields. So with EF Core, we can do something called projections where we take little bits of pieces of all the data coming back and we can create new objects or new object instances from that data. So here's the projection as creating an anonymous object. So let's say I'm going to get the person, but I only care about the first name, middle name and last name. So the select construct then says, just get me these properties. And because I don't have an object type defined here, what I get back is an anonymous, well, it's a list of an anonymous object. So it can't pass that out of the method, but I can certainly iterate through and use in certain ways. Nom is objects can be beneficial, but what's better is having what we would call a view model. And a view model, the way I like to look at a view model, it's parts of different models squished together to be more beneficial for the consumer, which is typically just you. When you say view model, this is a different usage of that term and then the view model we use in MVVM, model view view model. Is that correct? Same term. Same term? Yeah, view model, it's, yeah. So I've done a bunch of, by the fact that I think I did some MVVM shows with you, the view model in MVVM is different models squished together to be more convenient for the view. It's a model for the view, right? And so what, when we use the term view model, I guess loosely defined is you're taking models, which we've been using the term entity here. So maybe view entity would be more consistent with how we've been doing this show, but we talk about entities as representing the data objects or the tables. So a view model is just taking bits and pieces of those and putting them together as a convenient transport mechanism for the required data for the consumer. So let's say we've created a view model here, right? Which is just a subset of the person class. Now, typically I might have email address in here, some other models all squished together. I wanted to keep the demo simple and say, I just want this subset of the person. A good example of this, let's say that our person table had salary information in it. And we're never gonna display that in the company directory. So why even bother transport that to the view that's gonna render that? We just need the directory information, right? Building number, mail stop, phone extension, things like that. So instead of doing an anonymous object like we do here, we can actually create a new list and let's change this away from the var of the person view model, almost the exact same syntax, but we're saying select new person view model, assigning those properties based on the properties coming from the entity. Okay, so I understand what you're doing. What's the difference? Because at runtime, new and on list and new MV list, both have three properties, first name, middle name, last name and the SQL statements that are sent back are just selecting three fields. Yep, so from a database perspective, they're doing the exact same thing. Okay, so at runtime, they're exactly the same. The difference is because I have a strongly typed model here, I can pass this result out of this method. Okay. This is an anonymous object. Anonymous objects have significant limitations. So what I would use an anonymous object for is I could then iterate through, do some other things, but I'm not gonna be able to pass an anonymous object out of this method. I would have to either create a dynamic, which is a whole another set of issues or cast it into a person model. So this allows me to pass it out. This, it won't even let me, right? So I can't, there is no strongly typed thing and I can't have var be the return for a method. Yep, got it, okay. All right, so if we're dealing with collections, there is select, but there's also select many. Now, the difference here is if I just do a select, what I get is an I queryable of a collection of email addresses, probably not what we were looking for. So when we're dealing with collections that we want to use in our projection and we use select many, it flattens that list into the list that we are targeting. So select many on the email addresses brings me back an I queryable of email address, which probably is what you're wanting to do. Now, there are times where you want to have that I queryable of the collection of the email address, but it is a subtle difference in the term, select versus select many, but a huge difference on the result set. So select many flattens your list, select just takes your list and says, oh, you want this property? Okay, here's the property, right? So if I wanted to still have that collection here, I could do x.email address, which should be addresses as we've discussed. And now in here, I have, let me get rid of that, I have first name, middle name, last name and the collection of email addresses. In this case, that's probably what you meant to have, but if I want to get all the email addresses in the system, I don't want to have essentially a collection of collections, I want to have just the list. Okay. And that's really it. That's a shorter episode, but I just want to talk about how you load related data and then how you can use that in creating view models. All right. Making up our last episode went a little long. Our apologies, but there are some important topics in there. So we're making it up, we're giving you back. Actually, I'm going to use a couple of minutes since we have the time. Okay. Aren't collections all queryable? So what is an I queryable of I collection? It seems redundant. I queryable is used by EF Core to set up something that will eventually query the database. Okay. You can use link queries against collections and enumerables and lists, but that isn't going to query the database. So if I set up an I collection and then say two lists, it's not going to go through EF Core. Got it. Okay. And then secondly, you had said earlier that typically you'll just do var result one equal because you don't care about what gets returned. So in that case, you wouldn't necessarily know that you're returning an I queryable of I collection as opposed to an I queryable. Is that correct? So typically you're correct that you don't care and you're just going to use var because you just, in this case down here, I could use var because I know it's coming back a new person view model. Yep. When you are doing projections, I highly recommend that you don't use var so you can see what's coming back to make sure that you understand the process. Okay. I will admit that I use var too much in my coding. I should use it less that way that people who are following behind me can see what is explicitly happening as opposed to having to assume. Right. A lot of people will say you should use var as a way of not duplicating the class name. Right. So you wouldn't say int x equals, you know, new inter or string equals new string or list equals new list, I guess. Correct. Yeah. But in this case because- It's obvious what you're doing. Right. When it's obvious you can use var if for no other reason and you don't have to type long names twice. But in this case, certainly if you're learning maybe it's a thing to do, maybe not use var so you actually learn what's coming back. Correct. Yeah. Absolutely. But there again are times if you're doing a new anonymous object with projections you have to use var because there isn't a strongly typed thing that you can cast that to. All right. So that covers related data and projections and are we ready to start modifying data yet or are you still gonna query in the next episode? Nope. Next episode we're actually gonna persist the data to the database. All right. So hope you guys are enjoying this again and we will see you next time on Visual Studio Toolbox.