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.