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.

Advertisements

10 thoughts on “Entity Framework Core: Modeling and Connecting to SQL Views

  1. DarkVader January 26, 2017 / 12:21 pm

    This only maps existing views to EF “tables”. How do you automatically migrate changes in views to all instances of the application? And even better, how do you force EF to create the views via migrations when new instance is installed?

    Like

  2. Alex Read April 30, 2017 / 10:09 am

    Fantastic article – concise and insightful! Also now a year old and Microsoft STILL hasn’t added the support for EF scaffolding views I notice :c/

    Like

  3. Net core beginner August 3, 2017 / 5:16 am

    I don`t know how to putting code in a separate shared DataContext.cs file,could you help me.

    Like

  4. James Shinevar January 16, 2018 / 9:04 pm

    This is fantastic. Thank you. I am trying to make this work right now

    Like

  5. Matt W April 9, 2018 / 5:41 pm

    A handy technique I’ve found is that, since EF scaffolds the code as partial classes, to extend the DbContext in another file so that I can regenerate the code without overwriting my changes.

    Like

  6. Klemens April 18, 2018 / 9:14 am

    Hi Jesse,

    although your blog post is already two years old, it helped a lot in our current project. Thanks for that!
    We figured out several additional things to include your solution in a major web api based on ASP.NET Core 2.0, which I want to share with you and the other readers:

    First, we are using swagger-codegen for generating the model classes. As said by you, there are no models created for the views, only for tables. So the classes representing the views had to be created by ourselves (as you mentioned above, too).

    Second, we created a file called DbContext_View.cs, which implements the same partial class as the generated Context class. It declares all the view classes we created in step 1. It also has a method called OnModelCreating_Views(ModelBuilder modelBuilder), as the initial overwritten methode OnModelCreating is already implemented. We manually build our entity in there.

    Third, we call the method OnModelCreating_Views from the method OnModelCreating.

    The only thing we have to do again after a database change and code generation by swagger is step 3, and therefore that’s a really nice solution we can live with 🙂

    Regards,
    Klemens

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s