Using Reverse Engineering Tools

Out of the box there are no visual tools for this process, but future releases from Microsoft might include options. In Chapter 19, I will introduce a few additional tools that might assist you with this process, as described here:

• PowerShell cmdlets for the NuGet Package Manager Console (PMC) within the Visual Studio development environment. These commands can be used not only in .NET core projects but also in “full” .NET Framework projects.

• The command-line .NET Core tool (called dotnet.exe in Windows), which can also be used independently of Visual Studio and Windows. However, this is available only for .NET Core or ASP.NET Core–based projects.

Reverse Engineering with PowerShell Cmdlets

For reverse engineering, two NuGet packages are relevant in Entity Framework Core 2.0.

• The package Microsoft.EntityFrameworkCore.Tools is needed at development time in the current startup project of Visual Studio.

• The package for each Entity Framework Core database driver (for example, Microsoft.EntityFrameworkCore.SqlServer or Microsoft.EntityFrameworkCore.Sqlite) is needed in the project where the program code was generated and in the project with the tools.

For the example in this chapter, go ahead and create these two projects:

• EFC_Tools.csproj exists only for the tools. This project will be a .NET Framework console application (EXE).

• The program code is generated in EFC_WWWWingsV1_Reverse.csproj. This project is a .NET Standard 2.0 library and thus can be used in the .NET Framework as well as .NET Core, Mono, and Xamarin.

In this case, you have to install the packages first, so do the following in EFC_Tools.csproj:

Install-Package                                   Microsoft.EntityFrameworkCore.Tools Install-package Microsoft.EntityFrameworkCore.SqlServer

Do the following in EFC_WWWingsV1_Reverse.csproj:

Install-package Microsoft.EntityFrameworkCore.SqlServer

Or do the following for SQLite:

Install-package Microsoft.EntityFrameworkCore.Sqlite                                                    

When you run the package installation commands for the tools, a total of 33 assembly references will be added to a .NET 4.7 project (see Figure 4-1). In Entity Framework Core 1.x, there were even more, including ASP.NET Core assemblies , even though you were not in an ASP.NET Core project at all.

Figure 4-1 The Microsoft.EntityFrameworkCore.Tools package will add 33 more packages!

If a code generation command was executed without the previous package installation, the developer sees an error in the Package Manager Console (see Figure 4-2).

Figure 4-2 Scaffold-DbContext without previous package installation

Generating Code

The actual code generation then runs after the installation of the two packages via the Scaffold-DbContext cmdlet, to which at least the name of the database provider and a connection string have to be transferred by the developer.

Scaffold-DbContext -Connection "Server=DBServer02;Database=WWWings;Trusted_Connection=True;MultipleActiveResultSets=True;" -Provider Microsoft.EntityFrameworkCore.SqlServer

This command creates classes for all the tables in this database in the project that is set as the current target project in the NuGet Package Manager Console. For database columns that could not be mapped, Scaffold-DbContext issues warnings (see Figure 4-3).

Figure 4-3 Scaffold-DbContext warns that a column of type Geography has been ignored

Alternatively, you can use a schema or tables to limit the generation to specific database schema names or table names. For both parameters, you can specify several names separated by semicolons.

Scaffold-DbContext -Connection "Server=DBServer02;Database=WWWWingsV1;Trusted_Connection=True;MultipleActiveResultSets=True;" -Provider Microsoft.EntityFrameworkCore.SqlServer -Tables Flight,Person,Pilot,Passenger,Airport,Employee,Flight_Passenger -force

You can use the table names with or without schema names (in other words, you can use Flight or operation.Flight). But beware that if a table with the same name exists in multiple schemas, then a specification without a schema name will generate entity classes for all tables of that name from all schemas.

By default, the code is generated in the project currently selected in the NuGet Package Manager Console in its root directory using the default namespace of that project. With the parameters -Project and -OutputDir, the developer can influence the project and the output folder. Unfortunately, with the existing parameters, it is not possible to direct the code generation of the entity class and the context class into different projects.

With respect to the data model shown in Figure 4-4, the Scaffold-DbContext cmdlet now generates the following outputs:

• One entity class each in POCO style is generated for each of the six tables, including the N:M intermediate table Flight_Passenger, which has always eliminated the classic Entity Framework in the object model. Unfortunately, Entity Framework Core version 2.0 does not yet support N:M relationships; it only replicates them with two 1:N relationships, as does the relational model.

