将数据库视图映射到EF 5.0代码首先进行迁移 [英] Mapping Database Views to EF 5.0 Code First w/Migrations

问题描述

我试图在EF 5.0 Code First w / Migrations中将一个SQL View映射到一个实体，用于在页面上显示一些基本信息，而不需要查询该信息的多个表（目前需要大约20秒加载）。 不好。）。我听说可以这样做，但是我无法弄清楚或找到在线方式正确地这样做。

编辑：对于更深入地看一下我对这个问题的解决方案，请阅读

  CREATE VIEW [dbo]。[ClientStatistics] 
 AS 
 SELECT ROW_NUMBER（）OVER（Order by c.ID）as Row， c.LegacyID，c.ID，c.ClientName，slc.AccountManager，slc.Network，
（SELECT MAX（Cre​​atedDate）AS Expr1 
 FROM dbo.DataPeriods 
 WHERE（ClientID = ID））AS LastDataReceived，
（SELECT MAX（ApprovedDate）AS Expr1 
 FROM dbo.DataPeriods AS DataPeriods_2 
 WHERE（ClientID = c.ID））AS LastApproved，
（SELECT MAX（ReportProcessedDate）AS Expr1 
 FROM dbo.DataPeriods AS DataPeriods_1 
 WHERE（ClientID = c.ID））AS LastReportProcesssed 
 FROM dbo.Clients AS c INNER JOIN 
 dbo.SLClients AS slc ON c.ID = slc.ClientID 
  

这是实体：

  public class ClientStatisticsView 
 {
 [Key ] 
 public int Row {get;组; } 
 public int LegacyID {get;组; } 
 public int ClientID {get;组; } 
 public string ClientName {get;组; } 
 public string AccountManager {get;组; } 
 public string Network {get;组; } 
 public DateTime LastDataReceived {get;组; } 
 public DateTime LastApproved {get;组; } 
 public DateTime LastReportProcessed {get;组; } 
} 
  

最后我的映射在 DbContext

  protected override void OnModelCreating（DbModelBuilder modelBuilder）
 {
 modelBuilder.Conventions。删除< OneToManyCascadeDeleteConvention>（）; 
 
 modelBuilder.Entity< ClientStatisticsView>（）。ToTable（ClientStatistics）; 
 
 base.OnModelCreating（modelBuilder）; 
} 
  

所有这一切都给我以下错误：

数据库中已经有一个名为ClientStatistics的对象。

什么我做错了吗有没有办法完成这个，还是应该做别的事情？

解决方案

你指定了 ClientStatisticsView 实体应映射到名为ClientStatistics的表。因此，实体框架将生成包含创建该表的指令的迁移。但是您已经在数据库中独立创建了该视图，以防止出现错误，您应该从 Up CreateTable 指令$ c>迁移

我认为一个更好的方式是通过运行sql在迁移中创建视图：

  public override void Up（）
 {
 Sql（EXEC（'CREATE View [dbo]。[ClientStatistics] AS --etc 
} 
 
 public override void Down（）
 {
 
 Sql（@IF EXISTS（SELECT 
 * 
 FROM sys.views 
 WHERE object_id = OBJECT_ID（N'dbo.ClientStatistics'））
 DROP VIEW dbo.ClientStatistics））
} 
  / pre> 
 
 
这样，您的视图和表格在一个地方指定，您可以安全地上下移动
 
 
 
参考
 
 
 
  http://elegantcode.com/2012/04/12/entity-framework-migrations-tips/  
 
I'm trying to map a SQL View to an entity in EF 5.0 Code First w/Migrations for displaying some basic information on a page without having to query multiple tables for that information (which currently takes ~20 seconds to load. NOT GOOD.). I've heard that it is possible to do, but I haven't been able to figure out or find online a way to properly do so.

EDIT: For a more in-depth look at my solution to this problem, read this blog post on the subject.

Here is my View:
CREATE VIEW [dbo].[ClientStatistics]
AS
SELECT       ROW_NUMBER() OVER (Order By c.ID) as Row, c.LegacyID, c.ID, c.ClientName, slc.AccountManager, slc.Network,
                             (SELECT        MAX(CreatedDate) AS Expr1
                               FROM            dbo.DataPeriods
                               WHERE        (ClientID = c.ID)) AS LastDataReceived,
                             (SELECT        MAX(ApprovedDate) AS Expr1
                               FROM            dbo.DataPeriods AS DataPeriods_2
                               WHERE        (ClientID = c.ID)) AS LastApproved,
                             (SELECT        MAX(ReportProcessedDate) AS Expr1
                               FROM            dbo.DataPeriods AS DataPeriods_1
                               WHERE        (ClientID = c.ID)) AS LastReportProcesssed
FROM            dbo.Clients AS c INNER JOIN
                         dbo.SLClients AS slc ON c.ID = slc.ClientID
Here is the entity:
public class ClientStatisticsView
{
    [Key]
    public int Row { get; set; }
    public int LegacyID { get; set; }
    public int ClientID { get; set; }
    public string ClientName { get; set; }
    public string AccountManager { get; set; }
    public string Network { get; set; }
    public DateTime LastDataReceived { get; set; }
    public DateTime LastApproved { get; set; }
    public DateTime LastReportProcessed { get; set; }
}
And finally my mapping in DbContext:
protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
    modelBuilder.Conventions.Remove<OneToManyCascadeDeleteConvention>();

    modelBuilder.Entity<ClientStatisticsView>().ToTable("ClientStatistics");

    base.OnModelCreating(modelBuilder);
}
All of this gives me the following error:

There is already an object named 'ClientStatistics' in the database.

What am I doing wrong? Is there any way to me to accomplish this, or should I be doing something else instead?
解决方案
You have specified that the ClientStatisticsView entity should be mapped to a table named "ClientStatistics". So entity framework will generate a migration containing an instruction to create that table. But you have independently created that view in the database so to prevent the error you are getting you should remove the CreateTable instruction from the Up migration.

I think a better way to do it is to create the view in the migration by running sql like this:
public override void Up()
{
    Sql("EXEC ('CREATE View [dbo].[ClientStatistics] AS --etc"
}

public override void Down()
{

    Sql(@"IF  EXISTS (SELECT
                        *
                    FROM sys.views
                    WHERE object_id = OBJECT_ID(N'dbo.ClientStatistics'))
                    DROP VIEW dbo.ClientStatistics)")
}
That way your views and tables are specified in one place and you can safely migrate up and down

Reference

http://elegantcode.com/2012/04/12/entity-framework-migrations-tips/

                        
这篇关于将数据库视图映射到EF 5.0代码首先进行迁移的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！