Creating Navigation Property for Nullable Foreign Key

Question

I'm using EntityFramework v4.3.1 and building my model via code by inheriting from DbContext and overriding OnModelCreating(). I have a table called Companies and a table called Messages. The Messages table has a nullable foreign key Messages.CompanyId. When this is set, the particular message is associated to a specific company. When this field is null, the message is associated to all companies.

My problem is that I don't know how to express this behaviour in the OnModelCreating() method. If I do:

modelBuilder.Entity<Message>().HasRequired(o => o.Company).WithMany(o => o.Messages).Map(o => o.MapKey("CompanyId"));

I successfully get a navigation property on Company such that I can do calls like TestCompany.Messages and I get a list of messages associated to the particular company. But I also need to have all the null messages returned.

How can this be achieved?

Eranga Eranga · Accepted Answer · 2012-04-11T12:04:32

If you want to query messages that are not associated with any company you need to model the scalar property CompanyId in the Message entity class.

public class Message
{
    //other porperties

    public int? CompanyId { get; set; }
}

And configure the relationship using HasOptional instead of HasRequired.

modelBuilder.Entity<Message>()
    .HasOptional(o => o.Company).WithMany(o => o.Messages)
    .HasForeignKey(m => m.CompanyId);

Then you can query using

var messages = db.Messages.Where(m => m.CompanyId == null);

Creating Navigation Property for Nullable Foreign Key

1 Answers