• A context class derived by the base class Microsoft.EntityFrameworkCore.DbContext is derived. Unlike before, this class is no longer a wrapper around the ObjectContext class but a whole new, stand-alone implementation. The name of this class can be influenced by the developer with the command-line parameter -Context. Unfortunately, specifying a namespace is not possible here. Using points in the parameter value is acknowledged by Visual Studio with “The context class name passed in is not a valid C# identifier.”

• If code generation is not possible for individual columns, there will be a yellow alert output in the Package Manager Console. This happens for the Geometry and Geography data types, for example, because Entity Framework Core is not yet supported.

Figure 4-4 Example database for the Word Wide Wings airline (version 1)

Figure 4-5 shows the generated classes for the sample database from Figure 4-4, and Figure 4-6 shows the object model.

Figure 4-5 Project with the generated classes for the sample database from Figure 4-4

Figure 4-6 Object model of the generated classes

Unfortunately, you cannot give the code generator of Entity Framework Core any settings for the namespaces; it always uses the project’s default namespace for both the generated entity classes and the context class. Therefore, you should set the default namespace in the generation project so that it fits at least to the entity classes. Then you just have to manually change the namespace of the context class.

Unlike in reverse engineering with the ADO.NET Entity Framework, the Entity Framework Core does not automatically include the connection string in the application configuration file app.config or web.config. The connection string is in the OnConfiguring() method of the context class after generation, and it is up to the software developer to find a suitable and possibly backed-up location for it.

A pluralization (in other words, changing the table names to plural in the class names) does not take place. So far, there is no option in Entity Framework Core to do this; however, this is not a big loss because the pluralization service works only for English table names.

Looking at the Generated Program Code

The following listings show the program code generated by Scaffold-DbContext for the context class and, by way of example, for the entity classes Flight and Passenger.

The definition of how the object model is mapped to the database schema continues as in the classic Entity Framework in these three ways:

• Conventions that are applied by Entity Framework Core automatically

• Data annotations that are applied by the entity classes and their members

• Fluent API used in the OnModelCreating() method of the DbContext class

The code generated by the Entity Framework Core tools focuses on the third way. The OnModelCreating() method in the context class is accordingly full of Fluent API calls. But conventions continue to work, for example, that the properties of the class in the standard have the same names as the columns in the table.

So far, the assistant for the classic Entity Framework in Visual Studio also used data annotations, which are now no longer used in the generated program code. If you want to get the old behavior back, you can use the parameter -DataAnnotations in Scaffold-DbContext.

The Fluent API contains the following definitions:

• It defines the table names in the database if they differ or if they have a schema name different from dbo, using ToTable().

• It defines names for primary key columns and indexes, using HasName().

• It defines column types and column properties if the .NET type names are not unique to a data type in the database management system, using HasColumnType(), IsRequired(), HasMaxLength().

• It defines default values for columns, using HasDefaultValueSql() or HasDefaultValue().

• It defines the cardinalities between tables and their foreign keys, using HasOne(), HasMany(), WithOne(), WithMany(), HasForeignKey(), and HasConstraintName().

• It defines the indices, using HasIndex().

• It determines whether a column’s content must be reread after inserting or modifying an Entity Framework Core record because it is generated by the database management system, using ValueGeneratedOnAddOrUpdate() and ValueGeneratedOnAdd() and ValueGeneratedNever().

• It sets cascading delete settings, using OnDelete().

In the source code, the Fluent API configuration is organized by classes, as shown here:

modelBuilder.Entity<Person>(entity => {...});

Within these method calls you will find the configuration for the individual columns of these tables.

entity.Property(e => e.PersonId)...

Compared to the previous ADO.NET Entity Framework, there are some syntactical changes and also improvements. So, the index configuration is now much more concise.

The following features are just like the classic Entity Framework:

• The reverse engineering code generator does not create inheritance relationships between entity classes, even though this would be possible as in the case of Passenger ➤ Person, Employee ➤ Person, and Pilot ➤ Employee. Instead, the code generator always generates associations and associated navigation properties. Such inheritance relationships must be defined by the developer later and then remove the navigation properties.

  • For example, the navigation properties in the entity classes are declared virtual, even though the Entity Framework Core lazy loading does not yet support what virtual is necessary for.

  • For example, navigation properties for sets are declared with ICollection<T> and are then filled in the constructor with the new HashSet<T>().

• For each entity class, there is a DbSet<T> property in the context class.

The generated source code can be changed (see Listings 4-1, Listing 4-2, and Listing 4-3), such as if you want to have property names other than column names in the database in the object model. You would do this with the Fluent API method HasColumnName("column name") or the data annotation Column("column name").

Listing 4-1 Generated Context Class

using System;
using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Metadata;

