EF6: Using collection property without creating FK field in child table

Question

I'm studying EF6 and think I know quite a bit already, but couldn't find a good solution (yet) for this:

Suppose I have the following model classes:

class LivingRoom {
    public int Id { get; set; }
    public string Name { get; set; }

    public PersonTypeId { get; set; }
    public IList<Person> Persons { get; set; } 
}

class Person {
    public int Id { get; set; }
    public int PersonTypeId { get; set; }
    public string Name { get; set; }
}

With these model classes I'm able to save and load via DbContext without any problem. Thanks to the navigation property in the "parent" LivingRoom class, the Persons collection will be included in this process. I don't have to load/save them separately.

UPDATE: Forgot the logical PersonTypeId field which will be used for determining which Persons should be in the collection property.

So far so good.

But EF6 is creating a FK in the Persons table, pointing to the LivingRooms table, which seems logical.

But what if I'm going to use the Persons table for a lot more other parent entities, like eg. "Bus" and "Plane", and therefore don't want to have a dependency (= FK field in LivingRooms table) in the Persons table?

Can I achieve this (don't create the FK field) without breaking the "include child list" load/save process as described?

If yes, how? And if no, why not?

NB: Please understand that I want to learn the best techniques. So good advice, to not doing this, is also welcome.

How would you determine if `Person` is part of `LivingRoom`, `Bus`, `Plane` etc. w/o storing that information in a FK columns in `Person` table? It's not EF specific, but relational database design issue. — Ivan Stoev, Nov 12 '17 at 11:13
*don't want to have a dependency* This is a common (anti) pattern called *polymorphic associations*, something I've had [my own struggles with](https://stackoverflow.com/q/8895806/861716). Relational databases don't offer the conceptual basis to model such associations smoothly, so unless you're willing to turn to NoSQL databases you'll have to settle with a solution that's uncomfortable one way or the other. — Gert Arnold, Nov 12 '17 at 12:16

osman Rahimi · Answer 1 · 2017-11-12T11:15:29.197

First , it's better to handle FK in Person Table ourself to do that web have this :

class LivingRoom
{
    public int Id { get; set; }
    public string Name { get; set; }

    public IList<Person> Persons { get; set; }
}

class Person
{
    public int Id { get; set; }
    public string Name { get; set; }
    public LivingRoom LivingRoom { get; set; }
    public int LivingRoomId { get; set; }
}

now If you have others Entities Like Bus and ... so we have

public class Bus
{
    public int Id { get; set; }
    public ICollection<Person> People { get; set; }
}

and Updated Person class is :

 public class Person
{
    public int Id { get; set; }
    public string Name { get; set; }
    public LivingRoom LivingRoom { get; set; }
    public int LivingRoomId { get; set; }

    public Bus Bus{ get; set; }
    public int BusId { get; set; }

}

you can set FK in Person Table as Nullable to do this :

 public class Person
{
    public int Id { get; set; }
    public string Name { get; set; }
    public LivingRoom LivingRoom { get; set; }
    public int? LivingRoomId { get; set; }

    public Bus Bus{ get; set; }
    public int? BusId { get; set; }

}

As you can see We set BusId and LivingRoomId as nullable or you can just set one of them that you want

Note : You need to add some mapper to tell EF which field id FK and something like this ,...

Thanks, but my question was if it's possible to *not* having FK fields. — , Nov 12 '17 at 11:16
It's the same as having just collections in other entities - EF would automatically create optional FK columns as in your suggestion (kust the names by convention will be different). — Ivan Stoev, Nov 12 '17 at 11:16
@JohanV no you have to have ` FK` in your table (I mean in the result in SQL Server) — osman Rahimi, Nov 12 '17 at 11:22
@IvanStoev yes `EF` automatically create FK columns , but it's better to have `FK` properties in our Models — osman Rahimi, Nov 12 '17 at 11:24

EF6: Using collection property without creating FK field in child table

1 Answers1