title: EF 4 Code First - Combine Views and Tables
body: <p>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:</p>
  
  <p><a href="https://stackoverflow.com/questions/3756082/how-do-i-add-a-calculated-column-to-my-ef4-model">Add Calculated field to Model</a></p>
  
  <p>Ultimately, I have the same question, but I am hoping for a better solution.</p>
  
  <p>Consider the following DB Tables:</p>
  
  <pre><code>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
  )
  </code></pre>
  
  <p>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;</p>
  
  <p>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.</p>
  
  <p>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):</p>
  
  <pre><code>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
  </code></pre>
  
  <p>Here is an overview of my "POCO" objects:</p>
  
  <pre><code>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; }
  }
  </code></pre>
  
  <p>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.</p>
  
  <p>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.</p>
  
  <p>Here is what I have tried to use for configuration (using Fluent API and code-first EF):</p>
  
  <pre><code>public class PostConfiguration : EntityTypeConfiguration&lt;Post&gt;
  {
      public PostConfiguration()
          : base()
      {
          HasKey(obj =&gt; obj.ID);
  
          Property(obj =&gt; obj.ID).
              HasColumnName("PostID").
              HasDatabaseGeneratedOption(DatabaseGeneratedOption.Identity).
              IsRequired();
  
          Map(m =&gt;
              {
                  m.ToTable("Post");
              });
      }
  }
  
  public class BlogConfiguration : EntityTypeConfiguration&lt;Blog&gt;
  {
      public BlogConfiguration()
          : base()
      {
          HasKey(obj =&gt; obj.ID);
  
          Property(obj =&gt; obj.ID).
              HasColumnName("BlogID").
              HasDatabaseGeneratedOption(DatabaseGeneratedOption.Identity).
              IsRequired();
  
          Map(m =&gt;
              {
                  m.ToTable("Blog");
              });
      }
  }
  
  public class ContactConfiguration : EntityTypeConfiguration&lt;Contact&gt;
  {
      public ContactConfiguration()
          : base()
      {
          HasKey(obj =&gt; obj.ID);
  
          Property(obj =&gt; obj.ID).
              HasColumnName("ContactID").
              HasDatabaseGeneratedOption(DatabaseGeneratedOption.Identity).
              IsRequired();
  
          Map(m =&gt;
              {
                  m.ToTable("Contact");
              });
      }
  }
  
  public class PostDetailsConfiguration : EntityTypeConfiguration&lt;PostDetails&gt;
  {
  
      public PostDetailsConfiguration()
          : base()
      {
  
          Map(m =&gt;
              {
                  m.MapInheritedProperties();
                  m.ToTable("icoprod.PostDetails");
              });
  
      }
  
  }
  
  public class BlogDetailsConfiguration : EntityTypeConfiguration&lt;BlogDetails&gt;
  {
  
      public BlogDetailsConfiguration()
          : base()
      {
  
          Map(m =&gt;
              {
                  m.MapInheritedProperties();  
                  m.ToTable("icoprod.BlogDetails");
              });
  
      }
  
  }
  </code></pre>
  
  <p>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 (<a href="http://blog.abodit.com/2010/04/error-3032-problem-in-mapping-fragments-mapped-to-the-same-rows-in-table/" rel="nofollow noreferrer">error sample here</a>).  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:</p>
  
  <pre><code>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'.
  </code></pre>
  
  <p>So I have played with leaving off MapInheritedProperties a bit, but with no luck.  I continue to get a similar error.</p>
  
  <p>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).</p>
  
  <p>Lastly, <b>thank you</b> 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!!</p>