namespace EFC_WWWingsV1_Reverse
{
    public partial class WWWingsV1Context : DbContext
    {
        public virtual DbSet<Airport> Airport { get; set; }
        public virtual DbSet<Employee> Employee { get; set; }
        public virtual DbSet<Flight> Flight { get; set; }
        public virtual DbSet<FlightPassenger> FlightPassenger { get; set; }
        public virtual DbSet<Metadaten> Metadaten { get; set; }
        public virtual DbSet<MigrationHistory> MigrationHistory { get; set; }
        public virtual DbSet<Passenger> Passenger { get; set; }
        public virtual DbSet<Person> Person { get; set; }
        public virtual DbSet<Pilot> Pilot { get; set; }
        public virtual DbSet<Protokoll> Protokoll { get; set; }
        public virtual DbSet<Test> Test { get; set; }

        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            if (!optionsBuilder.IsConfigured)
            {
#warning To protect potentially sensitive information in your connection string, you should move it out of source code. See http://go.microsoft.com/fwlink/?LinkId=723263 for guidance on storing connection strings.
                optionsBuilder.UseSqlServer(@"Server=.;Database=WWWingsV1;Trusted_Connection=True;MultipleActiveResultSets=True;");
            }
        }

        protected override void OnModelCreating(ModelBuilder modelBuilder)                                                                              
        {
            modelBuilder.Entity<Airport>(entity =>
            {
                entity.HasKey(e => e.Name);

                entity.ToTable("Airport", "Properties");

                entity.Property(e => e.Name)
                    .HasColumnType("nchar(30)")
                    .ValueGeneratedNever();
            });

            modelBuilder.Entity<Employee>(entity =>
            {
                entity.HasKey(e => e.PersonId);

                entity.ToTable("Employee", "People");

                entity.Property(e => e.PersonId)
                    .HasColumnName("PersonID")
                    .ValueGeneratedNever();

                entity.Property(e => e.HireDate).HasColumnType("datetime");

                entity.Property(e => e.SupervisorPersonId).HasColumnName("Supervisor_PersonID");

                entity.HasOne(d => d.Person)
                    .WithOne(p => p.Employee)
                    .HasForeignKey<Employee>(d => d.PersonId)
                    .OnDelete(DeleteBehavior.ClientSetNull)
                    .HasConstraintName("FK_MI_Employee_PE_Person");

                entity.HasOne(d => d.SupervisorPerson)
                    .WithMany(p => p.InverseSupervisorPerson)
                    .HasForeignKey(d => d.SupervisorPersonId)
                    .HasConstraintName("FK_Employee_Employee");                                                                              
            });

            modelBuilder.Entity<Flight>(entity =>
            {
                entity.HasKey(e => e.FlightNo);

                entity.ToTable("Flight", "Operation");

                entity.Property(e => e.FlightNo).ValueGeneratedNever();

                entity.Property(e => e.Airline).HasMaxLength(3);

                entity.Property(e => e.Departure)
                    .IsRequired()
                    .HasMaxLength(30);

                entity.Property(e => e.Destination)
                    .IsRequired()
                    .HasMaxLength(30);

                entity.Property(e => e.FlightDate).HasColumnType("datetime");

                entity.Property(e => e.Memo).IsUnicode(false);

                entity.Property(e => e.PilotPersonId).HasColumnName("Pilot_PersonID");

                entity.Property(e => e.Timestamp).IsRowVersion();

                entity.Property(e => e.Utilization).HasColumnName("Utilization ");

                entity.HasOne(d => d.PilotPerson)
                    .WithMany(p => p.Flight)
                    .HasForeignKey(d => d.PilotPersonId)
                    .HasConstraintName("FK_FL_Flight_PI_Pilot");
            });

            modelBuilder.Entity<FlightPassenger>(entity =>
            {
                entity.HasKey(e => new { e.FlightFlightNo, e.PassengerPersonId })
                    .ForSqlServerIsClustered(false);

                entity.ToTable("Flight_Passenger", "Operation");

                entity.Property(e => e.FlightFlightNo).HasColumnName("Flight_FlightNo");

                entity.Property(e => e.PassengerPersonId).HasColumnName("Passenger_PersonID");

                entity.HasOne(d => d.FlightFlightNoNavigation)
                    .WithMany(p => p.FlightPassenger)
                    .HasForeignKey(d => d.FlightFlightNo)
                    .OnDelete(DeleteBehavior.ClientSetNull)
                    .HasConstraintName("FK_Flight_Passenger_Flight");

                entity.HasOne(d => d.PassengerPerson)
                    .WithMany(p => p.FlightPassenger)
                    .HasForeignKey(d => d.PassengerPersonId)
                    .OnDelete(DeleteBehavior.ClientSetNull)
                    .HasConstraintName("FK_Flight_Passenger_Passenger");
            });

            modelBuilder.Entity<Passenger>(entity =>
            {
                entity.HasKey(e => e.PersonId);

                entity.ToTable("Passenger", "People");

                entity.Property(e => e.PersonId)
                    .HasColumnName("PersonID")
                    .ValueGeneratedNever();

                entity.Property(e => e.CustomerSince).HasColumnType("datetime");

                entity.Property(e => e.PassengerStatus).HasColumnType("nchar(1)");

                entity.HasOne(d => d.Person)
                    .WithOne(p => p.Passenger)
                    .HasForeignKey<Passenger>(d => d.PersonId)
                    .OnDelete(DeleteBehavior.ClientSetNull)
                    .HasConstraintName("FK_PS_Passenger_PE_Person");
            });

            modelBuilder.Entity<Person>(entity =>
            {
                entity.ToTable("Person", "People");

                entity.Property(e => e.PersonId).HasColumnName("PersonID")                                                                              ;

                entity.Property(e => e.Birthday).HasColumnType("datetime");

                entity.Property(e => e.City).HasMaxLength(30);

                entity.Property(e => e.Country).HasMaxLength(2);

                entity.Property(e => e.Email)
                    .HasColumnName("EMail")
                    .HasMaxLength(50);

                entity.Property(e => e.GivenName)
                    .IsRequired()
                    .HasMaxLength(50);

                entity.Property(e => e.Memo).IsUnicode(false);

                entity.Property(e => e.Surname)
                    .IsRequired()
                    .HasMaxLength(50);
            });

            modelBuilder.Entity<Pilot>(entity =>
            {
                entity.HasKey(e => e.PersonId);

                entity.ToTable("Pilot", "People");

                entity.Property(e => e.PersonId)
                    .HasColumnName("PersonID")
                    .ValueGeneratedNever();

                entity.Property(e => e.FlightSchool).HasMaxLength(50);

                entity.Property(e => e.Flightscheintyp).HasColumnType("nchar(1)");

                entity.Property(e => e.LicenseDate).HasColumnType("datetime");

                entity.HasOne(d => d.Person)                                                                              
                    .WithOne(p => p.Pilot)
                    .HasForeignKey<Pilot>(d => d.PersonId)
                    .OnDelete(DeleteBehavior.ClientSetNull)
                    .HasConstraintName("FK_PI_Pilot_MI_Employee");
            });

        }
    }
}

