EF 4 Code First - Combine Views and Tables

Question

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:

Add Calculated field to Model

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!!

Answer 1

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.