在实体框架中使用视图 [英] Use Views in Entity Framework

问题描述

我在一个项目上使用Entity Framework，但是发现大型查询（尤其是那些使用LEFT联接的查询）编写起来非常繁琐且难以调试。

I am using Entity Framework on a project, but am finding the large queries, especially those which use LEFT joins, to be very tedious to write, and hard to debug.

使用数据库中的视图，然后在EntityFramework中使用这些视图是常见的或公认的做法吗？还是这是一种不好的做法？

Is it common, or accepted practice, to make use of Views in the database, and then use those views within the EntityFramework? Or is this a bad practice?

推荐答案

问题不是很清楚，但是在软件中没有绝对是非。这完全取决于您的情况。

the question is not very clear but there is no absolute right or wrong in Software. it all depends on your case.

在ef核心中对视图有本机支持，但在EF<中没有对视图的本机支持。 6.至少不是当前最新版本6.3。但是，有一个解决方法。首先在数据库中，您将通过sql正常创建视图，并且当您对数据库进行反向工程时，EF将把您的视图视为普通模型，并允许您像在普通表方案中一样定期使用它。在Code First中，这有点乏味。您将创建一个POCO对象，该对象映射到视图中的列。请注意，您需要在此POCO类中包含一个ID。例如

there is native support for views in ef core but there is no native support for views in EF < 6. at least not in the current latest version 6.3. there is, however, a work around to this. in database first you would create your view via sql normally and when you reverse engineer your database, EF will treat your view as a normal model and will allow you to consume it regularly as you would do in a normal table scenario. in Code First it's a bit more tedious. you would create a POCO object that maps to the columns in your view. notice that you need to include an Id in this POCO class. for example

public class ViewPOCO
{
    [Key]
    [DatabaseGenerated(DatabaseGeneratedOption.None)]
    public Guid Id {get;set;}
    public string ViewColumn1 {get;set;}
    ... etc.
}

您将在POb类中添加此POCO类

you would add this POCO class in your DbContext

public class MyDbContext : DbContext
{
  public virtual DbSet<ViewPOCO> MyView {get;set;}
}

现在通常将应用添加命令通过包管理器控制台进行迁移

now you will normally apply the command of adding migration through the package manager console

 Add-Migration <MigrationName> <ConnectionString and provider Name>

现在在上下迁移中，您会注意到EF将您的模型视为表。您将清除所有这些并编写自己的sql，以使用Sql函数在up方法中添加/更改视图，并在down方法中删除视图。

now in the migration up and down you will notice that EF treats your Model as table. you would clear all of this and write your own sql to add/alter the view in the up and drop the view in the down method using the Sql function.

 public override void Up()
 {
    Sql("CREATE OR ALTER VIEW <ViewName> AS SELECT NEWID() AS Id, ...");
 }
 public override void Down()
 {
   Sql("DROP VIEW <ViewName>");
 }

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