Listing 4-2 Generated Entity Class Flight

using System;
using System.Collections.Generic;

namespace EFC_WWWingsV1_Reverse
{
    public partial class Flight
    {
        public Flight()
        {
            FlightPassenger = new HashSet<FlightPassenger>();
        }

        public int FlightNo { get; set; }
        public string Airline { get; set; }
        public string Departure { get; set; }
        public string Destination { get; set; }
        public DateTime FlightDate { get; set; }
        public bool NonSmokingFlight { get; set; }
        public short Seats { get; set; }
        public short? FreeSeats { get; set; }
        public int? PilotPersonId { get; set; }
        public string Memo { get; set; }
        public bool? Strikebound { get; set; }
        public int? Utilization { get; set; }
        public byte[] Timestamp { get; set; }

        public Pilot PilotPerson { get; set; }
        public ICollection<FlightPassenger> FlightPassenger { get; set; }
    }
}                                                                              

Listing 4-3 Generated Entity Class Passenger

using System;
using System.Collections.Generic;

namespace EFC_WWWingsV1_Reverse
{
    public partial class Passenger
    {
        public Passenger()
        {
            FlightPassenger = new HashSet<FlightPassenger>();
        }

        public int PersonId { get; set; }
        public DateTime? CustomerSince { get; set; }
        public string PassengerStatus { get; set; }

        public Person { get; set; }
        public ICollection<FlightPassenger> FlightPassenger { get; set; }
    }
}

Seeing an Example Client

The program shown in Listing 4-4 uses the generated Entity Framework context class and the entity class Passenger.

The method illustrated creates a new passenger, attaches the passenger to the DbSet<Passenger>, and then stores the new passenger in the database using the SaveChanges() method.

Then all passengers are loaded for control, and their numbers are printed. Listing 4-4 shows a version of all passengers with the name Schwichtenberg. This filtering then takes place in RAM with LINQ to Objects over the previously loaded passengers. Figure 4-7 shows the output on the screen.

Figure 4-7 Output of the sample client

Listing 4-4 Program Code That Uses the Created Entity Framework Core Model

