Entity Framework Core Part 4

On today’s Visual Studio Toolbox, Part 4 of our Entity Framework Core series. Phil is going to show how you query related data. [MUSIC] >> Hi, welcome to Visual Studio Toolbox. I’m your host, Robert Green. Joining me is Phil Japikse. Hey, Phil. >> Hey, Robert. How are you? >> I’m great. We are on Part 4 of our multipart series on Entity Framework Core. So last episode, we did querying and simple querying, in fact.

00:36 - 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. So we encourage everybody to, actually, play with this. Run all the samples, see what’s happening, step through them. If you’re familiar with LINQ, then you get a big leg up on and if you’re new to it, then the samples are a great way to figure out what’s going on. >> Correct. So let’s talk about related data. So very seldom in an application, do you need just a single table, and we talked about that in episode 1 about the ORM part, the Object Relational Mapper, 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, and so we map between them.

01:47 - 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 DSQL, 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 LINQ 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? Do you see my screen? >> 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.

03:07 - Then again, we just changed the Lambda in here to be the business entity. >> So in the first episode, we noted that even though persons have multiple e-mail addresses, so the scaffolding called the e-mail address. Can you show us? Show in the entities where that comes from. So e-mail address is actually a collection. So again, in the episode 1, 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.

03:53 - >> We talked about that neither most like this naming convention, now, 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#, and it broke all the sample code, so we reverted. So yeah, good call. E-mail address is actually a collection. >> That’s stored in a separate table or an object. Yeah, e-mail address. That’s an object. >> Yeah, e-mail address is a different object which maps to the e-mail address table, and then business entity, where did it go? >> There it is. Public virtual business entity. >> Right there. There we go. You saw it before I did. I was already off the screen. We talked about this, maybe it’s a good time to review. E-mail address is the many and of a one-to-many.

04:58 - So person is one, e-mail address is many. With the businesses 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 would be many passwords. So in this case, we are getting the collection, the many of the [inaudible].

05:28 - >> So this is a join again, a person, a table, one-to-many to e-mail addresses? >> Correct. 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 sales person.

06:15 - 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. >> Then getting back to what we were talking about, query execution. These queries have not actually run? >> They have not run yet.

06:51 - Nothing has run because I have off- >> You’re just defining queries that are probably going to be run. >> Yeah, and I’m not running them here in this sample code. If we wanted to run them, we would just add but actually I’m using a discard here. So we’ll say var q equals and then we’ll say q.ToList will actually execute the query. Now, I’m going to point out, you can see ReSharper is providing me some information about the datatypes here and all along the way.

07:30 - So at this point, I have and I IncludableQueryable a person Employee but the end result is an I IncludableQueryable a person in SalesPerson. You asked in the last episode about the types, and my point was you don’t care. 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. 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 joins. >> So you said implicitly loading, what does that mean? Is that the opposite of explicitly loading? >> Yes.

So implicitly loading means that we’re doing 08:25 - 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. 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 can get it on 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 data back. So it wasn’t always the most efficient, but that has been greatly improved in EF Core 3.1, and will be keep getting diversions. I juggle up my head. In EF Core 5, which would probably be called EF5 because they’re dropping Core at Microsoft Marketing at work, got to love it.

09:25 - Anyway, the next version will be even better, but they spent a lot of time into 3.X world, completely rewriting that LINQ translation engine. >> So it makes sense that you would drop the Core and just call it.NET 5. Was there an Entity Framework 5? I know there was a 6, there was 4. Was there ever a 5? >> Yeah, there was a 4, 5, and 6. They skipped three like the holy hang grenade. They skipped 2, I don’t remember. They skipped one of them. I don’t know why I’m not going to say that on tape. >> Well, they skipped things to make it easier to keep track of things. Things get out of sync, and we can talk about this. There are good reasons for skipping things.

10:12 - But anyway, was there an Entity Framework 5? If you call Entity Framework Core, Entity Framework 5, does that confuse in your opinion? >> Yeah. There was an EF5. There’s 4, 5, and 6. Six is the last of the classic versions. I don’t know, I’m going to continue calling the Core things Core and the old thing’s classic. So that’s the way I’m going to roll. So the advantage here with the implicitly loading, you’re in a web world. 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 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.

11:09 - So you don’t care quite as much about chattiness, because you’re always connected. So you can also explicitly load related data. Let’s say we start off with a person and we’re going to get that person back, and we don’t know if we’re going to need the employee and e-mail addresses. So we can minimize the database call by just saying, “Get me the person,” and then the app later says, “You know what? I need the employee reference and the e-mail collection off of that person.” Again, because we’re in a WPF always connected type world, this is really not a performance had to doing this and 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 e-mail addresses for this person.” >> Got it.

