Working with SQL views in Entity Framework Core

Question

For example  I have such model   public class Blog       public int BlogId   get  set        public string Url   get  set         public BlogImage BlogImage   get  set       public class BlogImage       public int BlogImageId   get  set        public byte   Image   get  set        public string Caption   get  set         public int BlogId   get  set        public Blog Blog   get  set         I want to return in ImageView view Url and Image    Where do I need to create and define that SQL view

User · Answer

Views are not currently supported by Entity Framework Core. See https://github.com/aspnet/EntityFramework/issues/827.

That said, you can trick EF into using a view by mapping your entity to the view as if it were a table. This approach comes with limitations. e.g. you can't use migrations, you need to manually specific a key for EF to use, and some queries may not work correctly. To get around this last part, you can write SQL queries by hand

context.Images.FromSql("SELECT * FROM dbo.ImageView")

User · Answer

It is possible to scaffold a view  Just use -Tables the way you would to scaffold a table  only use the name of your view  E g   If the name of your view is    vw inventory     then run this command in the Package Manager Console  substituting your own information for  quot My    quot    PM gt  Scaffold-DbContext  quot Server MyServer Database MyDatabase user id MyUserId password MyPassword quot  Microsoft EntityFrameworkCore SqlServer -OutputDir Temp -Tables vw inventory  This command will create a model file and context file in the Temp directory of your project  You can move the model file into your models directory  remember to change the namespace name   You can copy what you need from the context file and paste it into the appropriate existing context file in your project  Note  If you want to use your view in an integration test using a local db  you ll need to create the view as part of your db setup  If you   re going to use the view in more than one test  make sure to add a check for existence of the view  In this case  since the SQL    Create View    statement is required to be the only statement in the batch  you   ll need to run the create view as dynamic Sql within the existence check statement  Alternatively you could run separate    if exists drop view        then    create view    statements  but if multiple tests are running concurrently  you don   t want the view to be dropped if another test is using it  Example    void setupDb                 SomeDb Command db   gt  db Database ExecuteSqlRaw CreateInventoryView                    public string CreateInventoryView     gt    quot    IF OBJECT ID   dbo   vw inventory    IS NULL     BEGIN EXEC  CREATE VIEW  dbo   vw inventory  AS        SELECT           END quot

User · Answer

Here is a new way to work with SQL views in EF Core  Query Types

User · Answer

The EF Core doesn t create DBset for the SQL views automatically in the context calss  we can add them manually as below   public partial class LocalDBContext   DbContext         public LocalDBContext DbContextOptions lt LocalDBContext gt  options    base options                    public virtual DbSet lt YourView gt  YourView   get  set         protected override void OnModelCreating ModelBuilder modelBuilder                modelBuilder Entity lt YourView gt  entity   gt                entity HasKey e   gt  e ID               entity ToTable  YourView                entity Property e   gt  e Name  HasMaxLength 50                          The sample view is defined as below with few properties  using System  using System Collections Generic   namespace Project Entities       public partial class YourView               public string Name   get  set            public int ID   get  set              After adding a class for the view and DB set in the context class  you are good to use the view object through your context object in the controller

User · Answer

QueryTypes is the canonical answer as of EF Core 2 1  but there is another way I have used when migrating from a database first approach  the view is already created in the database     define the model to match view columns  either match model class name to view name or use Table attribute  Ensure  Key  attribute is applied to at least one column  otherwise data fetch will fail add DbSet in your context   add migration  Add-Migration  remove or comment out code for creation drop of the  table  to be created dropped based on provided model update database  Update-Database

User · Answer

EF Core supports the views  here is the details      This feature was added in EF Core 2 1 under the name of query types  In EF Core 3 0 the concept was renamed to keyless entity types  The  Keyless  Data Annotation became available in EFCore 5 0    It is working not that much different than normal entities  but has some special points  According documentation         Cannot have a key defined    Are never tracked for changes in the DbContext and therefore are never inserted  updated or deleted on the database    Are never discovered by convention    Only support a subset of navigation mapping capabilities  specifically    They may never act as the principal end of a relationship    They may not have navigations to owned entities   They can only contain reference navigation properties pointing to regular entities    Entities cannot contain navigation properties to keyless entity types    Need to be configured with a  Keyless  data annotation or a  HasNoKey   method call    May be mapped to a defining query  A defining query is a query declared in the model that acts as a data source for a keyless entity type      It is working like below   public class Blog      public int BlogId   get  set       public string Name   get  set       public string Url   get  set       public ICollection lt Post gt  Posts   get  set       public class Post      public int PostId   get  set       public string Title   get  set       public string Content   get  set       public int BlogId   get  set        If you don t have an existing View at database you should create like below   db Database ExecuteSqlRaw    CREATE VIEW View BlogPostCounts AS      SELECT b Name  Count p PostId  as PostCount      FROM Blogs b     JOIN Posts p on p BlogId   b BlogId     GROUP BY b Name      And than you should have a class to hold the result from the database view     public class BlogPostsCount          public string BlogName   get  set         public int PostCount   get  set          And than configure the keyless entity type in OnModelCreating using the HasNoKey       protected override void OnModelCreating ModelBuilder modelBuilder              modelBuilder            Entity lt BlogPostsCount gt  eb   gt                           eb HasNoKey                 eb ToView  View BlogPostCounts                 eb Property v   gt  v BlogName  HasColumnName  Name                          And just configure the DbContext to include the DbSet   public DbSet lt BlogPostsCount gt  BlogPostCounts   get  set

User · Answer

In Entity Framework Core 2 1 we can use Query Types as Yuriy N suggested   A more detailed article on how to use them can be found here   The most straight forward approach according to the article s examples would be   1 We have for example the following entity Models to manage publications  public class Magazine     public int MagazineId   get  set      public string Name   get  set      public string Publisher   get  set      public List lt Article gt  Articles   get  set       public class Article     public int ArticleId   get  set      public string Title   get  set      public int MagazineId   get  set      public DateTime PublishDate   get   set      public Author Author   get  set      public int AuthorId   get  set      public class Author     public int AuthorId   get  set      public string Name   get  set      public List lt Article gt  Articles   get  set        2 We have a view called AuthorArticleCounts  defined to return the name and number of articles an author has written  SELECT   a AuthorName    Count r ArticleId  as ArticleCount from Authors a   JOIN Articles r on r AuthorId   a AuthorId GROUP BY a AuthorName   3 We go and create a model to be used for the View  public class AuthorArticleCount     public string AuthorName   get  private set      public int ArticleCount   get  private set        4 We create after that a DbQuery property in my DbContext to consume the view results inside the Model  public DbQuery lt AuthorArticleCount gt  AuthorArticleCounts get set     4 1  You might need to override OnModelCreating   and set up the View especially if you have different view name than your Class   protected override void OnModelCreating ModelBuilder modelBuilder        modelBuilder Query lt AuthorArticleCount gt    ToView  AuthorArticleCount        5 Finally we can easily get the results of the View like this   var results  context AuthorArticleCounts ToList      UPDATE According to ssougnez s comment     It s worth noting that DbQuery won t be is not supported anymore in EF   Core 3 0  See here

[c#] Working with SQL views in Entity Framework Core

Examples related to c#

Examples related to entity-framework

Examples related to entity-framework-core

Examples related to sql-view