Entity Framework Core 1.0 : Reverse Engineering the Model (Database First Development)

This article uses Entity Framework Core 1.0.0

Database First Microsoft docs here

and here are the steps to build out your model from an existing database (aka. reverse engineer).

Create a new ASP.NET Web Application

c1.PNG

 

Open up your Package Manager Console

  1. Open Tools > NuGet Package Manager > Package Manager Console

    After we type in a command, the packages will automatically install in the background.  While the install is in progress you will see “(Restoring…)” next to the projects References folder.

  2. Type these 3 commands in the Package Manager Console: Install-Package Microsoft.EntityFrameworkCore.SqlServer –Pre
    Install-Package Microsoft.EntityFrameworkCore.Tools –Pre
    Install-Package Microsoft.EntityFrameworkCore.SqlServer.Design –Pre

Your Package Manager Console should look something like this:

pmc.PNG

Open up your project.json file

  1. Add “Microsoft.EntityFramworkCore.Tools” to the “tools” section. It should end up looking something like this:
"tools": {
  "Microsoft.EntityFrameworkCore.Tools": {
    "version": "1.0.0-preview1-final",
    "imports": [
      "portable-net45+win8+dnxcore50",
      "portable-net45+win8"
    ]
  },
  "Microsoft.AspNetCore.Server.IISIntegration.Tools": {
    "version": "1.0.0-preview1-final",
    "imports": "portable-net45+win8+dnxcore50"
  }
},

Run Auto Scaffold Command in Package Manager Console  (First Option)

  1. First, create a folder in your project named “Models”
  2. Second,  run this command in the Package Manager Console with your server and db info (note: single quotes around connection string, very unusual)
Scaffold-DbContext "'Server=SERVERNAME;Database=DATABASENAME;Trusted_Connection=True;'" Microsoft.EntityFrameworkCore.SqlServer -OutputDir Models

if connecting to a secure db, use something like this (note: no single quotes needed here! effin crazy!)

Scaffold-DbContext "Server=SERVERNAME;Database=DATABASENAME;user id=USERNAME;password=USERPASSWORD;" Microsoft.EntityFrameworkCore.SqlServer -OutputDir Models

After successfully running the above command, your project should now look something like this:

models.PNG

Run Auto Scaffold Command in Command Prompt  (Second Option)

Another way to run these commands is through the Command Prompt.  To run the scaffold command, open up your command prompt and navigate to the projects root folder.  Then run a command that looks like this:

dotnet ef dbcontext scaffold "Server=SERVERNAME;Database=DATABASENAME;user id=USERNAME;password=USERPASSWORD;" Microsoft.EntityFrameworkCore.<wbr />SqlServer -o Models  --context MyDBDataContext --verbose

Thats it! This is suppose to become easier with the new version of Visual Studio.  Also, if your wondering how to get your SQL Server Views into your data context, check out this post.

 

Advertisements

Entity Framework Core: Modeling and Connecting to SQL Views

This post is based on EF Core 1.0 and it will be strictly about adding SQL Views to your Model and Data Context.  If you are interested on how to connect your EF Core 1.0 project to a SQL Server DB then check out this other post about how to reverse engineer and scaffold your SQL Server DB.

The state of EF Core 1.0 and VS2015
Unfortunately, that nice UI you were used too that allowed you to connect and select the tables and views to be mapped out, does not yet exist.  After setting up your project and project.json file you will run a script in the Command Prompt or in the Nuget Package Manager to connect and build out you EF model.  Will look a lil something like this:

Command Prompt Scaffle Command

dotnet ef dbcontext scaffold "Server=SERVERNAME;Database=DATABASENAME;user id=USERNAME;password=USERPASSWORD;" Microsoft.EntityFrameworkCore.<wbr />SqlServer -o Models  --context MyDBDataContext --verbose

Nuget Package Manager Scaffle Command

Scaffold-DbContext "'Server=SERVERNAME;Database=DATABASENAME;Trusted_Connection=True;'" Microsoft.EntityFrameworkCore.SqlServer -OutputDir Models

The above commands hook you up to your DB and creates a Model folder that contains all your newly generated classes and DataContext.cs.  But what about my views!?!?  Well, since your DataContext.cs is connected to your DB, you do technically have access to everything in that DB, including SPROCs and Views.  These Views just dont get created for you and there is no real way to access them except through inline SQL or manually adding them.  So lets manually add them together!

Create a new Class for your View

  1. Your project should have a Models folder (if you did everything here). Right click that Models folder and select Add > New Item.. > Class
  2. Name the Class “Team_View” and add this code to it.
    public class Team_View
    {
    public int Team_ID { get; set; }
    public int? Players_Count { get; set; }
    public double? Total_Wins { get; set; }
    public double? Total_Losses { get; set; }
    }
    
    

Add your new View’s Class to DataContext.cs

  1. In the Models folder, open up xxxxDataContext.cs. This file was automatically generated for you.
  2. In this class add this class object:
    public virtual DbSet<Team_View> Team_View { get; set; }
    
  3. In  OnModelCreating(ModelBuilder modelBuilder) add:
    modelBuilder.Entity<Team_View>(entity => { entity.HasKey(e => e.Team_ID); });
    
  4. Your DataContext should now look something like this
    public virtual DbSet<Team_View> Team_View { get; set; }
    
    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
    modelBuilder.Entity<Team_View>(entity => { entity.HasKey(e => e.Team_ID); });
    
    .......
    }
    

Also, note that the DataContext is a “Shared” class.  So if you don’t want to re-write this code every time you run the update scaffold command, putting code like this Team_View code in a separate shared DataContext.cs file may be the way to go.

Thats It!
You now have access you your View! AND you now have a Class for that view! You can now call it from any lambda/LINQ  query that your want.

Entity Framework Core 1.0 -Lazy Loading and Eager Loading

EF Core 1.0 does not support automatic lazy loading and eager loading related objects in the same way that EF6 did. Lazy loading does make data access easier and it cuts the code you have to write to reach that data significantly,  so its good to see that it is still listed as a priority item here on EF’s  Roadmap.

Until then, the way to eager load related objects, is by the .Include() and .ThenInclude() methods.  Here are two examples.


var leagues = db.Leagues
            .Include(l => l.Teams)
            .ThenInclude(t => t.Players)
            .ToList();
  
var leagues = from d in db.Leagues.Include(l => l.Teams) select d;