12:03 - >> Now, in a web world, we’re not going to want to do that, we’re going to want to do more of this construct because communication between your web server and a database server should be held at a premium standard and not be so chatty. >> Cool. >> So we now have this data whether we’re explicitly or implicit, but sometimes we don’t need all the data. I mean, very seldom in the database world, do you do select star and join five tables? You don’t need all of 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 object instances from that data.

12:55 - So here’s the projection has creating an anonymous object. So let’s say I’m going to get the person, but I only care about their first name, middle name, and last name. So the select construct then says, “Just get me these properties.” Because I don’t have an object type defined here, what I get back is a list of an anonymous object. So I can’t pass that out of the method, but I could certainly iterate through and use in certain ways.

13:33 - None of those objects can be beneficial, but what’s better is having what we would call a ViewModel. The way I look at a ViewModel, it’s parts of different models squished together to be more beneficial for the consumer, which is typically the view. >> When you say ViewModel, this is a different usage of that term, then the ViewModel is in MVVM, Model-View-ViewModel. Is that correct? >> No. Same term. >> Same term? >> Yeah. So in fact, I think I did some MVVM shows with you. The view model in MVVM is different model’s squished together to be more convenient for the view. But it’s a model for the view.

14:25 - So when we use the term ViewModel, 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 ViewModel 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 ViewModel here, which is just a subset of the person class. Now typically, I might have e-mail address in here, some other models all squished together.

15:14 - 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 going to display that in the company directory, so why even bother transport that to the view that’s going to render that? We just need the directory information; 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 PersonViewModel. Almost the exact same syntax, but we’re saying select new PersonViewModel, assigning those properties based on the properties coming from the entity. >> Okay. So I understand what you’re doing. What’s the difference? Because that runtime, newAnonList and newMVList both have three properties: first name, middle name, last name, and the SQL statements that are sent back are just selecting three fields. >> Yeah.

So from a database perspective, 16:35 - 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 isn’t 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 going to 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 view model. So this allows me to pass it out. It won’t even let me, so I can’t.

17:32 - There is no strongly typed thing, and I can’t have var be the return for the method. >> Yeah. Got it. Okay. >> All right. So if we’re dealing with collections, there’s Select, but there’s also SelectMany. Now, the difference here is if I just do a Select, what I get is an IQueryable of a collection of e-mail addresses; probably not what we were looking for. So we’re dealing with collections that we want to use in our projection, and we use SelectMany. It flattens that list into the list that we are targeting.

18:22 - So SelectMany on the e-mail addresses brings me back an IQueryable of e-mail address, which probably is what you’re wanting to do. Now, there are times where you want to have that IQueryable of the collection of the e-mail address. But it is a subtle difference in the term Select versus SelectMany, but a huge difference on the result set. So SelectMany flattens your lists. Select just takes your list and says, “Oh, you want this property? Okay, here’s the property.” So if I wanted to still have that collection here, I could do x.

E-mailAddress which 19:06 - should be addresses as we discussed. Now, in here, let me get rid of that, I have first name, middle name, last name, and the collection of e-mail addresses. In this case, that’s probably what you meant to have. But if I want to get all of the e-mail addresses in the system, I don’t want to have, essentially, a collection of collections, and I want to have just the list. >> Okay. >> That’s really it. That’s a shorter episode of it. I just want to talk about how you load related data, and then how you can use that in creating view models. >> All right.

19:49 - >> So we’re 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 [inaudible]. >> Actually, I’m going to use a couple of minutes since we have the time. >> Okay. >> Aren’t collections all IQueryable? So what is an IQueryable of ICollection? It seems redundant. >> IQueryable is used by EF Core to set up something that will eventually query the database. >> Okay. >> You can use LINQ queries against collections and innumerables and lists, but that isn’t going to query the database.

20:26 - So if I set up an ICollection and then say to list, it’s not going to go through EF Core. >> Got it. Okay. 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 IQueryable of ICollection as opposed to an IQueryable. 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 what’s coming back, a new PersonViewModel. >> Yeah. >> 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.

21:18 - >> I will admit that I use var too much in my coding. I should use it less. That way, the people who are following behind me can see what is explicitly happening as opposed to having to assume. >> Right. Well, a lot of people will say you should use var as a way of not duplicating the class name. So you wouldn’t say int x equals new int, or string equals new string, or list equals new list, I guess. >> Correct. If it is obvious, what you’re- >> Right. When it’s obvious, you can use var. If for no other reason, then you don’t have to type long names twice.

22:04 - But in this case, certainly, if you’re learning, maybe it’s the thing to do. Maybe not use var so you actually learn what’s coming back. >> Correct. >> Okay. >> 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. >> All right. So that covers related data and projections. Are we ready to start modifying data yet, or we’re still going to query in the next episode? >> Nope. Next episode, we’re actually going to persist the data to the database. >> All right.

22:46 - So we hope you guys are enjoying this again, and we will see you next time on Visual Studio Toolbox. [MUSIC] .