将数据库视图映射到EF 5.0代码首先进行迁移 [英] Mapping Database Views to EF 5.0 Code First w/Migrations
问题描述
我试图在EF 5.0 Code First w / Migrations中将一个SQL View映射到一个实体,用于在页面上显示一些基本信息,而不需要查询该信息的多个表(目前需要大约20秒加载)。 不好。)。我听说可以这样做,但是我无法弄清楚或找到在线方式正确地这样做。
编辑:对于更深入地看一下我对这个问题的解决方案,请阅读 这是实体: 最后我的映射在 所有这一切都给我以下错误: 什么我做错了吗有没有办法完成这个,还是应该做别的事情? 你指定了 我认为一个更好的方式是通过运行sql在迁移中创建视图: 这样,您的视图和表格在一个地方指定,您可以安全地上下移动 参考 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: Here is the entity: And finally my mapping in All of this gives me the following error: 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 I think a better way to do it is to create the view in the migration by running sql like this: 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屋!
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 = 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 $ c中删除
CreateTable
指令$ c>迁移
public override void Up()
/ pre>
{
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))
}
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
:protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
modelBuilder.Conventions.Remove<OneToManyCascadeDeleteConvention>();
modelBuilder.Entity<ClientStatisticsView>().ToTable("ClientStatistics");
base.OnModelCreating(modelBuilder);
}
There is already an object named 'ClientStatistics' in the database.
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.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)")
}