Nullable entity projection in Entity Framework

Question

I have a following SQL Server 2005 database schema:

CREATE TABLE Messages (
   MessageID int,
   Subject varchar(500),
   Text varchar(max) NULL,
   UserID NULL
)

The column "UserID" - which can be null - is a foreign key and links to the table

CREATE TABLE Users (
   UserID int,
   ...
)

Now I have several POCO classes with names Message, User etc. that I use in the following query:

public IList<Message> 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();
}

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.

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.

If I change the last couple of lines to

return (from m in q
        select new Message {
           ID = m.MessageID,
           User = m.User == null ? null : new User {
              ID = m.User.UserID,
              ...
           }
        }).ToList()

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.

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.

Answer 1

I suspect your relationship is not 1 to 1.

Answer 2

Since you did an .Include("Users") you should be able to just traverse the User property in the Message object to get the information your want.

Answer 3

You forgot to include the declaration of your "Message" class but I suspect that the UserID property in that class is not declared as a nullable type. If that is the case, change it from "int" to "int?" (nullable int).