public static void Run()
  {
   Console.WriteLine("Start...");
   using (var ctx = new WWWingsV1Context())
   {
    // Create Person object
    var newPerson = new Person();
    newPerson.GivenName = "Holger";
    newPerson.Surname = "Schwichtenberg";
    // Create Passenger object
    var newPassenger = new Passenger();
    newPassenger.PassengerStatus = "A";
    newPassenger.Person = newPerson;
    // Add Passenger to Context
    ctx.Passenger.Add(newPassenger);
    // Save objects
    var count = ctx.SaveChanges();
    Console.WriteLine("Number of changes: " + count);
    // Get all passengers from the database
    var passengerSet = ctx.Passenger.Include(x => x.Person).ToList();
    Console.WriteLine("Number of passengers: " + passengerSet.Count);
    // Filter with LINQ-to-Objects
    foreach (var p in passengerSet.Where(x=>x.Person.Surname == "Schwichtenberg").ToList())
    {
     Console.WriteLine(p.PersonId + ": " + p.Person.GivenName + " " + p.Person.Surname);
    }
   }
   Console.WriteLine("Done!");
   Console.ReadLine();
  }

Using the .NET Core Tool dotnet

When developing .NET Core projects, the command-line tool dotnet (also known as the .NET Core command-line interface [CLI]) from the .NET Core SDK can be used as an alternative to the PowerShell cmdlets ( https://www.microsoft.com/net/download/core ). Unlike the PowerShell cmdlets, dotnet is available not only for Windows but also for Linux and macOS.

This form of generation works for the following:

• .NET Core console applications

• ASP.NET Core projects based on .NET Core or the .NET Framework 4.6.2 and later

First, the package Microsoft.EntityFrameworkCore.Tools.DotNet has to be installed, which does not work via a command-line tool but only through a manual entry in the XML-based .csproj project file (see Figure 4-8):

<ItemGroup>
   <DotNetCliToolReference Include="Microsoft.EntityFrameworkCore.Tools.DotNet"     Version="2.0.1" />
</ItemGroup>

Figure 4-8 Manual extension of the .csproj file

Then you have to add the following package:

<ItemGroup>
    <PackageReference Include="Microsoft.EntityFrameworkCore.Design" Version="2.0.1" />
</ItemGroup>

However, this is also possible via the command line in the project directory, as shown here:

dotnet add package Microsoft.EntityFrameworkCore.design

Now add the desired Entity Framework Core provider, shown here:

dotnet add package Microsoft.EntityFrameworkCore.SqlServer

The following package was also necessary in Entity Framework Core 1.x but is not needed anymore in Entity Framework Core 2.0:

dotnet add package Microsoft.EntityFrameworkCore.SQL Server.design

Then you can do the code generation (see Figure 4-9).

dotnet ef dbcontext scaffold "server =.; Database = WWWings66; Trusted_Connection = True; MultipleActiveResultSets = True; "Microsoft.EntityFrameworkCore.SqlServer --output-dir model

Figure 4-9 Reverse engineering with dotnet.exe

Understanding the Weaknesses of Reverse Engineering

As in the classic Entity Framework, you can create entity types only for tables with primary keys. Composite primary keys, however, are not a problem for Entity Framework Core.

In the case of the temporal tables (called system-versioned tables) added in SQL Server 2016, the history tables cannot be mapped using Entity Framework Core. However, this is already possible for the actual table, whereby querying the historical values is possible only via SQL and so far not via LINQ.

For database views and stored procedures, in contrast to the classic Entity Framework, classes and functions cannot be generated.

Once the object model is generated using the Entity Framework Core command-line tools, you cannot update it. The Update Model from Database command available for the Database First approach is currently not implemented. You can only restart the generation. If the classes to be generated already exist, the cmdlet Scaffold-DbContext complains about it. With the additional parameter -force, the cmdlet will overwrite existing files. However, any manually made changes to the source code files will be lost.

If in a new Scaffold-DbContext command you let not all previously generated tables be generated, but only a few selected ones, then in the context class the DbSet<T> declarations and the Fluent API configurations are missing for all the tables that are now no longer generated. Once again, this is a reason to generate a project from which you can then copy the generated parts you need to another project. However, Microsoft has announced (at https://github.com/aspnet/EntityFramework/wiki/Roadmap ) that it plans to improve the tools and provide an Update Model from Database feature.

Until then, it’s the best way to at least limit the code generation that occurs in changes to new tables; the changes for new, changed, or deleted columns are better made manually in the source code. Or, after a reverse engineering of a database, you can switch to forward engineering; in that case, changes will be recorded in the object model and used to generate DDL commands for changing the database schema.