将数据库视图映射到 EF 5.0 Code First w/Migrations [英] Mapping Database Views to EF 5.0 Code First w/Migrations

问题描述

我正在尝试将 SQL 视图映射到 EF 5.0 Code First w/Migrations 中的实体，以便在页面上显示一些基本信息，而无需查询多个表以获取该信息(目前加载大约需要 20 秒.不好.).我听说可以这样做，但我一直无法弄清楚或在网上找到正确执行此操作的方法.

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.

要更深入地了解我对这个问题的解决方案，请阅读 这篇博文 关于这个主题.

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

这是我的观点:

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

这是实体:

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

最后是我在 DbContext 中的映射:

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:

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

我做错了什么?我有什么办法可以做到这一点，还是应该做其他事情?

What am I doing wrong? Is there any way to me to accomplish this, or should I be doing something else instead?

推荐答案

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

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.

我认为更好的方法是通过像这样运行 sql 在迁移中创建视图:

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

参考

http://elegantcode.com/2012/04/12/entity-framework-迁移提示/

这篇关于将数据库视图映射到 EF 5.0 Code First w/Migrations的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！