title: Nullable entity projection in Entity Framework
body: <p>I have a following SQL Server 2005 database schema:</p>
  
  <pre><code>CREATE TABLE Messages (
     MessageID int,
     Subject varchar(500),
     Text varchar(max) NULL,
     UserID NULL
  )
  </code></pre>
  
  <p>The column "UserID" - which can be null - is a foreign key and links to the table</p>
  
  <pre><code>CREATE TABLE Users (
     UserID int,
     ...
  )
  </code></pre>
  
  <p>Now I have several POCO classes with names Message, User etc. that I use in the following query:</p>
  
  <pre><code>public IList&lt;Message&gt; GetMessages(...) {
    var q = (from m in dataContext.Messages.Include("User")
             where ...
             select m); // could call ToList(), but...
  
    return (from m in q
            select new Message {
              ID = m.MessageID,
              User = new User {
                ID = m.User.UserID,
                FirstName = m.User.FirstName,
                ...
              }
            }).ToList();
  }
  </code></pre>
  
  <p>Now note that I advise the entity framework - using Include("Users") - to load a user associated with a message, if any. Also note that I don't call ToList() after the first LINQ statement. By doing so only specified columns in the projection list - in this case MessageID, UserID, FirstName - will be returned from the database. </p>
  
  <p>Here lies the problem - as soon as Entity Framework encounters a message with UserID == NULL, it throws an exception, saying that it could not convert to Int32 because the DB value is NULL.</p>
  
  <p>If I change the last couple of lines to</p>
  
  <pre><code>return (from m in q
          select new Message {
             ID = m.MessageID,
             User = m.User == null ? null : new User {
                ID = m.User.UserID,
                ...
             }
          }).ToList()
  </code></pre>
  
  <p>then a run-time NotSupportedException is thrown telling that it can't create a constant User type and only primitives like int, string, guid are supported.</p>
  
  <p>Anybody has any idea how to handle it besides materializing the results just right after the first statement and using in-memory projection afterwards? Thanks.</p>