EF 4代码第一 - 组合视图和表格 [英] EF 4 Code First - Combine Views and Tables
问题描述
最终，我有同样的问题，但是我希望有一个更好的解决方案。
考虑以下DB表：
CREATE TABLE [Contact]（
[ContactID] [int] IDENTITY（1,1）NOT FOR REPLICATION NOT NULL，
[ContactName] [varchar]（80）NOT NULL，
[email] [varchar]（80）NOT NULL，
[标题] [varchar]（120）NOT NULL，
[Address1] [varchar]（80）NOT NULL，
[地址2] [varchar]（80）NOT NULL，
[City] [varchar]（80）NOT NULL，
[State_Province] [varchar]（50）NOT NULL，
[ZIP_PostalCode] [varchar]（30）NOT NULL，
[Country] [varchar]（50）NOT NULL，
[OfficePhone] [varchar]（30）NOT NULL，
[MobilePhone] [varchar ]（30）NOT NULL）
CREATE TABLE [Blog]（
[BlogID] [int] IDENTITY（1,1）NOT FOR REP LITE NOT NULL，
[BlogName] [varchar]（80）NOT NULL，
[CreatedByID] [int] NOT NULL， - FK to ContactTable
[ModifiedByID] [int] NOT NULL - FK to ContactTable
）
CREATE TABLE [Post]（
[PostID] [int] IDENTITY（1,1）NOT FOR REPLICATION NOT NULL，
[BlogID] [int] NOT NULL， - FK to BlogTable
[Entry] [varchar]（8000）NOT NULL，
[CreatedByID] [int] NOT NULL， - FK to ContactTable
[ModifiedByID] [int] NOT NULL - FK to ContactTable
）
I现在想使用视图加载常用查找/计算信息。每当我们在网站上显示一个帖子时，我们想知道创建该帖子的人的名字，以及最后一次修改的帖子。这两个字段存储在与表格不同的表格中。我可以很容易地使用以下语法（假设应用了Lazy / eager加载，CreatedBy是基于CreatedByID的Contact类型的属性）：currentPost.CreatedBy.Name;
该方法的问题是Db调用的次数以及为联系人检索的大记录，但是在这种情况下我们只使用Name 99％。我意识到上面的DB模式很小，但这只是一个简化的例子，真正的联系表有大约50个字段。
过去管理这种情况（在使用EF之前），我通常会为我将使用的表创建详细视图。 详细视图包含常见的查找/计算字段，因此只需要1次调用DB才能有效地获取所需的所有信息（注意：我们还在SQL视图中使用索引，以使阅读效率非常高效）我将通常使用的视图列表（因为它们将包含相关表中的查找字段）：
ALTER VIEW [icoprod]。[BlogDetail]
AS
SELECT B. [BlogID]，
B. [BlogName]，
B. [BlogDescription]，
B. [ CreatedByID]，
B. [ModifiedByID]，
CREATEDBY。[ContactName] AS CreatedByName，
MODIFIEDBY。[ContactName] AS ModifiedByName，
（SELECT COUNT（*）FROM Post P WHERE P.BlogID = B.BlogID）AS PostCount
从博客AS B
JOIN联系AS CREATEDBY ON B.CreatedByID = CREATEDBY.ContactID
JOIN联系AS MODIFIEDBY ON B.ModifiedByID = MODIFIEDBY。 ContactID
ALTER VIEW [icoprod]。[PostDetail]
AS
SELECT P. [PostID]，
P. [BlogID]，
P. [Entry]，
P. [CreatedByID]，
P. [ModifiedByID]，
CREATEDBY。[ContactName] AS CreatedByName，
MODIFIEDBY。[ContactName] AS ModifiedByName，
B.Name AS BlogName
FROM Post AS P
JOIN联系AS CREATEDBY ON P.CreatedByID = CREATEDBY.ContactID
JOIN联系AS MODIFIEDBY ON P.ModifiedByID = MODIFIEDBY.ContactID
加入博客AS B ON B.BlogID = P.BlogID
以下是我的概述POCO对象：
public class Blog
{
public int ID {get;组; }
public string Name {get;组; }
public int CreatedByID {get;组; }
public DateTime ModifiedByID {get;组;
}
public class Post
{
public int ID {get;组; }
public string Name {get;组; }
public int CreatedByID {get;组; }
public DateTime ModifiedByID {get;组;
}
public class Contact
{
public int ID {get;组; }
public string Name {get;组; }
public string Email {get;组; }
public string标题{get;组; }
public string Address {get;组; }
public string City {get;组; }
public string MobilePhone {get;组;
}
public class BlogDetails：Blog
{
public string CreatedByName {get;组; }
public string ModifiedByName {get;组; }
public int PostsCount {get;组;
}
public class PostDetails：Post
{
public string CreatedByName {get;组; }
public string ModifiedByName {get;组; }
public string BlogName {get;组; }
}
我喜欢这种方法的原因是它允许我从基于表或视图的数据库AND如果我加载视图，该视图包含所有表信息，这将允许我从视图加载但保存到表中。 IMO，这给了我最好的两个世界。
我以前一直使用这种方法，但通常，我刚刚从数据库中加载信息，使用数据行或信息存储过程或甚至使用亚音速激活记录模式和从DB加载后的映射字段。我真的希望我可以在EF中做一些事情，让我加载这些对象，而不需要创建另一层抽象。
这是我试图用于配置（使用流畅的API和代码优先EF）：
public class PostConfiguration：EntityTypeConfiguration< Post>
{
public PostConfiguration（）
：base（）
{
HasKey（obj => obj.ID）;
属性（obj => obj.ID）。
HasColumnName（PostID）。
HasDatabaseGeneratedOption（DatabaseGeneratedOption.Identity）。
IsRequired（）;
地图（m =>
{
m.ToTable（Post）;
}）;
}
}
public class BlogConfiguration：EntityTypeConfiguration< Blog>
{
public BlogConfiguration（）
：base（）
{
HasKey（obj => obj.ID）;
属性（obj => obj.ID）。
HasColumnName（BlogID）。
HasDatabaseGeneratedOption（DatabaseGeneratedOption.Identity）。
IsRequired（）;
地图（m =>
{
m.ToTable（Blog）;
}）;
}
}
public class ContactConfiguration：EntityTypeConfiguration< Contact>
{
public ContactConfiguration（）
：base（）
{
HasKey（obj => obj.ID）;
属性（obj => obj.ID）。
HasColumnName（ContactID）。
HasDatabaseGeneratedOption（DatabaseGeneratedOption.Identity）。
IsRequired（）;
地图（m =>
{
m.ToTable（Contact）;
}）;
}
}
public class PostDetailsConfiguration：EntityTypeConfiguration< PostDetails>
{
public PostDetailsConfiguration（）
：base（）
{
Map（m =>
{
m.MapInheritedProperties（）;
m.ToTable（icoprod.PostDetails）;
}）;
}
}
public class BlogDetailsConfiguration：EntityTypeConfiguration< BlogDetails>
{
public BlogDetailsConfiguration（）
：base（）
{
地图（m =>
{
m.MapInheritedProperties（）;
m.ToTable（icoprod.BlogDetails）;
}）;
}
}
此时，我试图使用包含表中所有信息的视图扩展信息，当我尝试这个，我得到可怕的3032错误（这里的错误示例）。然后我试图让视图只包含表的主键和扩展属性（例如[Entry]不在PostDetails视图中）。当我尝试这个，我得到以下错误：
EntitySetDBContext.Post中的所有对象必须具有唯一的主键。但是，类型为'PostDetails'的实例和类型为'Post'的实例都具有相同的主键值'EntitySet = Post; ID = 1'。
所以我已经玩了一些MapInheritedProperties，但没有运气。有没有人建议如何扩展一个基础/表对象，并从视图加载信息？再次，我相信这样做有很大的表现。我在这个问题开始时提到的文章有两个潜在的解决方案，但是1需要太多的DB命中（只是为了获得一些常见的查找信息），另外需要一个额外的抽象层（我真的很想直接去我的POCO来自数据库，没有写任何映射）。
最后，谢谢你给所有回答这些类型的问题的人。我赞扬多年来为响应作出贡献的每个人。我认为我们太多的开发者把这些信息当成是理所当然的！
从视图加载记录并将其保存到表中使用代码映射 - 博客实体将始终从表中加载并保存到表，并且BlogDetail实体将始终从视图加载并保存到视图 - 因此您必须具有可更新视图，而不是触发器来支持此方案。如果您使用EDMX，还可以映射执行插入，更新和删除的自定义SQL /存储过程，以强制保存到表，但此功能在代码映射中不可用。无论如何，这不是你最大的问题。
您可以使用您的视图，您可以按照您的方式将其映射到类，但不能映射继承。原因是继承如何工作。继承说实体是父或子（可以作为父）。永远不可能是数据库记录，可以是父级（我的意思是只有父）或孩子。在.NET中甚至是不可能的，因为要支持这种情况，您需要两个实例 - 父类型和子类型之一。这两个实例不是等效的，因为纯父代不能被转换为子（它不是一个孩子）。这里是最大的问题。一旦映射了继承，则密钥在整个继承层次结构中必须是唯一的。所以你永远不会有两个实例（一个用于父代，另一个用于孩子）具有相同的密钥。
作为解决方法，不得从映射实体（ Blog BlogDetail C $ C>）。使用第三个未映射的类作为父或两者的接口。也不要使用 MapInheritedProperties 来使您的 BlogDetail 与博客。
另一种解决方法是根本不映射BlogDetail。在这种情况下，您可以使用代码，而不是使用视图创建简单的可重用查询与投影：
var blogDetails =从b在context.Blogs
其中...
选择新的BlogDetail
{
名称= b.Name，
CreatedByID = b.CreatedByID，
...
CreatedByName = b.CreatedBy.Name //您需要导航属性
...
};
在这两种情况下，如果您需要保存 Blog 您必须创建新的实例，并从 BlogDetail 填写。之后，您将其附加到上下文，将其设置为修改状态并保存更改。
I researched this question for days and cannot seem to find an option I feel good about; however, here is a link to a very similar question:
Ultimately, I have the same question, but I am hoping for a better solution.
Consider the following DB Tables:
CREATE TABLE [Contact](
[ContactID] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
[ContactName] [varchar](80) NOT NULL,
[Email] [varchar](80) NOT NULL,
[Title] [varchar](120) NOT NULL,
[Address1] [varchar](80) NOT NULL,
[Address2] [varchar](80) NOT NULL,
[City] [varchar](80) NOT NULL,
[State_Province] [varchar](50) NOT NULL,
[ZIP_PostalCode] [varchar](30) NOT NULL,
[Country] [varchar](50) NOT NULL,
[OfficePhone] [varchar](30) NOT NULL,
[MobilePhone] [varchar](30) NOT NULL)
CREATE TABLE [Blog](
[BlogID] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
[BlogName] [varchar](80) NOT NULL,
[CreatedByID] [int] NOT NULL, -- FK to ContactTable
[ModifiedByID] [int] NOT NULL -- FK to ContactTable
)
CREATE TABLE [Post](
[PostID] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
[BlogID] [int] NOT NULL, -- FK to BlogTable
[Entry] [varchar](8000) NOT NULL,
[CreatedByID] [int] NOT NULL, -- FK to ContactTable
[ModifiedByID] [int] NOT NULL -- FK to ContactTable
)
I now would like to use views for loading "common" lookup/calculated info. Every time we display a post on the site, we want to know the name of the person who created the post and who last modified it. These are two fields that are stored in separate tables from the post table. I could easily use the following syntax (assuming Lazy/eager loading was applied and CreatedBy was a property, of type Contact, based on CreatedByID): currentPost.CreatedBy.Name;
The problem with that approach is the number of Db calls and also the large record retrieved for contact, but we are only using Name 99% in this situation. I realize the DB schema above is tiny, but this is just a simplified example and the real contact table has about 50 fields.
To manage this type of situation in the past (prior to using EF), I have typically built out "detail" views for the tables I will use. The "detail" views contain common lookup/calculated fields so that it only takes 1 call to the DB to efficiently get all the info I need (NOTE: We also use indexing on our SQL views to make this extremely efficient for reading) Here is a list of views that I will commonly use (as they will contain "look up" fields from related tables):
ALTER VIEW [icoprod].[BlogDetail]
AS
SELECT B.[BlogID],
B.[BlogName],
B.[BlogDescription],
B.[CreatedByID],
B.[ModifiedByID],
CREATEDBY.[ContactName] AS CreatedByName,
MODIFIEDBY.[ContactName] AS ModifiedByName,
(SELECT COUNT(*) FROM Post P WHERE P.BlogID = B.BlogID) AS PostCount
FROM Blog AS B
JOIN Contact AS CREATEDBY ON B.CreatedByID = CREATEDBY.ContactID
JOIN Contact AS MODIFIEDBY ON B.ModifiedByID = MODIFIEDBY.ContactID
ALTER VIEW [icoprod].[PostDetail]
AS
SELECT P.[PostID],
P.[BlogID],
P.[Entry],
P.[CreatedByID],
P.[ModifiedByID],
CREATEDBY.[ContactName] AS CreatedByName,
MODIFIEDBY.[ContactName] AS ModifiedByName,
B.Name AS BlogName
FROM Post AS P
JOIN Contact AS CREATEDBY ON P.CreatedByID = CREATEDBY.ContactID
JOIN Contact AS MODIFIEDBY ON P.ModifiedByID = MODIFIEDBY.ContactID
JOIN Blog AS B ON B.BlogID = P.BlogID
Here is an overview of my "POCO" objects:
public class Blog
{
public int ID { get; set; }
public string Name { get; set; }
public int CreatedByID { get; set; }
public DateTime ModifiedByID { get; set; }
}
public class Post
{
public int ID { get; set; }
public string Name { get; set; }
public int CreatedByID { get; set; }
public DateTime ModifiedByID { get; set; }
}
public class Contact
{
public int ID { get; set; }
public string Name { get; set; }
public string Email { get; set; }
public string Title { get; set; }
public string Address { get; set; }
public string City { get; set; }
public string MobilePhone { get; set; }
}
public class BlogDetails : Blog
{
public string CreatedByName { get; set; }
public string ModifiedByName { get; set; }
public int PostsCount { get; set; }
}
public class PostDetails : Post
{
public string CreatedByName { get; set; }
public string ModifiedByName { get; set; }
public string BlogName { get; set; }
}
The reason I like this approach is that it allows me to retrieve information from the database based on tables or views AND if I load a view, the view contains all the "table" information which would allow me to load from a view but save to a table. IMO, this gives me the best of both worlds.
I have used this approach in the past, but typically, I just loaded information from the DB using datarows or info from stored procs or even used subsonic activerecord pattern and mapped fields after loading from the DB. I am really hoping I can do something in EF that lets me load these objects without creating another layer of abstraction.
Here is what I have tried to use for configuration (using Fluent API and code-first EF):
public class PostConfiguration : EntityTypeConfiguration<Post>
{
public PostConfiguration()
: base()
{
HasKey(obj => obj.ID);
Property(obj => obj.ID).
HasColumnName("PostID").
HasDatabaseGeneratedOption(DatabaseGeneratedOption.Identity).
IsRequired();
Map(m =>
{
m.ToTable("Post");
});
}
}
public class BlogConfiguration : EntityTypeConfiguration<Blog>
{
public BlogConfiguration()
: base()
{
HasKey(obj => obj.ID);
Property(obj => obj.ID).
HasColumnName("BlogID").
HasDatabaseGeneratedOption(DatabaseGeneratedOption.Identity).
IsRequired();
Map(m =>
{
m.ToTable("Blog");
});
}
}
public class ContactConfiguration : EntityTypeConfiguration<Contact>
{
public ContactConfiguration()
: base()
{
HasKey(obj => obj.ID);
Property(obj => obj.ID).
HasColumnName("ContactID").
HasDatabaseGeneratedOption(DatabaseGeneratedOption.Identity).
IsRequired();
Map(m =>
{
m.ToTable("Contact");
});
}
}
public class PostDetailsConfiguration : EntityTypeConfiguration<PostDetails>
{
public PostDetailsConfiguration()
: base()
{
Map(m =>
{
m.MapInheritedProperties();
m.ToTable("icoprod.PostDetails");
});
}
}
public class BlogDetailsConfiguration : EntityTypeConfiguration<BlogDetails>
{
public BlogDetailsConfiguration()
: base()
{
Map(m =>
{
m.MapInheritedProperties();
m.ToTable("icoprod.BlogDetails");
});
}
}
At this point, I have tried to use a view containing all of the information from the table with "extended" information and when I try this I get the dreaded 3032 error (error sample here). Then I tried to have the view ONLY contain the Primary key of the table and the "extended" properties (e.g. [Entry] is not in PostDetails view). When I try this, I get the following error:
All objects in the EntitySet 'DBContext.Post' must have unique primary keys. However, an instance of type 'PostDetails' and an instance of type 'Post' both have the same primary key value, 'EntitySet=Post;ID=1'.
So I have played with leaving off MapInheritedProperties a bit, but with no luck. I continue to get a similar error.
Does anyone have a suggestion on how to "extend" a base/table object and load info from a view? Again, I believe there is a big performance gain by doing this. The article I referenced at the beginning of this question has 2 potential solutions, but 1 requires too many DB hits (just to get some common lookup info) and the other requires an additional layer of abstraction (and I would really like to go directly to my POCO's from the DB, without writing any mapping).
Lastly, thank you to everyone who answers these types of questions. I applaud everyone who has contributed to responses over the years. I think too many of us developers take this information for granted!!
Loading record from view and saving it to table will not work with code mapping - Blog entity will always be loaded from table and saved to table and BlogDetail entity will always be loaded from view and saved to view - so you must have updatable view or instead of trigger to support this scenario. If you use EDMX you can also map custom SQL / Stored procedure executed for insert, update and delete to force saving to table but this feature is not available in code mapping. Anyway it is not your biggest problem.
You can use your view and you can map it to class as you did but you must not map the inheritance. The reason is the way how inheritance works. Inheritance says that entity is either parent or child (which can act as parent). There can never be database record which can be be both parent (I mean only parent) or child. It is even not possible in .NET because to support this scenario you need two instances - on of parent type and one of child type. These two instances are not equivalent because pure parent cannot be cast to child (it is not a child). And here comes the biggest problem. Once you map inheritance the key must be unique in the whole inheritance hierarchy. So you can never have two instances (one for parent and one for child) with the same key.
As a workaround don't derive BlogDetail from mapped entity (Blog). Either use third not mapped class as parent for both or interface. Also don't use MapInheritedProperties to make your BlogDetail completely unrelated to Blog.
Another workaround is not mapping BlogDetail at all. In such case you can use your code as is and instead of using a view create simple reusable query with projection:
var blogDetails = from b in context.Blogs
where ...
select new BlogDetail
{
Name = b.Name,
CreatedByID = b.CreatedByID,
...
CreatedByName = b.CreatedBy.Name // You need navigation property
...
};
In both cases if you need to save Blog you must create new instance and fill it from BlogDetail. After that you attach it to context, set it to modified state and save changes.
这篇关于EF 4代码第一 - 组合视图和表格的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
