EF6 Help in defining the foreign key of my model (optional foreign key & conflicting multiplicities)

Question

I am new to EF6 and fluent API, but I really tried all the possibilities before trying to bother you here but I am kind of stuck now.

I have the following model:

public class Event
{
    public int Id { get; set; }
    [Required]
    public string Label { get; set; }
}

public class Address
{
    public int Id { get; set; }
    [Required]
    public String City { get; set; }

}

Here's what I am trying to do:

1. The event should have a required field Address
2. The address can be used in an event or in another class (let's call it Person for example). Therefore, it shouldn't be directly be bound to the Event class
3. When deleting an Event (or Person), I want to cascade and delete the linked Address

I tried many possibilities in Fluent API by looking at the documentations and searching on the net. Despite that, I kept having various issues: conflicting multiplicities, inconsistent model or not being able to cascade on delete.

Could someone please help? I am really stuck! :)

Thank you!

!!!UPDATE!!! As requested, here's the best solution I could find:

public class Event
{
    public int Id { get; set; }
    [Required]
    public string Label { get; set; }
    [Required]
    public virtual Address.Address Address { get; set; }
}

public class Address
{
    public int Id { get; set; }
    [Required]
    public String City { get; set; }
}

And the fluent code as follows:

modelBuilder.Entity<Event>()
            .HasRequired(e => e.Address)
            .WithOptional()
            .Map(e => e.MapKey("AddressId"))
            .WillCascadeOnDelete(true);

This implementation adds a foreign key in the Event table to the Address table. However, the cascade on delete will work in the following direction: deleting the Address will delete the Event. Unfortunately, I am trying to accomplish the opposite: I want the Address to be deleted only when the Event is!

Thank you again :)

Answer 1

Your requirements are fighting each other.

• Cascade delete is from principal to dependent, so when an Address should be deleted when an Event is, the Event must be the principal (the entity to which a foreign refers).
• If more than one class must have an Address, the other classes should refer to Address, which makes Address the principal.

There is an alternative. Address could have two foreign keys, to Person and to Event. But this is not very appealing, because the keys must both be nullable and there is no database constraint to enforce that they are mutually exclusive.

Another alternative is to make Address a complex type, so both the Event and the Person tables will have all address columns. Of course this is not well normalized, and you can't handle Addresses as separate entities, but at least there is no cascade issue, because an address is part of the Event record.

I think your best option is to have several classes refer to Address and write logic to delete orphan addresses.