 On today's Visual Studio Toolbox, Phil Jepixi is here to kick off a series on how to use Entity Framework Core. Hi, welcome to Visual Studio Toolbox. I'm your host, Robert Green, and joining me today is Phil Jepixi. Hey, Phil. Hey, Robert. How are you? Great. Welcome back to the show. Thanks. Good to be back. It's been a while. Phil Jepixi is my guest. That means we're doing a multi-part series. Absolutely. We've done some Epic ones. We did an eight-parter on design patterns a while back. Yeah, still get lots of great feedback on that series. Yes, I loved that one. Also did one on unit testing. Was that like four episodes or something? Or five. I don't know. It all runs together when you're having so much fun. I know. Today, we're going to kick off, and we're going to take a different approach to our series this time. We're going to do Entity Framework Core, but we're going to start from the beginning. This is going to be a introduction to Entity Framework Core. Correct. Just if you're watching this and you've been using Entity Framework Classic, or if you've been tinkering with Entity Framework Core, you might want to just double the speed on this one. Later in this series, we'll do a deep dive into all the awesome sauce that is in Entity Framework Core, but we want to get people up to speed on ORMs in general, and of course, specifically in these first few episodes. This is part one of a multi-part, and is it safe to say that Entity Framework Core is to Entity Framework as ASP.NET Core is to ASP.NET? So it is the version that is aimed at the.NET Core runtime? Yeah. So if you want to make that analogy, it is important to note that both ASP.NET Core and EF Core are complete rewrites of their classic versions. So they are similar in name, they are similar in goal, but under the covers they're vastly different. But the way you use them is more the same than different. Is that correct? That's correct. Yeah. There's a lot of new features that came in EF Core that we'll cover in later episodes, but from the general perspective of an object relational mapper, which is what EF is, the general concepts are the same. Okay. So I guess that's a great place to start. Because anytime I see a technology, I like to ask what is this thing? Why was it invented? What problems is it trying to solve and then how do you use it? So what is an object relational mapper? What is entity framework? So databases tend to store their data very relationally, hence the name relational databases. So you have a customers table, you have a products table, you have very specialized, it's called normalized tables based on the data. Problem is our applications don't work that way. We tend to work with domain objects. So if you're looking at a customer, you're also going to want to see that customer's orders and their order details and their products related. So we have this disparity between what our application needs and what is optimized for the database. So that's where the ORM or the object to relational mapper comes in. So it enables us to very, very simply convert what is good for the database into what is good for the application. Okay. So it's not about how you talk to the database because we've been able to talk to the database for a very long time. In fact, before entity framework came on the scene, we were using ADO.net and before that we're using ADO and before that other thing. So it's less about the underlying talking to the database and more about how you get to work with the data in your apps. Yeah, correct. In any framework in any framework core, both use ADO.net or ADO.net core now, I guess you would call it under the covers. Okay. So they're still using data providers, they're still using connections, command objects and things like that. It just extracted away from you. So I look at it this way. Everybody really appreciates indoor plumbing. We don't have to walk out to the outhouse, we don't have to dig a hole if we have to go to the restroom. Data access, I consider analogous to plumbing. Almost every application needs it, but I don't have customers coming up to me saying, hey, I need you to build a data access layer for me. I have customers coming up saying, I need an app that does this. Data access is part of that. So there's not a perceived value add into data access. So anyway, we can streamline that process to provide more functionality for the business and spending less time on the plumbing. So I look at any framework core as the plumbing that every application needs, but we don't necessarily want to build. So in days past and we'll see demos of this, we would have to create a command object, open a connection, write the query somehow to get data from the database, pull that back into either a data set, if you're looking at the older technology, or a data adapter, and read through that data and populate models. Well, there's not a whole lot of business value add to that. It's a requirement, it's a necessity. That's plumbing code. That's plumbing code. Right. Okay. So the goal as always is to spend less time writing plumbing code and more time writing the logic that's useful for the app, business logic. Okay. So that sounds like a good overview of what it is. Let's see how it works. Sure. So I have some pre-built code here, but most of it we're going to be typing live. So that's always comical, but it'll work. So one thing I want to point out before we dive into the code is there's two different paradigms that you can use with Entity Framework Core. The first paradigm, which is what a lot of people who aren't using it will come into, is you have this existing database, and you want to start using Entity Framework Core to query the data and to work with the data, but how does that work? Because we only have this thing called Code First, and Code First in my mind is one of the worst names that Microsoft Marketing came up with, because what we really mean is Code Centric. So in EF6 or EF Classic, we had four ways of talking to a database. We had with a designer from an existing database, a designer with no database, and then what they called Code First with an existing database and Code First with no database. So I want to stress that when we talk about Code First, we're really talking about Code Centric. So what I have here is a very simple project. Now I'm using the preview of EF Core 5. We can switch that back to 3.1. And the way we would do that very simply is Microsoft Entity Framework Core in the NuGet packages. We go down here to SQL Server, or Microsoft Entity Framework Core SQL Server, and we can roll back to what is the actual supported release, and then we click on update. And this is how you would install Entity Framework Core into your project. You pick the version. We can also use the command line for this, but Entity Framework Core different than prior versions of ORMs is very modular. So we just pull down those bits that we need. So what I have pulled down here is Entity Framework Core SQL Server, and then we're going to change the version of Entity Framework Core design, which allows us to do things like migrations and scaffolding. So this is how we set up our original project. It is just a concept. So for the purposes of what we're doing in these intro episodes, we're going to do stuff that's in 3.1.3, right? We are going to do stuff in 3.1, yes. So there's no reason not to use the stable version. Yeah, the EF-5 at the time of this recording is very, very early, and I don't want to confuse people with things that might be changing as it gets farther in the release cycle. Okay, okay. Okay. All right, so now we've got our project set up, and what I want to do is I want to take AdventureWorks, which I have installed to local DB, and I want to run a command line interface process that will then take the existing database and create all of the Entity Framework Core objects that I need to work with it. So this is called scaffolding. We can also do the reverse, which we'll see in a later episode where we actually build our database from code. But we're going to start with the existing database and we're going to then create all of the objects that we need, and then we'll talk about what those objects do and see examples of that. And does this have to be a command line thing? Are there tools that make this easy? There is an add-in that was written by, and I don't remember his name, somebody on the EF Core team, but it's not built individuals to you out of the box. And the reason for that, and this is probably important to point out in all of .NET Core, the command line interface is the queen of the root. So you can do everything that you need to do with .NET Core and EF Core from the command line, and you can do most of what you need to do through Visual Studio. The reason that the team is focusing on a command line is not everybody's using Visual Studio. I could be developing this in Visual Studio Code, or I could be using Rider by JetBrains, or I could use any host of IDEs, notepad if I was really not into productivity, and then do everything from the command line. So the command line comes first, and then Visual Studio follows. Okay. All right, so if I go to a command line, which I happen to have right here, the first thing that we have to do is install the .NET Core global tool, and I've already done this. And what global tools are in .NET Core, it's similar to, if you're familiar with the old .NET classic, putting something into the GAC, so you can access it from anywhere. To run any of the command line tools in EF Core, we actually have to install the global tool for EF Core, and there are now tools that run similar to how it was with .NET classic of putting things into the GAC or global assembly cache. So it's .NET tool, and we want to install it globally, and then the tool is called .NET-EF. Now, I already have it installed, and that should probably type in the word install. That's what we get for, and it says it's already installed, but you're gonna have to run this command, and what I'll do is I'll copy it into this file that is coming with the show notes right here. And so we've installed the tool that we need, and now that we have the tool installed, we have a host of things that we can do with the tool. So we run .NET, which is a command line interface for all .NET Core, and we type EF. We get the magic unicorn, and then here are some of the options that we can do. So we are going to create a DB context in all of the pieces and parts, and then this will give us a good vehicle to talk about what the pieces and parts of EF Core are, why they're used, and see how they use them. Now, I have pre-baked this command, it's a little lengthy. So let me clear the screen, we'll go into here, and I'm going to do the .NET EF, so that's the EF Core command line tool. I'm gonna create a DB context, and we're done. So what has this done for us? So all of AdventureWorks is now here with different models and a DB context. And the DB context is what? Yep, so great segue, thank you, my Ed McMahon. So the DB context is like the Master Control Program if you're a Tron fan, right? It's MCP, it is the heart and soul of how Entity Framework Core works. It derives from DB context. It contains a mechanism to tie into the database to open connections and do things like that. It works very closely with the change tracker which we'll talk about very soon. It holds all the DB sets, which we'll also talk about, and then it has a relationship with a database provider. So when we installed the package, Microsoft.EntityFrameworkCore.SQL server, we were adding the SQL server data provider into our installation. So let's talk about the pieces and parts of the DB context. We first have a DB set or a whole series of them, and these are specialized collections that wrap our tables. Now, the way that I have built this and the way the scaffolding works right now in 3.x is we have table per class. So every table in the database is represented by a DB set. So we have address, address type, et cetera, et cetera. And that's the object relational mapping because you have an address object which is mapping to the address table in the database. Correct. And the mapping comes in when we look at the table itself. Now, this is just one to one, but it has a schema and a table. Now, I would typically have called this address says in the SQL server side in address. So you can see that we can map our class to a table called foo if we wanted to. It wouldn't be very good for follow-on developers, but we can. We can also change the column names. Now, these are just here because of capitalization, right? So in .NET, we like to have i lowercase d in SQL server. They tend to do i uppercase d in a case sensitive. So you can see how we're mapping columns to different columns in the database. So the column in the database is state province ID and in your object, in your class, it's called city. No, it's attributes rolled down. So let me do this, create a little space to make it more readable. Oh, there we go. Right, so SQL server is not case sensitive unless you specify that during the install. C sharp is case sensitive. In C sharp land, we like to have, we don't like to have two capitals together. Yeah, okay, I see. The scaffolding then changes that for us. We can also change column types. So we're specifying the SQL server column type of date time so that we are very specific in what type of date com because there are options in SQL server and we only have to date time in C sharp. So if you wanted to rename a column, you would just change the property name in the class. You wouldn't touch that column attribute because that has to be what's back in the database. Correct, I could call this foo. Right. All right, so let's go back into the DB context. Now, this is particularly huge because of course I picked adventure works. Right here is how we configure the DB context. Now, this is different than prior versions of NED framework but of course this episode is for people who haven't used it. A key tenant throughout all of .NET course depends the injection. So we're actually going to configure connection string and other options by injecting them into the DB context. So we have the full power of that. If we scroll down a bunch, we have this on configuring. A lot of people brand new to EF and EF core will use this. I'm gonna tell you don't. We're actually going to delete this. What the on configuring method does is it provides a fallback mechanism to say that if you haven't configured this class, then we'll go ahead and configure it for you. So what we have here is a hard coded connection string. So if you roll this into production and you forget to configure it, now you're trying to connect to your development database. So we don't like to use this. So instead of deleting it, I'll just comment it out so that people who are looking at the code can see what we were talking about. The other thing that the DB context does for us is provides a mechanism to further shape our database. So this is more of that mapping. So you saw how you can change the table names, the schema, the data type of a column, the column name. This is where we go into much more detail of how we want that database shaped. So for the address entity, and we just call the C sharp objects entities, typically, we're going to in the database, provide a comment. We're now gonna provide a unique index for the row GUID. We're also adding an index for state province ID. Here's a multi-column index. Now we can talk about whether multi-column indexes make sense or not. I didn't build this database as just an example. That's an important point though, that all of this stuff is coming out of the database. Yes, so we scaffold this from the database into our C sharp project. So on the flip side, if you need to add these things into your database and you're building your model from code when we do a migration, which is the reverse process, which we'll talk about in a later episode, this is how you would configure a database. The beauty of this whole process is what I call the Homer Simpson button, right? So if I want to get everything from the database into code, I just run the scaffold command and I have everything in there. If I need to get my project, if I'm doing true code first, I have migrations and either way with these commands, I have a new developer who starts on the project, all they have to do is run this command line interface for .net CLI command and they have everything they need. So we don't have to have a build document and all this stuff to get developers up and running. Right. All right, so that is the DB context and DB set. Let's look a little more about how the DB sets work because what I want to talk about is foreign keys and relationships. So databases and tables have foreign keys. So for example, this is the person, no, I wanted to go to person because all my examples are on person. So here's a person object. And a person object has a foreign key out to this thing called business entity. There's also a business entity context that has a foreign key back to person. So how do we represent those? And we could pull up to database diagram, but I'm not gonna waste your time. Let's look at this first. So we have this business entity, which is the one end of the many. So person is on the many end of a one to many relationship between person and business entity. So from person, we have a navigation property that goes to business entity, but we have to tell entity framework core what the foreign key value is. So we have this business entity ID field that's in the table, and that is the foreign key out to the business entity table. Now there are conventions that I don't wanna go into, but for example, based on name structure in other things, entity framework core will figure some of this stuff out for you. The problem with conventions, you have to know them. And so if I didn't have this here, entity framework core would still pick up that the business entity ID is the foreign key based on the name convention. But Robert, if you're coming and following me and trying to understand this code and you didn't know those conventions, you'd be like, well, how does this work? So I am always very specific and explicit in foreign keys on navigation properties. And thankfully the people who built the scaffolding feel the same way. Now inverse property just is another helper that says, hey, entity framework core. On the other end of this, the business entity, we have this person object right here. So that is the other end. That's the inverse property. Okay. We can also be on the one end of a one to many. And the person in this database example is at the one end of a one to many relationship between the person table and the business entity context. So what we get here is a collection of business entity contacts that we can then navigate to. So these are again referred to as navigation properties on both sides. But if you think of it in terms of SQL Server, this is like saying, select star from person, inner join a business entity contact on a business entity contact.person ID equals ID. Right, this is how we would write to SQL. And this is how we're telling entity framework core how to join those things together. We'll see examples of how this works. So these properties here are collections, right? So, so they, should they be pluraled? Yeah. So this is person.business entity contact, which then represents a collection of all the contacts for that person. You bring up a great point. I hate the naming comments, the naming conventions that they use. So let's just real quickly talk about how we do it in real life. So let's use a rename function. I would call this business entity navigation. And it's going to refactor everything. Hopefully this works without breaking the example, but I have the finished product in the oven if I need to pull it out. Okay. So let's talk about this. We had a property called business entity, which is also a class. Anytime you have a property on an object the same as a class, the same as a namespace, right? You start running into name conflicts and tele-sense tries really hard to help you out. It will shoot you in the foot if you have all these names that are the same. Likewise, this should be plural. So great point. That is there's one thing I don't like about the out-of-the-box scaffolding is you can plug in your own code to change these rules. I didn't bother to do that. That's a little complex. It's easy enough just to go in and rename things. Right. Okay. Okay. So that's the DB set. And DB set has, if we just look at the DB set, let me save all this. What does a DB set give us? It gives us, in addition to being a collection, it derives from I async enumerable, I queryable, et cetera, et cetera, but we get a bunch of methods here that help us. So we have find, let's do, where are you? Here's add range, right? So we have add, add range, update, update range, remove, remove range. So these are our crud features that we can do. So if we want to add a person, we would call context variable dot person dot add and we'll see how this works. So that's what the DB set gives us. So last thing I wanna talk about before we, we're coming up against the wall time-wise here, but the last major piece of the EF core components is the change tracker. And the change tracker is what really provides the power to using entity framework core. So anytime you add data into these DB sets and you start working with them in code, the changes are tracked and that is used to build very efficient queries. That's a core of any framework core, no pun intended. Come on, you worked on that one. All right, all right, so I had that saved up. Pun intended. Next episode, we're going to dive into code to show how all this stuff works. Great, all right. So that's a good introduction to entity framework core. Just to recap, we took an existing database, ran one line of scaffolding code after installing some global tools and we now have a class or object for all of the tables and all of the underlying relationships are represented. So at this point, we'd be ready to start working with the actual data. Correct. All right, and we will do that in part two and so we will see you next time on Visual Studio Toolbox.