Geek Out on EF7 – Reverse Engineering First Look


Let’s take a look at Reverse Engineering with Entity Framework 7. This feature allows you to generate code from an existing database.
This is beta4 code so it is a work in progress (like me!).

I added this to Geek Out series because it’s a little advanced and could require some setup/homework on your part. I am using the new kre directory “.k” so if you have not set that up you may need to. I’ll try and help you as much as possible. One thing to watch out for is a nuget package Remotion.Linq gets pulled in from nuget.org besides the packages from aspnetvnext nightly builds.
Check out this post for help with setting up .k runtime enviornment , setup.

My environment is Windows 7 SP1, Visual Studio 2015 CTP 5, SQL Server 2014 Express with latest updates (CU6).

First I want the latest runtime which right now is 1.0.0-beta4-11117. I have a path set in my enviornment to this runtime bin directory.

latestruntime

Create a new C# ASP.NET Web Application using Visual Studio 2015 and name it EfReverseEngDemo
Select the ASP.NET 5 Empty template.
emptytemplate

Go to Package Manager Console to install some packages.
Run the following commands with package source as aspnetvnext nightly build:
Install-Package -Pre EntityFramework.SqlServer -Verbose
Install-Package -Pre EntityFramework.Commands -Verbose
Install-Package -Pre EntityFramework.SqlServer.Design -Verbose

Let’s install one package from nuget.org package source.
Install-Package -Pre Remotion.Linq
Note in picture below the nuget.org source is selected for Remotion.Linq but packages before that used aspnetvnext package source.
install packages
I added a command for EF to my project.json file. Here is what mine looks like now. Notice I updated the version of package Microsoft.AspNet.Server.IIS to beta4.

{
    "webroot": "wwwroot",
    "version": "1.0.0-*",
    "exclude": [
        "wwwroot"
    ],
    "packExclude": [
        "node_modules",
        "bower_components",
        "**.kproj",
        "**.user",
        "**.vspscc"
    ],
    "dependencies": {
        "Microsoft.AspNet.Server.IIS": "1.0.0-beta4-*",
        "EntityFramework.SqlServer": "7.0.0-beta4-12325",
        "EntityFramework.Commands": "7.0.0-beta4-12325",
        "EntityFramework.SqlServer.Design": "7.0.0-beta4-12325",
        "Remotion.Linq": "2.0.0-alpha-002"
    },
    "frameworks": {
        "aspnet50": { },
        "aspnetcore50" : { }
    },
    "commands": {
        "ef": "EntityFramework.Commands"
    }
}

Make sure your project builds without any errors.
Open a command prompt window and go the the projects directory where the project.json file resides.
Run kpm restore, this makes sure we have everything downloaded.
I can quickly verify runtime and EF versions with the following commands and they match the latest from the nightly build.
k --version
k ef --version

checklatestversions

Now enter the command k ef
You will notice a new command available “revEng” which allows us to reverse engineer code from a database. As you can imagine this could be very useful, let’s say you start a project where they have some database structures already.

newmenu

Let’s reverse engineer an existing database. I have a ZooSightingDemo database sitting in SQL Server 2014 which you might recognize from a certain blog series. It currently has three tables with some foreign key relationships.
ourdb
This is not very complicated but will certainly be interesting to see the generated code results.
Run the following command but alter your connection string appropriately.

k ef revEng Server=Glenn-PC\SQLEXPRESS;Database=ZooSightingDemo;Trusted_Connection=True;
Here my machine is Glenn-PC, using a SQL Server instance named SQLEXPRESS and my database is named ZooSightingDemo.
I now have four new C# source files added to my project and I bet they are the Entities and the DbContext. Let’s see if I’m right.

newfiles

Looks like I was correct, each entity (table) is placed in a separate file. They even commented nav properties the way I do , cool!

    public partial class ZooAnimalSighting
    {
        public ZooAnimalSighting()
        {
        }
        
        // Properties
        public int ZooAnimalSightingId { get; set; }
        public DateTime DateOf { get; set; }
        public bool IsSightingReliable { get; set; }
        public string Sighter { get; set; }
        
        // Navigation Properties
        public virtual Animal Animal { get; set; }
    }

