EF Code First Fluent Mapping: 0-1 to Many: HasOptional() WithMany()

Question

I'm using EF on a project with an existing database that I'm stuck with. I have 2 tables with an odd mapping (I believe its 0-1 to many). Part of the problem is there might be garbage in the fields so I want it to be entirely possible to get null back from the nav property.

A TruckPart can be optionally related to a Part. A Part can be related to 0 or more Truck parts (although I don't need to traverse the relation in that direction)

I've got my EF close, except that when I pull in the relation via an Include("Part"), it uses an INNER JOIN and I need it to use a LEFT JOIN.

Classes:

public class TruckPart
{
    public int TruckPartId { get; set; }
    public int PartId { get; set; }   
    public string Location { get; set; }
    public virtual Part Part { get; set; }
    // plus other fields
}

public class TruckPartMapping : EntityTypeConfiguration<TruckPart>
{
    public TruckPartMapping()
    {
        // Primary Key
       HasKey(t => t.TruckPartID);

       HasRequired(t => t.Part).WithMany().HasForeignKey(t => new { t.Location, t.PartID });
       //HasOptional(t => t.Part).WithMany().HasForeignKey(t => new { t.Location, t.PartID });
       //HasOptional(t => t.Part).WithMany().Map(t => t.MapKey("Location", "PartID"));
    }
}

public class Part
{
    public string Loc { get; set; }
    public int PartID { get; set; }
    // plus other fields
}

public class PartMapping : EntityTypeConfiguration<Part>
{
    public PartMapping()
    {
        // Primary Key
        HasKey(t => new { t.Loc, t.PartID});
    }
}

I've tried HasOptional() instead of HasRequired() but I get the following error (I assume because HasOptional() and HasForeignKey() don't get along)

System.Data.Entity.Edm.EdmAssociationType: 
    Multiplicity conflicts with the referential constraint in Role 
   'TruckPart_Part_Target' in relationship 'TruckPart_Part'. 
    Because all of the properties in the Dependent Role are non-nullable, 
    multiplicity of the Principal Role must be '1'.

I've tried replacing HasForeignKey with Map(MapKey) but I get error (I assume because I already have those properties):

 error 0019: Each property name in a type must be unique. 
             Property name 'Location' was already defined.
 error 0019: Each property name in a type must be unique. 
             Property name 'PartID' was already defined.

Am I even able to add this sort of nav property in EF?

Did you intend to have lazy loading enabled on Part? public virtual Part Part { get; set; } — Greg
Do you have Entity Framework Power Tools installed? That way you can view your context file in a diagram and see the types of joins that are being created. — Greg
I am using SQL Profiler and viewing the SQL that is run against the DB — Adam Tegen
I would suggest using the EF power tools for this. If I understand you correctly, the errors you are seeing are EF errors? I had a similar issue on a recent project and it was a life saver. If you choose to install them, there is a minor issue you will hit, but its easy to sort out. Then you can view the EF schema and see what joins are being produced. — Greg

Slauma Slauma · Accepted Answer · 2013-09-07T17:30:03

In order to make the relationship optional you need a nullable foreign key property in your TruckPart entitiy (plus the mapping with HasOptional):

public int? PartId { get; set; }

EF Code First Fluent Mapping: 0-1 to Many: HasOptional() WithMany()

1 Answers