 Hi, welcome to Visual Studio Toolbox. I'm your host, Robert Green. And today we're going to talk about entity framework power tools with Eric Jensen, who is joining us via Skype from halfway around the planet. Hey, Eric. Hey, Robert. Nice to speak to you. Thanks for joining us for this. You're welcome. And we'll hope that the technology holds out for half an hour. We're keeping fingers crossed, yes. Eric is an extension builder. You wrote the fantastic SQLite admin tools. That is an extension that plugs into Visual Studio. Highly recommend that. We did an episode on that a while back. Yes, I just looked at the VS Toolbox site on Channel Line. And it was aired in March 2015. So that's a couple of years ago. Yep. So we'll have a link to that in the show notes. You guys, if you're doing anything with SQLite, you should definitely check that out. But today, we're going to talk about some new extensions you've written to help entity framework users. Yes. Let me first just do a little plug for my Toolbox. I made this little site here that helps you get an overview of the different editions of the Toolbox. And I also have accompanying libraries and command line tools. And there's a link on GitHub to this website, which is also hosted on GitHub, where I present all the various editions of the Toolbox and so on. OK, cool. Yes. But today, it's about entity framework power tools, both entity framework six power tools, and the new kit on the block entity framework core power tools. So that's a focus for today. Excellent. For the EF6 power tools, they already existed and was available on Marketplace for a while, but they were not available for Visual Studio 2015 and 2017. And in agreement with the EF team, I have now shipped a version that works with Visual Studio 2015 and 2017. OK. What you can basically do with the EF6 entity framework six power tools community edition is that you can view a model of your database. It used to also have a reverse engineering features, where you can take an existing database and create a code-based model, either an edmx file or sorry, a code-first model based on an existing database. But that feature is no longer supported in this new edition of the power tools for entity framework six. There are other alternatives that you can do use, and I will briefly touch upon those during the demo. Yes. So here's a quick question. Why is the community edition? Is there a pro edition and an enterprise edition? It's just because the entity framework team used to own this tool. And in this world of open source, I took the liberty of forking their repository. And just to make it stand out that this is not a, the power tools were never a supported tool, but this is even less supported than the unsupported tool supplied by the EF team, if that makes any sense at all. So you took the power tools they wrote, which is an open source project. You forked it. And now you're releasing your version of it and calling it the community edition. Correct. So I'm taking kind of responsibility for it. And it's also open source. So I've already received the first community contribution from a user that fixed the long standing bug in the tool. So that was a really nice experience. All right, cool. Yes. Yep. Let's see it in action. Let's just have a quick demo. Yep. I thought I had started Visual Studio already, but maybe I didn't. Sorry. Oh, yes, I have. It's the wrong Visual Studio I'm looking at. There we go. There we go. Yes. So now what I have here is a project that includes a reference to entity framework 6, actually entity framework 6.2, which is currently in beta. Hopefully, it will be released sometime in the future. But it's currently in beta. That's the latest release of the good old entity framework 6. For the power tools for this, simply allows you to generate a read-only view of your model. Before you can do that, you need to have a model. So let's generate a model from a database. And in order to do that, as I mentioned, there's a couple of options. It used to be a feature of the power tools. It's not anymore. One of the options to create a code first model is simply to go in and add an ndo.net entity data model to your project. And then you get the good old entity data model wizard that has been around for many, many years. But recently, this wizard had a new feature attitude called code first from database, which allows you to generate a code-based model, db context, and Poco classes from an existing database. And I have already established a connection to my local db database. And I have installed the Chinook database, a very good demo database that's available on GitHub. So I will call this the model. We'll call it Chinook. And then this wizard will enumerate all the tables in that database. And I can scaffold some code based on those tables. That's what I'm doing now. I'm clicking Finish here in the wizard. And it's now inspecting the database, looking at the tables, and generating tables and the corresponding columns, and making that into Poco classes and a corresponding db context. And finally, it will open the db context in the editor. So now, when I right click the db context file, I have this feature here called entity framework. And that is the entity framework six tooling. And one of the important features of this is view entity data model read only. So let's try to run that. Behind the scenes, it will generate a edmx file of your model and show that as a read only file, just to diagram the relationship between the various components of your model. That looks familiar. Yes, exactly. And you can expect the properties of each of the entities and so on like you have been used to before. So if you want to understand how entity framework interprets your code first model, this tool can help you do that. Very cool. That's basically it. So that is basically the only feature that's kind of left in the EF6 community is now that it doesn't have reverse engineering anymore. As you saw, you can use reverse engineering. It's built into the EF entity framework six tools that supplied with Visual Studio. But that's also another option that you can use. It's called entity framework reverse pogo generator. And it's a TT file, T4 file is also called, which is a highly customizable template that you can use to generate code similar to what this wizard I just demonstrated here generated. But it's infinitely more customizable. So that was all about the entity framework six power tools community. You can see I released this a couple of months ago and it's already had 6,000, 7,000 downloads. So it's obviously something that some people have been waiting for. Right. Very cool. Yep. The new kit on the block is the EF core power tools. EF core is a parallel product to entity framework six, has the same API, but is from the ground up a completely new framework for data access in the Microsoft stack. Yep, written on top of .NET core. It's actually in version two. It's a .NET standard 2.0 library. Very good. Yes. So that means that it will support any of the known Windows platforms, sorry, any of the known platforms that .NET can run on. That includes .NET core, .NET framework, 4.6, .1, and higher. Samarin also has in their very latest version has support for .NET standard 2.0. And Windows will have it very soon, I understand. Right, UWP apps will have support for it. Yes, UWP apps will have it very soon. So you'll be able to do entity framework from inside your UWP apps. That's correct. So another interesting tip. Yeah, I used to be writing Windows 8 apps, UWP apps that you were in the sandbox and you couldn't just call your SQL database. But now you would be able to use entity framework and talk to SQL database the same way you've been able to do from WPF and WinForms forever. That's correct. One of the new exciting features about .NET standard 2.0 it includes system.data and system.data.sqlapkind also for UWP. Right. Exactly, which means you can address a SQL database or an Azure SQL database directly from within your UWP app. Some people think that's cool. Yeah, I'm one of them. Yes, I think it's pretty cool as well. But maybe it's the wrong pattern that you're accessing your database directly from an app like that. I mean, you can get into that discussion. That's an architectural discussion, but not being able to do it at all because the feature didn't exist is one thing. And then you have the feature and there are times it makes sense. And absolutely there's scenarios where it makes a lot of sense and a lot of people have been asking for this, including myself, so I'm delighted to see it happen. But I think we'll do an episode on that later on. Yes, yes. We digress. OK, EF Core Power Tools is, well, it's like a twin brother of the EF6 Power Tools. So it has reverse engineering like the EF6 Power Tools used to do and it has model generation just like I showed you before, just different. It's only available for Visual Studio 2017 just because .NET Standard 2.0 really only works well with Visual Studio 2017. And it only works with a limited number of database providers. The EF6 Power Tools really works with any database that EF6 supports. But this little tool of mine only supports SQL Server, Azure SQL Database, SQLite and SQL Server Compact. But I mean, I can pull myself together and add support for more databases if there's a request for it, of course. Right. It's open source on GitHub. Yeah, so other people could add that potentially. So other people could potentially help me add that capability, yes. Yep. OK. So let's have a look. Wow. It kind of complained a little bit there. So what I have here is an entity framework. It's just a console app. I'm not really demonstrating .NET Standard or .NET Core or anything. It's just a standard console app. Notice that in order to use anything that runs .NET Standard 2.0, you need to target .NET 4.6.1 or higher. OK. Yeah, that's just how it is. I have installed the entity framework core provider for SQL Server in this project. So the basic Nuke package is already installed in this project. And when I right-click the project, I get the EF Core Power Tools command. And you can see it has reverse engineer and then ad-db-contact model diagram, which are the features I will demonstrate. OK. So let's try to have a look at reverse engineering. So now I'm going to connect to the same. Chinook database connected to previously for the EF6 Power Tool. Didn't make any typing mistakes here. And then next step is you can choose which tables to script. You can also save. If you have a huge database, you can actually save the selection of tables. So if you want only a subset, you can actually save that subset as a text file. And then you can also load that subset as a text file. Let's just go with everything tonight. So in this dialog, you can specify what you want to call your context. It was called model 1 in the previous wizard. What is your namespace? It will pick up the namespace from the project, of course. And you can put all your code in a folder in this project. You can specify what parts of code you want to generate, whether you want to generate everything, or just the DP context that kind of pulls your Poco classes together in a single unit of work and the repository. Or just your Poco classes in this context is called entity types. And there's various other options. And if the provider that was chosen is not installed, this tool was also offered to install a required Nukit packet for the provider. That's nice. So for this demo, we'll just generate everything. And the provider for SQL Server was already installed. Oh, that was fast. So yeah. That was fast. That was fast. Yes, faster than EF6. Wow. Yeah. So now we have generated Poco classes for each of the tables in the Cnuk database. There's an example here. And the context that sticks everything together in DB sets and configures various properties for each of the entities. So let's see if we can build this. Yeah, it built. So now I will show how to generate the model diagram. Just right-click again and choose this feature. And then it will generate a diagram that includes all the tables in the demo. So you're using code maps to do this. I'm using DGML to do this. Yes, same diagram feature that CodeMap uses. Yes, very interesting. What it is is actually just an XML file with a special syntax that is able to be displayed in Visual Studio in a nice manner. So what was the decision that drove that versus recreating the same database diagram that you did in EF6? Lack of knowledge of how to do it. Fair enough. Yes. I already have one of the reasons why I chose this was I'm comfortable with this feature because I already have a feature where I draw this diagram based on a database schema. OK. Yeah. Good. That was my main reason. But of course, the model concepts of EF Core and EF6 are radically different. So maybe it makes sense to not diagram it in the same way because then people expect it to be the same and it's certainly not. Yeah, that's interesting. But for this diagram, it's not that exciting because it basically just diagrams everything that's in our model one to one. All these boxes, smaller boxes represent the properties. Let's zoom in a little bit here. Represent each of the properties in the classes because this is just based on existing database. And what are the different colors mean? That's actually a good question. Somewhere you can see the legend here. Ah, there it is. OK. So the gray is entity type. If it's a primary property, it's green. If it's an optional property, it's I don't know what you call that color. Green, brown, yes. If it's blue, it's foreign key. I think it's solid. Assuming that my eyes are good enough to see that. Yeah. But yes, and of course, if something is both a foreign key and a primary key, it's kind of the primary key that wins. So I can't really visualize everything about the model. But you can hover over each of these and see all the properties. And you can also see them in the properties window here. The standard properties window when you navigate the graph. So yes, I know this can quickly get kind of technical about how models are made in EF. I'm not even sure I'm capable of explaining it all. But I'm trying to visualize everything that makes sense to visualize based on the information I have available. Right. So this is, as I said, this is just a one-to-one visualization of what is already in the database. So but let's try to look at something that's a little bit different. So let's include this other file I've already prepared at home where we have a model down here. Can you see it should assume in a bit? No, that's good. OK. So we have a block class and we have a post class. They both have IDs and some data properties. But the only thing that's linking them together in this model is that the block class contains a list of posts. There's nothing in post pointing back to block and so on. So this is a perfectly valid way of building a model in code. But this tool, this diagramming tool, lets you see what is the actual model that EF builds based on this code-based model. So let's try to create the diagram again. Oh, maybe we have to build. Let's see what happens. So now it's created a model here. And if you look at the post, if we go back to the code and look at the post, you can see it had one, two, three properties. But in the diagram, there's actually four properties. There's a block ID as well. And it's probably impossible to see. But that's actually what is called a shadow property. So in order to make a coherent model, entity framework has decided by building the model that the post should actually have a block ID that you point back to the block. So this tool enables you to tell, to visualize the actual model that the entity framework works with based on your code-based model. Cool. Yes. Yes, that concludes the demo. All right. Let's get your face back here. There we are. You're back. I'm back. That's really cool. Really cool stuff. So what are your plans for enhancing these in the future? What are the work items that you're thinking about doing or ones that you would ask for help for? I'm thinking about, as I already mentioned, to maybe include more databases. I know that there's a PostgreSQL provider out there. And it would be nice to have that included in this package as well, not just to limit the number of supported databases. And that's the main thing that is bugging me now that's not really working. I'm sure if somebody was very clever with DTML, they could make the diagram better. But luckily, it's open source. So maybe somebody will chime in there. It serves its purpose. It tells you what's going on. So that's good. Yes. Yes. All right. Very good. Well, thanks for coming on and showing us these things. You're welcome. Next time you're in Redmond, we'll have you in the studio live. But this worked pretty well. Since you're on the other side of the world. Yes. It's like nine hours difference or something like that. Yeah. Well, there is that, too. Right. All right. So hope you enjoyed that. If you're using Entity Framework, either Six or Core, you should definitely check these tools out, as well as the other tools that Eric has built. Thanks for coming on. And we'll see you next time on Visual Studio Toolbox.