This post is going to focus on a specific feature available in EF Core 6 database first development. The examples are going to be referring to the Adventure Works (2019) database, which can be found here. First, let's take a look at extended properties and how we can view them on our database objects.
The AdventureWorks2019 database is very well documented with metadata on almost all tables, but for this post, we'll be looking at the
HumanResources.Employee table and specifically the extended data related to the columns of the
Employee table. In order to see the existing metadata for the columns in
HumanResources.Employee, we can use the following query.
SELECT ext.major_id, c.name as [Column Name], ext.[value], ext.name as [Extended Property Name] FROM sys.extended_properties ext JOIN sys.columns c ON ext.minor_id = c.column_id AND c.object_id = ext.major_id WHERE ext.major_id = OBJECT_ID(N'HumanResources.Employee') AND class = 1;
As you can see from the results grid in the screenshot above each column has an extended property that includes both a name and a value. The name of the extended property is important for EF Core to recognize during the scaffolding process, so it must be set to
MS_Description. In this case, the extended properties were already created for the columns in the
Employee table, but let's pretend for a moment that they weren't. How would we go about adding an extended property so that it could be consumed by EF Core scaffolding tools? To do this we need to use
sp_addextendedproperty which is a stored procedure that will add extended properties to the database object specified in the arguments. Here is an example using the
EXEC sp_addextendedproperty @name = N'MS_Description', @value = 'M = Married, S = Single, C = It''s Complicated', @level0type = N'Schema', @level0name = 'HumanResources', @level1type = N'Table', @level1name = 'Employee', @level2type = N'Column', @level2name = 'MaritalStatus';
The snippet above will add an extended property to the
MaritalStatus column with a name of
MS_Description and a value of
M = Married, S = Single, C = It''s Complicated. If you'd like to learn more about
sp_addextendedproperty check out the full documentation here. With the extended properties in place, we can now look at what needs to be done to actually scaffold the EF Core entity.
When using the database first approach with EF Core 6, the scaffolding tool is a must-have in my opinion. The scaffolding tool handles a great deal of the boilerplate code and also conforms to EF best practices in terms of configuring database contexts and entities. Let's touch on the prerequisites for scaffolding.
First, we'll install the
dotnet-ef as a global tool with
dotnet tool install --global dotnet-ef. This installs the
dotnet-ef tool globally and makes it available to use from anywhere on the host machine. Next, we'll be going into an existing project and the only requirement for that project is to have the
Microsoft.EntityFrameworkCore.Design package installed. At this point, we are ready to run the scaffolding command for the
Employee table. For the purpose of this post, I won't go too deep into the scaffold command options. If you'd like to learn more, check out the documentation here.
dotnet ef dbcontext scaffold "CONNECTION_STRING" Microsoft.EntityFrameworkCore.SqlServer -o Models -t Employee --context-dir Context -c AdventureWorksContext
This will result in the
Employee entity being created in the
Models directory. If we look at the entity, we will see that all of the extended properties on columns or tables with the name
MS_Description result in XML doc comments corresponding to the respective class or property.
I find this feature to be really useful for storing metadata on the database objects, but still being able to keep that information available when interfacing with the database through entity framework. As long as the comments provide value at the database level, I think they will provide equal value when included in the corresponding entity.