在Entity Framework Core中使用SQL视图 [英] Working with SQL views in Entity Framework Core

问题描述

例如，我有这样的模型：

  public class Blog 
 {
 public int BlogId {得到;组; } 
公用字串Url {get;组; } 
 
 public BlogImage BlogImage {get;组; } 
} 
 
公共类BlogImage 
 {
 public int BlogImageId {get;组; } 
 public byte []图片{get;组; } 
公用字串Caption {get;组; } 
 
 public int BlogId {组; } 
 public Blog Blog {get;组; } 
} 
  

我要在 ImageView 视图中返回网址和图片。

我需要在哪里创建和定义该SQL视图？

解决方案

在 Entity Framework Core 2.1 中，我们可以使用查询类型，如Yuriy N所建议。

可以在此处

$ b $中找到有关如何使用它们的更详细的文章。 b

根据本文示例，最直接的方法是：

1。例如，我们有以下用于管理出版物的实体模型

 公共类杂志
 {
 public int MagazineId {get;组; } 
公共字符串Name {get;组; } 
公共字符串Publisher {get;组; } 
公共列表< Article>文章{get;组; } 
} 
 
公共类文章
 {
 public int ArticleId {get;组; } 
公用字串Title {get;组; } 
 public int MagazineId {组; } 
 public DateTime PublishDate {get;组; } 
公共作者作者{组; } 
 public int AuthorId {get;组; } 
} 
公共类作者
 {
 public int AuthorId {get;组; } 
公共字符串Name {get;组; } 
公共列表< Article>文章{get;组; } 
} 
  

2。我们有一个名为AuthorArticleCounts的视图，定义该视图可返回名称和作者撰写的文章数量

  SELECT 
 a.AuthorName，
 Count（r.ArticleId） as ArticleCount 
来自作者a 
在r.AuthorId = a.AuthorId 
 GROUP BY a.AuthorName 
中加入文章r.AuthorId = a.AuthorId > 
 
 
 3。我们去创建一个用于视图的模型
 公共类AuthorArticleCount 
 {
公共字符串AuthorName {get;私人套装； } 
 public int ArticleCount {get;私人套装； } 
} 
  
 4。之后，我们在DbContext中创建一个DbQuery属性以使用查看模型中的结果
  public DbQuery< AuthorArticleCount> AuthorArticleCounts {get; set;} 
  
 4.1。您可能需要重写OnModelCreating（）并设置视图，尤其是在您的视图名称与类不同的情况下。
 受保护的重写void OnModelCreating（ModelBuilder modelBuilder）
 {
 modelBuilder.Query< AuthorArticleCount>（）。ToView（ AuthorArticleCount）; 
} 
  
 5。最后，我们可以很容易地获得View的结果。 / p> 
 
 
  var结果= _context.AuthorArticleCounts.ToList（）; 
  
 更新 
根据ssougnez的评论
 
 
值得注意的是EF 
 Core 3.0将不再/不再支持DbQuery。 在此处  
 
 
 
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? 
解决方案
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<Article> 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<Article> 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<AuthorArticleCount> 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<AuthorArticleCount>().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


                        
这篇关于在Entity Framework Core中使用SQL视图的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！