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

问题描述

例如，我有这样的模型:

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; }
} 

我想在 ImageView 视图 Url 和 Image 中返回.

I want to return in ImageView view Url and Image.

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

Where do I need to create and define that SQL view?

推荐答案

在 Entity Framework Core 2.1 中，我们可以使用 查询类型，正如 Yuriy N 建议的那样.

In Entity Framework Core 2.1 we can use Query Types as Yuriy N suggested.

可以在此处

根据文章示例，最直接的方法是:

The most straight forward approach according to the article's examples would be:

1.例如，我们有以下实体模型来管理出版物

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.我们有一个名为 AuthorArticleCounts 的视图，定义为返回作者所写文章的名称和数量

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.我们去创建一个用于视图的模型

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.然后我们在我的 DbContext 中创建一个 DbQuery 属性来消费模型内部的视图结果

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.您可能需要重写 OnModelCreating() 并设置视图，尤其是当您的视图名称与类不同时.

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.最后我们就可以很容易的得到这样的View的结果了.

5.Finally we can easily get the results of the View like this.

var results=_context.AuthorArticleCounts.ToList();

更新根据 ssougnez 的评论

UPDATE According to ssougnez's comment

值得注意的是，EF 将不再/不再支持 DbQuery核心 3.0.请参阅此处

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屋！