CODEWITHSUNDEEP
c#mysqlentity-framework
Zaphod Fox
Zaphod Fox

Reputation: 58

Can't get Entity Framework zero-or-one to zero-or-one relations to work

I am fairly new to Entity Framework and I've spend the last 4 hours day trying to make a (to me seemingly) simple relation to work.

I have three classes: Person, House, Garage

By now I've read like 10 different documentations that all make matters only more confusing to me. I've tried solving it using annotations but it seems that I also have to incorporate this fluent api stuff.

The furthest I've gotten is to have Houses and Garages associated to a Person, but not to eachother. Now I've been stuck having to define the principle end of a relationship, which I don't even understand what it's supposed to do.

Right now I get

Unable to determine a valid ordering for dependent operations. Dependencies may exist due to foreign key constraints, model requirements, or store-generated values.

from trying to solve a barrage of different errors.

I've been implementing relations like these for ages in PHP, manually, by writing all my own queries. This is the first time I'm doing database stuff in a bigger framework that is supposed to make things easier, but so far I'd have had more success in filling the objects myself from a DatabaseReader.

From my logical point of view the relation between the three Classes/Tables should be really simple yet I can't find examples of similar relations or get it to just work.

So my questions are:

I have prepared a demo project: https://github.com/Zaphox/DatabaseTest
It contains the three classes, context and a test of the relations that should successfully complete (but doesn't).

Thanks for any and all help and hints that lead to the solution to this problem!

Z.

Update 1

After trying all suggestions I noticed that any circular relation causes errors in EF and it seems that it's just not possible to create references like these, even 0..1 to 0..1 self references do not seem to work.

I have however implemented a hack that at least lets me work, but it's horrible and I would love for someone to tell me that there's a better way.

  1. Only Houses have references to Garages, not vice versa
  2. To get a House belonging to a Garage a separate query is required (db.Buildings.OfType<House>().Where(h => h.Garage.Id == myGarage.Id).First())

Also I thought about adding a NotMapped House field in the Garage which does a lookup for the house by itself but it interferes with other queries, so that's not a valid option either.

What annoys me most is that there are a lot of articles about self-references and the likes, but only for 1 to n relations. Any attempt to make a 1 to 1, or 0..1 to 0..1 relation, always fails with EF not being able to figure out the "order".

The thing is that EF thinks it has to make put a GarageId FK in the House table, and a HouseId FK in the Garage table, which is nonsense. I only need a GarageId in the House table, which can used as a backwards reference. I can implement this code-wise but it's not compatible to EF queries and usually interferes with other running queries because you can't run two queries at once.

The demo project on GitHub is updated with the current version, which kinda works but requires a lot of additional querying every time I want to find the House belonging to a Garage.

Upvotes: 2

Views: 107

Answers (2)

Ivan Stoev
Ivan Stoev

Reputation: 205829

Apparently the problem is the relationship between House and Garage.

I only need a GarageId in the House table, which can used as a backwards reference.

In fact this is what happens by default when you put only Garage navigation property on House class (with the only difference that the FK column name is Garage_Id). This is called unidirectional association. With such setup EF considers House to be the dependent, Garage to be the principal and both to be optional. The important part is that FK column is created in dependent table.

So this is what you have currently in your test project and it works. The problem arises when you make the association bidirectional by adding House property to Garage class:

public class House
{
    public int Id { get; set; }
    public Garage Garage { get; set; }
    public string Style { get; set; }
}

public class Garage
{
    public int Id { get; set; }
    public House House { get; set; }
    public int Size { get; set; }
}

In this case EF has no enough information what is your intention, so you need to provide that information. Remember that you want GarageId FK in House table, so here is the corresponding Fluent configuration that you need to add (the Map part is optional - if you skip it, the FK column will be called Garage_Id as in your current project):

modelBuilder.Entity<House>()
    .HasOptional(e => e.Garage)
    .WithOptionalDependent(e => e.House)
    .Map(m => m.MapKey("GarageId"));

and everything will work as expected.

Upvotes: 2

Barry O&#39;Kane
Barry O&#39;Kane

Reputation: 1187

Entity Framework doesn't like relationships where there is no clear principal. In this case as either entity can exist without the other, neither is dependent on the other.

I would suggest redesigning your model so that you have an abstract class Building which House and Garage both derive from. You can then link your Person to a Building, and in the concrete types 'House' and Garage have a link to a Building for the association.

Example:

public abstract class Building
{
    public Person Person { Get; Set; }

    public Building Building { Get; Set; }
}

public class House : Building
{
    // Other House properties
}

public class Garage : Building
{
    // Other Garage properties
}

Then in the business logic you can enforce that the associated building cannot be of the same type with something like this.GetType() != Building.GetType()

Upvotes: 1

Related Questions