EF 4代码优先 - 组合视图和表 [英] EF 4 Code First - Combine Views and Tables
问题描述
我研究了这个问题几天，似乎找不到一个选项我感觉很好;但是，这里是一个非常类似问题的链接：
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.
考虑以下数据库表：
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
)
现在我想使用视图来加载common查找/计算信息。每次我们在网站上显示一条信息时，我们想知道创建信息的人的姓名和最后修改信息的人。这两个字段存储在与post表分开的表中。我可以很容易地使用下面的语法（假设应用Lazy / eager加载，CreatedBy是Contact类型的属性，基于CreatedByID）：currentPost.CreatedBy.Name;
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;
该方法的问题是Db调用的数量以及为联系检索的大型记录，但是在这种情况下我们只使用名称99％。我意识到上面的DB模式很小，但这只是一个简化的例子，真正的联系表有大约50个字段。
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.
要管理这种类型的情况过去（在使用EF之前），我通常为我将使用的表建立细节视图。 detail视图包含常用的查找/计算字段，所以它只需要1调用DB以有效地获取所有我需要的信息（注意：我们还使用索引在我们的SQL视图，使这非常有效的阅读）这里是我将通常使用的视图列表（因为它们将包含相关表中的查找字段）：
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
这里是我的POCO objects：
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; }
}
我喜欢这种方法的原因是它允许我检索信息基于表或视图的数据库，如果我加载视图，视图包含所有表信息，这将允许我从视图加载，但保存到表。 IMO，这给了我最好的两个世界。
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.
我以前使用这种方法，但通常，我刚从数据库加载信息使用datarows或信息存储过程或甚至在从DB加载后使用的亚音速活动记录模式和映射字段。我真的希望我能在EF中做一些事情，让我加载这些对象，而不创建另一层的抽象。
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.
这是我试图用于配置Fluent API和代码优先EF）：
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");
});
}
}
，我试图使用一个视图包含所有的信息从表与扩展信息，当我尝试这个我得到可怕的3032错误（ error sample here ）。然后我试图让视图只包含表的主键和扩展属性（例如[Entry]不在PostDetails视图中）。当我尝试这个，我得到以下错误：
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'.
所以我玩了，离开MapInheritedProperties有点，但没有运气。我继续得到类似的错误。
So I have played with leaving off MapInheritedProperties a bit, but with no luck. I continue to get a similar error.
有没有人有建议如何扩展基地/表对象和从视图加载信息？同样，我相信这样做有很大的性能提升。我在这个问题的开头引用的文章有2个潜在的解决方案，但1需要太多的DB命中（只是为了获得一些常见的查找信息），另一个需要一个额外的抽象层（我真的想直接去
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!!
推荐答案
从视图加载记录并将其保存到表不会使用代码映射 - 博客实体将始终从表中加载并保存到表和BlogDetail实体将总是从视图加载并保存到视图 - 所以您必须有可更新视图或代替触发器支持这种情况。如果使用EDMX，您还可以映射为插入，更新和删除执行的自定义SQL /存储过程，以强制保存到表，但此功能在代码映射中不可用。反正这不是你最大的问题。
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.
你可以使用你的视图，你可以像你一样将它映射到类，但是你不能映射继承。原因是继承如何工作的方式。继承说实体是父或子（可以充当父）。永远不能有数据库记录，可以是父（我的意思是只有父）或子。它甚至不可能在.NET中，因为支持这种情况下，你需要两个实例 - 父类型和一个子类型。这两个实例不等效，因为纯父不能转换为子（它不是一个孩子）。这里面临的最大的问题。一旦映射继承，键在整个继承层次结构中必须是唯一的。所以你永远不能有两个实例（一个为父和一个为子）与相同的键。
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.
作为解决方法，不能从映射实体派生 BlogDetail （ Blog ）。使用第三个未映射类作为父对象或接口。也不要使用 MapInheritedProperties 使您的 BlogDetail 与博客完全无关。
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.
另一种解决方法是不映射BlogDetail。在这种情况下，你可以使用你的代码，而不是使用视图创建简单的可重复使用的查询与投影：
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
...
};
在这两种情况下，如果您需要保存 Blog 您必须创建新实例并从 BlogDetail 填充它。之后，将其附加到上下文，将其设置为修改状态并保存更改。
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屋！
