2

Our database has three entities: Person, Company and PhoneCall.

Person describes an individual. Company describes a company. PhoneCall describes the details of a phone call to either a Person or Company.

public class Person
{
    public int Id { get; set;}
    public string FirstName { get; set; }
    public string LastName { get; set; }
    public DateTime DateOfBirth { get; set; }
}

public class Company
{
    public int Id { get; set;}
    public string Name { get; set; }
    public string VatNumber { get; set; }
}

public class PhoneCall
{
    public int Id { get; set;}
    public string Description { get; set;}
    public int Duration { get; set;}

    public int? PersonId { get; set; }
    public Person Person { get; set; }

    public int? CompanyId { get; set; }
    public Company Company { get; set; }

}

I am seeing certain undesirable behavior with Entity Framework when deleting a Person or Company.

If I delete a Person, entity framework is updating any associated PhoneCall entries and setting PersonId to NULL.

dbContext.Entry(person).State = EntityState.Deleted;
dbContext.SaveChanges();

Instead of Entity Framework setting PersonId to null on all of the associated PhoneCall entries, what I would like is for entity framework throw some kind exception to let me know that the Person cannot be deleted because there are related entities referencing said Person.

The problem seems to be that entity framework doesn't respect the PhoneCall.PersonId and PhoneCall.CompanyId foreign keys since they are both (necessarily) nullable.

I can obviously perform my own check before deletion like so:

if (phoneCallDbSet.Where(ph => ph.Person == personToDelete).Any())
    throw new InvalidOperationException("Cannot delete person with associated calls");

...but my preference would be to set up a some kind of restriction that prevents Entity Framework from setting the foreign key to NULL in the first place, so that if a developer forgets to perform this check, we don't end up with entries in the PhoneCall table linking to nothing.

Is this possible?

NoPyGod
  • 4,905
  • 3
  • 44
  • 72
  • changing the type of foreignkey-column instead `int?` to `int` makes the relationship to not-nullable. or why are they neccessarily nullable? Either they may be nullable, so the relation can be null or they may not be nullable and a relation is needed. every thing else you do with cascase delete – Matthias Burger Aug 30 '16 at 09:37
  • Possible duplicate of [How to disable cascade delete for link tables in EF code-first?](http://stackoverflow.com/questions/13705441/how-to-disable-cascade-delete-for-link-tables-in-ef-code-first) – Martheen Aug 30 '16 at 09:39
  • The two columns are nullable because a Phone call can be made to either a Company OR a Person. – NoPyGod Aug 30 '16 at 10:03
  • If I delete a Person in the database with an SQL query it does enforce the foreign key constraint and prevents the person from being deleted... it's just Entity framework giving me trouble because of this automatic NULL setting behavior. – NoPyGod Aug 30 '16 at 10:09
  • 3
    Your schema assumes, that your PhoneCall entity can exist without both Person and Company set, so you might need to rearrange your schema to not allow for that - either with some inheritance or many-to-many for your PhoneCall-Company, PhoneCall-Person relationship. Another way is also to extract a base class for Company/Person, but that might limit your options in future for inheritance for those two. – Red Aug 30 '16 at 10:35
  • Do you have a join table for each relationship? (i. e. CompanyPhoneCall and PersonPhoneCall tables?) – Chris Dunaway Aug 30 '16 at 16:08

1 Answers1

4

No!! There is no way to achieve what you want without having explicit checks in your code.

If we set the property as nullable, we tell the framework that you can set it to null if required. Then why would it show any error when it is set to null?

Either you make the property non-nullable or have explicit checks in your code to handle it.

Jatin Nath Prusty
  • 557
  • 4
  • 16
  • I figured as much, thank you for your response. As an alternative, what do you think about manually creating a trigger in the database that prevents updates to the columns? We'll still do the explicit checks, but this would prevent the columns being set to null in the event that somebody forgot to do an explicit check. – NoPyGod Aug 30 '16 at 10:29
  • yeah!! That's what I have seen many people do in such cases. Triggers sound like a good idea. You would have consistent data which you want. – Jatin Nath Prusty Aug 30 '16 at 10:37