And here is the DbContext derived class generated for me named after the database. Fluent API will be added here as needed during the code generation process.

    public partial class ZooSightingDemo : DbContext
    {
        public virtual DbSet<Animal> Animal { get; set; }
        public virtual DbSet<Zoo> Zoo { get; set; }
        public virtual DbSet<ZooAnimalSighting> ZooAnimalSighting { get; set; }
        
        protected override void OnConfiguring(DbContextOptions options)
        {
            options.UseSqlServer(@"Server=Glenn-PC\SQLEXPRESS;Database=ZooSightingDemo;Trusted_Connection=True;");
        }
        
        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            modelBuilder.Entity<Animal>(entity =>
                {
                    entity.Key(e => e.AnimalId);
                    entity.Property(e => e.AnimalId)
                        .ForSqlServer()
                            .UseIdentity();
                }
            );
            
            modelBuilder.Entity<Zoo>(entity =>
                {
                    entity.Key(e => e.ZooId);
                    entity.Property(e => e.ZooId)
                        .ForSqlServer()
                            .UseIdentity();
                }
            );
            
            modelBuilder.Entity<ZooAnimalSighting>(entity =>
                {
                    entity.Key(e => e.ZooAnimalSightingId);
                    entity.Property(e => e.ZooAnimalSightingId)
                        .ForSqlServer()
                            .UseIdentity();
                    entity.Property(e => e.DateOf)
                        .ForRelational()
                            .ColumnType("datetime2(7)");
                }
            );
            
            modelBuilder.Entity<Animal>(entity =>
                {
                    entity.HasOne<Zoo>(d => d.Zoo).WithMany(p => p.Animal);
                }
            );
            
            modelBuilder.Entity<ZooAnimalSighting>(entity =>
                {
                    entity.HasOne<Animal>(d => d.Animal).WithMany(p => p.ZooAnimalSighting);
                }
            );
        }
    }

I’m going to create Migrations and DAL top-level directories and see if I can Add and Apply a migration to a new database with just these files. The only change I’ll make is to name the database ZooSightingTest in the connection string.

Here is a database creation script if you’d like to create same database using SQL Server Management Studio or some other tool. It uses directory c:\data on your machine. Remember I used SQL Server 2014 and have not tested this on any other version.

USE [master]
GO
/****** Object:  Database [ZooSightingDemo]    Script Date: 2/20/2015 5:48:33 PM ******/
CREATE DATABASE [ZooSightingDemo]
 CONTAINMENT = NONE
 ON  PRIMARY 
( NAME = N'ZooSightingDemo', FILENAME = N'C:\data\ZooSightingDemo.mdf' , SIZE = 3264KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
 LOG ON 
( NAME = N'ZooSightingDemo_log', FILENAME = N'C:\data\ZooSightingDemo_log.ldf' , SIZE = 816KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
GO
ALTER DATABASE [ZooSightingDemo] SET COMPATIBILITY_LEVEL = 120
GO
IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))
begin
EXEC [ZooSightingDemo].[dbo].[sp_fulltext_database] @action = 'enable'
end

GO
USE [ZooSightingDemo]
GO
/****** Object:  Table [dbo].[Animal]    Script Date: 2/20/2015 5:48:33 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Animal](
	[AnimalId] [int] IDENTITY(1,1) NOT NULL,
	[Name] [nvarchar](max) NULL,
	[Weight] [real] NOT NULL,
	[ZooZooId] [int] NULL,
	[Group] [int] NOT NULL,
 CONSTRAINT [PK_Animal] PRIMARY KEY NONCLUSTERED 
(
	[AnimalId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO
/****** Object:  Table [dbo].[Zoo]    Script Date: 2/20/2015 5:48:33 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Zoo](
	[City] [nvarchar](max) NULL,
	[Name] [nvarchar](max) NULL,
	[ZooId] [int] IDENTITY(1,1) NOT NULL,
 CONSTRAINT [PK_Zoo] PRIMARY KEY NONCLUSTERED 
(
	[ZooId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO
/****** Object:  Table [dbo].[ZooAnimalSighting]    Script Date: 2/20/2015 5:48:33 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[ZooAnimalSighting](
	[AnimalAnimalId] [int] NULL,
	[DateOf] [datetime2](7) NOT NULL,
	[IsSightingReliable] [bit] NOT NULL,
	[Sighter] [nvarchar](max) NULL,
	[ZooAnimalSightingId] [int] IDENTITY(1,1) NOT NULL,
 CONSTRAINT [PK_ZooAnimalSighting] PRIMARY KEY NONCLUSTERED 
(
	[ZooAnimalSightingId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO
ALTER TABLE [dbo].[Animal]  WITH CHECK ADD  CONSTRAINT [FK_Animal_Zoo_ZooZooId] FOREIGN KEY([ZooZooId])
REFERENCES [dbo].[Zoo] ([ZooId])
GO
ALTER TABLE [dbo].[Animal] CHECK CONSTRAINT [FK_Animal_Zoo_ZooZooId]
GO
ALTER TABLE [dbo].[ZooAnimalSighting]  WITH CHECK ADD  CONSTRAINT [FK_ZooAnimalSighting_Zoo_ZooZooId] FOREIGN KEY([AnimalAnimalId])
REFERENCES [dbo].[Animal] ([AnimalId])
GO
ALTER TABLE [dbo].[ZooAnimalSighting] CHECK CONSTRAINT [FK_ZooAnimalSighting_Zoo_ZooZooId]
GO
USE [master]
GO
ALTER DATABASE [ZooSightingDemo] SET  READ_WRITE 
GO

Great job by the EF7 team.