Reference child entity with multiple common parent

Question

I have the following data structures:

public class Foo
{
    [Key]
    public int Id { get; set; }
    public IList<Bar> Bars { get; set; }
}
public class Bar
{
    [Key]
    public int Id { get; set;}
    public int FooOneId { get; set;}
    public int FooTwoId { get; set; }

    [ForeignKey("FooOneId")]
    public Foo FooOne { get; set; }
    [ForeignKey("FooTwoId")]
    public Foo FooFwo { get; set;}
}

What I want to do when querying the context is to include all the Bars that are joined either by FooOneId or FooTwoId. So I would like to do something like:

var foos = context.Foos.Where(f => f.Id == id).Include(f => f.Bars).ToList();

in order to include all the associated Bars when I retrieve a Foo from the database.

But I get following exception when the context builds the model:

System.InvalidOperationException: Unable to determine the relationship
represented by navigation property 'Foo.Bars' of type 'IList<Bar>'

If I remove FooTwoId and FwoTwo then it has a simple Parent/Child relationship and is quite happy.

Any ideas how to do this? I don't mind if I have to have the list of Bars as two separate lists - I just want do this is a single database hit.

Answer 1

Why not invert it and let Bar be the driver?

var bars = context.Bars.Where(f => f.Foo1Id == id || f.Foo2Id == id).Include("FooOne").Include("FooTwo").ToList();

Of course, you would have to process which Foo to use in the receiving end.

This is actually a Many-to-Many relationship and should be designed that way. Arguably, this increases complexity but more accurately describes the relationship.

public class Foo
{
    [Key]
    public int Id { get; set; }

    public IList<FooBar> Bars { get; set; }
}
public class Bar
{
    [Key]
    public int Id { get; set;}

    public IList<FooBar> Foos { get; set;}
}
public class FooBar
{
    [Key]
    public int Id { get; set; }
    public int FooId { get; set; }
    public int BarId { get; set; }

    [ForeignKey("FooId")]
    public Foo Foo { get; set; }
    [ForeignKey("BarId")]
    public Bar Bar { get; set;}
}

context.FooBar.Where(f => f.FooId == id).Include("Foo").Include("Bar").ToList();

or

context.Foo.Where(f => f.Id == id).Include("FooBar").Include("FooBar.Bar").ToList();