EntityReference returning stored entity not null when the referenced row does not exist RRS feed

  • Question

  • When calling the Load command on an entity reference with MergeOption.OverwriteChanges the entity populated in the navigation property is the stored value not null, which is the current state of the row in a related table. 


    The model for this entity is setup in a 0..1 to 1 relationship which is manually created because the referenced data is generated by a View with the same primary key as the principal table in the relationship. I.e. An Invoice table with Primary Key ID has a navigation property to View Invoice Outstanding Balance with Unique Field ID. The foreign key association is setup on the ID fields in both entities as the Invoice Outstanding Balance is a query that lists the invoice ID and the outstanding amount but only if there is an outstanding amount. As a result of this the Invoice Outstanding Balance entity is extremely volatile and must always be returned from the database. The problem  is when there is an outstanding balance for an invoice an entity object is created and associated to the context, However, when the balance is completely paid off the Invoice ID does not exist in the Invoice Outstanding Balance table. Instead of the navigation property entity being null, the stored entity is returned by the navigation property which is not the desired result. Below is a timeline of events to hopefully clarify the operations and problem being encountered.

    The timeline of events is:

    1. Call the load method of the navigation property reference passing MergeOption.OverwriteChanges to the method
    2. Retrieve the Outstanding Balance of the invoice which is now part of the context
    3. Create an invoice payment and set it to the outstanding amount of the invoice
    4. Associate it to the context
    5. Save Changes
    6. Invoice is paid off so no database value exists for Invoice ID in Invoice Outstanding Balances
    7. Call the load method of the navigation property reference passing MergeOption.OverwriteChanges to the method
    8. Retrieve the Outstanding Balance but instead of being returned a null value, the value of Step 2 is returned although the current state of Storage for this entity is null. 


    The problem is that the entity object in memory is not being removed when the navigation property lookup is performed. 
    The work around would be to detach the entities from the context but this would require me to manually do this at each use of invoice oustanding balances. As I cannot get at the context within the entity itself I foresee a lot of custom code sprinkled wherever I use this entity.  Any thoughts or suggestions?


    Here is the EF ERD:


    Thursday, October 27, 2011 5:31 PM