Entity Framework, Many-to-Optional, Without a Foreign Key?

Question

I have two tables, which are from a legacy system. These tables are periodically updated from two separate external sources and are to be used as merely "read-only" tables for look-up data in my application:

DeliverySites

public partial class DeliverySite
{
    public string CustomerID { get; set; } // PK
    public string CustomerName { get; set; }
    public string DeliveryAddress { get; set; }
    public string BillingAddress { get; set; }
    //... fields removed for clarity.

    // Navigational Properties.
    public virtual ICollection<Item> Items { get; set; }
}

public class DeliverySiteMap : EntityTypeConfiguration<DeliverySite>
{
    public DeliverySiteMap()
    {
        // Primary Key
        this.HasKey(t => t.CustomerID);

        // Properties
        this.Property(t => t.CustomerID)
            .IsRequired()
            .HasMaxLength(50);

        this.Property(t => t.CustomerName)
            .IsRequired()
            .HasMaxLength(50);

        this.Property(t => t.DeliveryAddress)
            .IsRequired();

        this.Property(t => t.BillingAddress)
            .IsRequired();

        // Table & Column Mappings
        this.ToTable("DeliverySites");
        this.Property(t => t.CustomerID).HasColumnName("CustomerID");
        this.Property(t => t.CustomerName).HasColumnName("CustomerName");
        this.Property(t => t.DeliveryAddress).HasColumnName("DeliveryAddress");
        this.Property(t => t.BillingAddress).HasColumnName("BillingAddress");   
    }
}

Items

public partial class Item
{
    public string Item { get; set; }  // PK
    public string ItemDescription { get; set; }
    public decimal Brand { get; set; }
    public decimal Price { get; set; }
    public string CustomerID { get; set; }  // PK + FK
    //... fields removed for clarity.

    // Navigational Properties.
    public virtual DeliverySite DeliverySite { get; set; }
}

public class ItemMap : EntityTypeConfiguration<Item>
{
    public ItemMap()
    {
        // Primary Key
        this.HasKey(t => new { t.Item, t.CustomerID });

        // Properties
        this.Property(t => t.UserItem)
            .HasMaxLength(50);

        this.Property(t => t.UserItemDescription)
            .HasMaxLength(255);

        this.Property(t => t.CCItem)
            .IsRequired()
            .HasMaxLength(50);

        this.Property(t => t.CCItemDescription)
            .IsRequired()
            .HasMaxLength(255);

        this.Property(t => t.CustomerID)
            .HasMaxLength(50);

        // Table & Column Mappings
        this.ToTable("Items");
        this.Property(t => t.Item).HasColumnName("Item");
        this.Property(t => t.ItemDescription).HasColumnName("ItemDescription");
        this.Property(t => t.Brand).HasColumnName("Brand");
        this.Property(t => t.Price).HasColumnName("Price");
        this.Property(t => t.CustomerID).HasColumnName("CustomerID");
    }
}

Given that these tables are updated independently, there may be "Items" entered for "DeliverySites" which do not exist.

Thus, I want to make an optional relation. (So I can make use of navigational properties within my application, but so that I do not prevent the tables from being independently updated.)

Within my ItemMap : EntityTypeConfiguration<Item> I've tried the following:

this.HasOptional(x => x.DeliverySite)
    .WithMany(x => x.Items)
    .HasForeignKey(x => x.CustomerID)
    .WillCascadeOnDelete(false);

but I get this error:

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

How should I be implementing this relationship?

Also, it would be ideal if I could do this without adding any FK constraints in the database. Is this possible?

Answer 1

I know it's not the answer you want but IMHO your time would be better spent crafting a solution that ensures the integrity of your legacy database. Looking for work-arounds in an ORM for a denormalized DB is not only bad practice but it will cause headaches for your peers and devalue your product. Not sure how the data is persisted but you could hold items in a temp table and only add to the normalised table when the corresponding Delivery Site is present.

Answer 2

Most likely you are having this error because Item.CustomerID is required. Although you didn't call IsRequired() in your mapping it is required because it is part of the composite primary key...

this.HasKey(t => new { t.Item, t.CustomerID });

...and because every column of a composite key does not allow NULL in the database. Therefore EF wants to have the mapping

this.HasRequired(x => x.DeliverySite)
    .WithMany(x => x.Items)
    .HasForeignKey(x => x.CustomerID)
    .WillCascadeOnDelete(false);

To me the real problem seems to be that you are trying to create a relationship and navigation properties with EF where no real relationship and foreign key constraint is in the database.

If Item.CustomerID can have a value in the database that does not exist as a row in table DeliverySites you can't use HasRequired because if you would try to load an Item with an Include for the DeliverySite EF will create an INNER JOIN and Items that don't refer to an existing DeliverySite via the CustomerID won't get loaded at all. So, basically you would be getting wrong query results. On the other hand you can't use an optional mapping (which would lead to LEFT OUTER JOIN) because CustomerID is part of the PK and it results in your exception.

Honestly with your legacy database schema I wouldn't try to introduce navigation properties and an artificial relationship in your EF model. If you need "related" DeliverySites and Items in one query I would probably prefer to just use manual LINQ joins with the CustomerID as an ordinary scalar property to join the data on and project the loaded DeliverySites and Items into a specialized class (or anonymous objects).

Answer 3

I believe this would work if you remove the foreign key and just make it:

this.HasOptional(x => x.DeliverySite)
    .WithMany(x => x.Items);