ASP.NET Core Application on an existing database (Database First vs Code First)

What is it?

Everybody has some existing project that would be suitable for an ASP.NET Core application.
For my work I had a project that uses Linq to SQL with some weird database access layer (don’t get me started about that).
After two minutes I had around 60 models and a fully stuffed DataContext. Wow right?

This post is a small walk-through what steps I took to get the models and DataContext.
All information and steps came from https://docs.efproject.net/en/latest/platforms/aspnetcore/existing-db.html

C# code

All the code can be found on my github page: https://github.com/timdows/webpages/tree/master/EFCore ExistingDatabase

The existing database

For this small walk-through I took a database from an existing ASP.NET Core application. This way I can compare the results from the generated class with the original one.

Running some commands

In the Package Manager Console I ran:

Install-Package Microsoft.EntityFrameworkCore.SqlServer
Install-Package Microsoft.EntityFrameworkCore.Tools –Pre
Install-Package Microsoft.EntityFrameworkCore.SqlServer.Design


And added the following line to the tools part of my project.json
"Microsoft.EntityFrameworkCore.Tools": "1.0.0-preview2-final"


After the restore is complete I ran the Scaffold-DbContext command
Scaffold-DbContext "Server=localhost;Database=Startpage;Trusted_Connection=True;" Microsoft.EntityFrameworkCore.SqlServer -OutputDir Models -Verbose


After a minute the models folder was populated with all the models

Comparing the generated and original models

Some thoughts:

1. The first thing that you can notice, is that the base class is of course not created.
   But this can easily done by hand (read: ReSharper) afterwards;
2. The enum property could never be generated from the database information, so you will need to know what the different int values represent in order to manually create it;
3. Any attribute on different properties is lost, but as you are only interested in the database structure there is really no point in getting them from the old project anyway;
4. HashSet vs List and ICollection vs List
5. partial on the class, virtual on some properties

Original Database.cs

[code lang=”csharp”]
public class Database : Base
{
public enum InlogType
{
WindowsAuthentication = 1,
PasswordAuthentication
}

public Database()
{
EnvironmentDatabases = new List<EnvironmentDatabase>();
}

public string Server { get; set; }
public string Description { get; set; }
public string DatabaseName { get; set; }
public InlogType AuthenticationType { get; set; }
public string User { get; set; }
[ProtectedData]
public string Password { get; set; }
public Project Project { get; set; }

[JsonIgnore]
public List<EnvironmentDatabase> EnvironmentDatabases { get; set; }
}
[/code]

Scaffolded Database.cs

[code lang=”csharp”]
public partial class Databases
{
public Databases()
{
EnvironmentDatabase = new HashSet<EnvironmentDatabase>();
}

public long Id { get; set; }
public long? ProjectId { get; set; }
public string Server { get; set; }
public byte[] Timestamp { get; set; }
public int AuthenticationType { get; set; }
public string DatabaseName { get; set; }
public string Description { get; set; }
public string Password { get; set; }
public string User { get; set; }
public DateTime? DeletedDateTime { get; set; }
public string Notes { get; set; }

public virtual ICollection<EnvironmentDatabase> EnvironmentDatabase { get; set; }
public virtual Projects Project { get; set; }
}
[/code]