EF Core database model first - take it to the next level with Power Tools CLI | .NET Conf 2023

Transcript

[Music] awesome so do we have more great sessions coming up our next session is about Entity framework Core Power Tools CLI I'm so excited to invite Eric on stage hi Eric hi AA um so thank you so much for join joining us um we can just move forward with ef Core Power Tool CLI I guess that's a new uh tool I've never heard before yes it's a brand new Tool uh it's a bit in um preview for um about half a year waiting fortnate 8 to be released so um I'm happy to be able to finally release the proper version of it now um and um the session will be a demo of it yes awesome so whenever you're ready you can share of course so my assist is called EF core database model first uh take it to the next level with the power tools CLI uh my name is Eric Alo Yensen and you can reach me on GitHub and uh Twitter SLX uh under the tag Eric EJ uh please feel free to mention me on um Twitter if you have any qu follow-up questions for this session that do not get answered during question time and uh also if you have uh any additional questions feel free to follow up with a feature request or issue on on GitHub um but let's just uh jump straight into the demo now um I'm going to show you some here so uh for this demo I will be showing everything directly in Visual Studio code and um I have installed a couple of extensions and visual studio code to uh help me with this demo uh the mssql extension for working with SQL server and as a SQL database and I have also installed the C Del kit which allows me to work with the net project and solutions uh directly in Vis Studio sorry directly in Vis Studio code um so as you can see I don't have any folder opens or anything like that because I have this uh CF D kit install I actually get an option to create a net project down here at the bottom so let's do that um we get a selection of available. net templates for this demo I will just create a console app and we will put it in temp folder and demo so I will put it in the temp. net demo folder this new. net project which will be a console app um and I will call it netcon and now uh we can see that uh these files are added to our net demo folder um a solution file and then a the netcon project file um and in addition um since I have this uh C do kit extension installed we will immediately get a view um of the project with the solution file that was automatically created for us um and we can view uh the packages installed in the project file uh here in this uh visual representation here in our Explorer so in this demo I will show you how you can quickly get started with an existing database and since I have the SQL Server extension installed also uh it will Now list some of my data connections and uh I have a database a connection to a database called Northwind which contains a number of tables um and it also contains some stop procedures and some functions um we will try to see how we can map these and create a databased context and related entity classes using this new power tools CLI tool so first we need to go and install the tool I will go to the terminal and um just go to to the project folder so um I need to install the tool it's a net Global tool and I can install it with a net uh tool install command and the name of the tool is um just zoom out a bit is er Eric ed. efcore power tools.

