我正在尝试将 SQL 视图映射到 EF 5.0 Code First w/Migrations 中的实体,以便在页面上显示一些基本信息,而无需查询多个表以获取该信息(目前加载需要 ~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(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
实体应映射到名为"ClientStatistics"的表。因此,实体框架将生成一个迁移,其中包含创建该表的指令。但是您已经在数据库中独立创建了该视图,因此为了防止出现错误,您应该从Up
迁移中删除CreateTable
指令。
我认为更好的方法是通过像这样运行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)")
}
这样,您的视图和表就可以在一个位置指定,您可以安全地上下迁移
参考
http://elegantcode.com/2012/04/12/entity-framework-migrations-tips/
我实际上正在使用实体框架"代码优先"和视图,我这样做的方式是这样的:
1) 创建类
[Table("view_name_on_database")]
public class ViewClassName {
// View columns mapping
public int Id {get; set;}
public string Name {get; set;}
// And a few more...
}
2) 将类添加到上下文中
public class ContextName : DbContext {
// Tables
public DbSet<SomeTableClassHere> ATable { get; set; }
// And other tables...
// Views
public DbSet<ViewClassName> ViewContextName { get; set; }
// This lines help me during "update-database" command
protected override void OnModelCreating(DbModelBuilder modelBuilder) {
// Remove comments before "update-database" command and
// comment this line again after "update-database", otherwise
// you will not be able to query the view from the context.
// Ignore the creation of a table named "view_name_on_database"
modelBuilder.Ignore<ViewClassName>();
}
}
有点晚了,但我希望这对某人有所帮助。
如果有一种方法可以在"更新数据库"期间忽略创建名为视图的表,并且在此之后不忽略,那就太好了。
将 ToTable 属性添加到实体中,并将其作为 DbSet 包含在上下文中。
[ToTable("ClientStatics")]
public class ClientStaticsView{}
public class DataContext : DbContext
{
public DbSet<ClientStaticsView> ClientStatics { get; set; }
}
或者,如果您不想将 DbSet 添加到上下文中,请为 ClientStatisView 创建一个 EntityTypeConfiguration,并在流畅的 api 而不是属性中包含 ToTable 属性。然后,您可以将实体添加到上下文的 OnModelCreation 中的上下文中:
public class ClientStaticsViewConfiguration : EntityTypeConfiguration<ClientStaticsView>
{
public ClientStatusViewConfiguration
{
ToTable("ClientStatics");
}
}
protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
modelBuilder.Configurations.Add(new ClientStatisViewConfiguration());
}