CLI and I would like to install version 8 I I already have the tool installed but this is the command you need to use in order to install the tool um so let's try to run the command uh the command for running the tool is efcp and let's just have a quick look at the help help screen sorry the help screen yeah um so we have a the ability to put our connection string our Ado connection string for our database in the as a first parameter and then uh we specify the the provider and I have a convention for some uh abbreviations you can use for the provider name so for example to point to this uh database that uh we already using uh the Northwind database that I have locally um I can use a connection string similar to this uh this database is located on my local SQL Express instance but it might as well be located in on on S SQL database and then I put the abbreviation for the provider which is in this case is uh mssql when I run the tool it will now look at all the objects in the database and generate code for those tables uh store procedures and functions that it finds inside the database uh that we can then use in our project so it found a number of tables views St procedures and functions we also getting some warnings that some of the columns couldn't be mapped we'll look into how we can fix that later and finally it also created a read me file but let's just look at the what the result of running this was uh now in uh the project F folder we have a models folder which contains all the code that was generated so a a class is generated for each of the tables in the database and for each of the stop procedure result sets uh that is also in this database and then a Northwind context CS file which is this if core file that put that uh gets everything together and you can see we get red squiggles now because we don't have a reference to Entity framework in our project um let's see how we can add that so if we look at the readme file um I will just look at it is sorry in the preview mode here so we can see that we get some advice on um what we need to add to our project so we'll add this item group to our project file um let's just shut this down here and open the I double click the project file and then I will add this package reference uh to my project uh which will install the Entity framework course SQL Server provider version eight and I will save this and as you can see the packages so because I have this C de kit installed it will automatically start restoring packages for me and now this package is installed inside my project and if I open the de context I should be able to see that now uh Entity framework core is installed and I don't get any more uh squiggles uh because um now we have this uh Library installed and we know what which knows what ad DP context and so on is so um in addition to the DP context which is where there's a DB set defined for each of the tables in our database um we also have a class per table which is this ccab class which has properties that represent um our columns in the database uh but in a strongly typed C fashion of course um so if we if we're not pleased with how this code is generated for example we might want to call this class uh not not Northwind context but maybe Northwind DB context um and maybe make some other changes uh to the to the generated code we can do we can do that by modifying the configuration file so the tool installs a number of tasks um let's see yeah I don't see them now actually um but let's open the config uh the tool also added a configuration file to our code and um this configuration file defines all the settings that uh we can use in our um used to specify how the tool is uh running and um set options that allows us to customize how code is generated and uh what objects are included in the generated code so let's uh try to modify some of those uh settings uh to suit our needs better um so first of all I'm going to set soft delete um notice the experience that you get inside Visual Studio code uh there's intelligence for us explaining what all those options mean by by hovering over them um so we're going to run cleanup of obsolete files meaning that if some of the files that are in our models folder are no longer needed they will be removed or actually just soft deleted by by added to the recycle bin um so we will uh make sure that all the soft uh sorry that obsolete files are removed when we start changing options and we will change the DB context name to Northwind D context um and let's just change the root name space to net 8 instead instead of net conon um if you notice there was also code generated for a table called migration history which is not really a table that we want to include in our DB context so we can actually exclude that table by locating it in the list of tables and setting exclude Le equal true and notice how we get intelligence for the options that we can set here as well so we can also exclude with a wild card um it's not something I'm going to demo but it's something you can read more about in uh in the GitHub repository for the tool so again we can run the tool and uh to do that we simply just need to run the same command uh we did before um efpt efcp uh the ado.net connection string and the name of the provider notice that the to in Tool uh supports um not only SQL server but all basically all providers that all the mainor major providers that are supported by Entity framework core8 uh and that includes post sqlite um MySQL uh Oracle and um and I think that's about it so let's run the code again it will now respect the settings that we did in our modified uh configuration files and generate uh regenerate the code accordingly so if we look at the models folder we now have a is now the DB context is now called Northwind DB context and we can see that the name space has also changed um again if you are doubt about how to proceed you can refer to this um read me file and it also shows what you should do to register your connection and what connection string you should add to your configuration there's also links to more information uh and the GitHub repository at the bottom of this read me file that should hopefully be able to get you to some tutorials and uh potentially ways of getting in contact with uh with me as a maintainer so finally we can try to see if we can fix these um error warnings uh These Warnings we got here uh could not find type mapping so that means that we actually need to enable the type mapping for these special types special SQL Server uh data types hierarchy ID geometry and geography um so let's see if we can do that um the two additional warnings we actually not able to change until unless we change the definition of our uh of these functions um and stop procedures that doesn't a stop procedure that doesn't return um a a named result set and a function that has a TV TVP parameter which is not currently supported um but for the other ones again we can open the configuration file and we can go to the mapping type mapping section and you can see that here's already some options available to start using hierarchy ID and spatial which is the geography and um geometry classes so if we make these true and true we should be able to get rid of These Warnings we saw uh I just saved the settings file again here and again we can run the tool one more time so and the in the read me now has been a a couple of additional packages has been added to the readme um because those packages are needed uh to support mapping these additional types so we'll add these additional packages to our project so we have a particular package called net topology Suite which adds support for the spatial types and there's a new package uh called SQL Server Hier ID uh it used to be a third party Community package but it's now part of the actual product which allows you to map um hierarchy ID also notice which is a new feature in EF core 8 that um if you go to this uh special class here that um date and time columns so this is actually a date column inside a SQL server and this is a Time column and they're now automatically mapped to date only and time only um so if you go back to the configuration file before they used to be mapped to date time and time span uh so that's because this is on by default for EF core8 if you don't want to if you're upgrading and don't want to immediately start using these new types you can change this value to false and um maintain compatibility with how code was generated previously before support for date only and time only was built in so let's try to see if we are still getting These Warnings yeah we can see we only getting the two warning remaining warnings now uh which we are not able to fix at this at this point in time so finally we can actually start using uh the code that we generated in just a few seconds uh 1.5 seconds as you can see down here uh by actually using our DP context so we'll do that here and uh then we can write DB so that means we can now starting to write DV um sorry uh link to uh to entities uh queries um for example a bu notice uh the intelligence I also get from the from the C D kit um so this query will order get all the customers from the database and order order them by name and then take the turn first uh of those uh and and then return them as a list and then we can uh write them to this console this we got another weight up here and then we can right click this project and uh just start a new instance of the application or we can even start it without debuging so in the terminal uh the program will be built restored and ah yeah sorry we need to uh let's see customer doesn't contain H C so is actually contact name so sorry the name of customer is actually con contact name we'll just quickly fix that that's why in cicus we're complaining and um we also need to add the connection string to the code so we will enable on configuring here and then run output on the tool again and let's just make sure that this is saved [Music] this um will actually add the connection string to the generated code this is generally not something you should be doing um why is it still uh no what is still complaining about that use spatial tool okay so let's try to run the app again [Music] uh did we not we add not configuring DB context ah we need a public Constructor as well [Music] what I don't see the let's just go back and see if we to the config file ah true that's because we were in the wrong folder that's better ah the command the terminal was actually running in the wrong folder which tripped me up here and finally we are able to get the first 10 um contact names from a customer database so let's just switch to the PowerPoint slides here so hopefully you realize that uh with this tool and together with Visual Studio code you can get started with ef core and an existing database in seconds you can map SQL server and as a SQL functions and St procedures on top of uh just mapping tables and Views uh like you can do with the build-in tooling provided by the fcor team in addition you can actually use a SQL Server database project as a source of the schema instead of a live database uh if you want to you can customize the generated code further with T4 templates uh if you're interested in that topic I did a presentation at last year's uh. netcon which demonstrates exactly how to do that and uh hopefully you realize that you can use this tool command line tool with Visual Studio code for a good developer experience um so basically my call to action is to go and install the tool and play with it and of course please provide feedback uh via Twitter or if you encounter any issues or have a feature requests or ideas uh contact me on the GitHub repository so let's go to streamyard and stop p thank you Eric for the great presentation it was awesome we have a couple of questions in the chat um for example um where should I where should I go to find more information about this tool is there any documentation or resources avilable uh you actually go to my GitHub profile um as I mentioned earlier that's where you should contact me and then you can go to the EF Core Power Tools repository um let me just share the link uh with you in chat maybe you can share it with everyone else of course yes uh so this is where you should contact me for the tool it's actually not a Microsoft tool it's a tool that I maintain and uh it's open source tool so anybody who has good ideas from it and uh feel like they can contribute are of course also able to um to contribute to the tool but at least they can Pro provide feedback or feature requests or buck reports uh on this link here that I'm sharing for the GitHub um repository it's uh github.com Eric EJ like my tag here and then EF Core Power Tools okay thank you so much I'll definitely share that in the chat and one interesting question uh yanes found this very interesting and could it ever cause a DB lock if there are over 100 or so tables um while running the the tool or uh no uh I have users that have had thousands of tables and uh in but you generally shouldn't really be generating one code that contains 1,000 of tables uh maybe you should think about do you actually need to refer to all those tables in your code and only include those tables that you actually need uh there would be some performance impact on starting up your application if it contains a huge number of tables that are all interconnected awesome thank you so much that's all we have in the chat at the moment that was an awesome session Eric thanks for sharing with us you're welcome and I hope to see you in next sorry as I mentioned go and install the tool net tools install Eric EA efcore power tools. CLI and play with it and let me know what you think if you are you already using visual studio there is a visual studio version more graphically based version of this tool already available for you uh in Visual Studio but if you're using vs code or any any other tool that is not Visual Studio you can use this tool as an alternative okay so install EF Core Power Tools and let Eric know about your experience we appreciate your feedback awesome abolutely thank you so much and it's so nice to have you inet con hope to see see you next year thank you it was a pleasure being here