OneOrZero Relationship using Data Annotation in EF.6?

Question

I have 2 tables, the relationship between them is linked using field TableId1 and TableId2.

What I want is, if both data are linked, field will filled by primary-key value.

That mean field TableId2 in Table1 will filled by Table2.Id and also field TableId1 in Table2 will filled with Table1.Id.

And, I also want both of field can be empty. That mean nolink of them (that data is Independent).

public class Table1
{
    [Key]
    public int Id { get; set; }
    public string table1_desc { get; set; }

    public int? TableId2 { get; set; }
    [ForeignKey("TableId2")]
    public Table2 Table2 { get; set; }
}

public class Table2
{
    [Key]
    public int Id { get; set; }
    public string Table2_desc { get; set; }

    public int? TableId1 { get; set; }
    [ForeignKey("TableId1")]
    public Table1 Table1 { get; set; }
}

My Question is, How solve this problem using Data Annotation ?. is that possible ?. The code a above give me error :

 Unable to determine the principal end of an association between the types 'ConsoleApplication1.Data2' and 'ConsoleApplication1.Data1'. The principal end of this association must be explicitly configured using either the relationship fluent API or data annotations.

Thanks You, Jigu Haslim

score 0 · Accepted Answer · answered Nov 11 '17 at 09:44

No, that is not possible - at all.

A foreign key constraint needs a principal type and a dependant type, so you can configure the cascades if nessacary. That is excactly what the error message is telling you.

If you need a practical example, think of a mother cat and one of its kitten. The mother cat is the principal. It can exist without its kittens. But the kittens are strongly dependant on its principal mother.

Delete the mother and the deletion will cascade down to its kittens. Delete the children, and the mother will live on.

So you have to make one entity the principal

public class Table1
{
    [Key]
    public int Id { get; set; }
    public string table1_desc { get; set; }
    public Table2 Table2 { get; set; }
}

And the other one the dependant

public class Table2
{
    [Key, ForeignKey("Table1")]
    public int Id { get; set; }
    public string Table2_desc { get; set; }

    public Table1 Table1 { get; set; }
}

Gert Arnold · Answer 2 · 2017-11-11T14:38:45.070

There is a way to do this, not with data annotations though, and I don't think I'd recommend it (so feel free to vote me away if you don't like it).

The trick is to map both relationships as one-to-many. For instance with cars and drivers:

public class Car
{
    public int CarId { get; set; }
    public string Name { get; set; }
    public int? DriverId { get; set; }
    public Driver Driver { get; set; }
}

public class Driver
{
    public int DriverId { get; set; }
    public string Name { get; set; }
    public int? CarId { get; set; }
    public Car Car { get; set; }
}

Mapping:

modelBuilder.Entity<Driver>()
            .HasOptional(d => d.Car).WithMany()
            .HasForeignKey(d => d.CarId);
modelBuilder.Entity<Car>()
            .HasOptional(c => c.Driver).WithMany()
            .HasForeignKey(c =>c.DriverId);

But here's the downside. You have to establish the relationship from both sides, otherwise you can get a Car with a DriverId while the belonging driver has CarId = null. But if you do this ...

var car = new Car { Name = "Ford" };
var driver = new Driver { Name = "Henry" };
driver.Car = car;
car.Driver = driver;

db.Set<Car>().Add(car);
db.SaveChanges();

... an exception is thrown:

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

Yeah, the driver needs the new car's primary key, and the car that of the new driver.

So this requires you to do it this way:

var car = new Car { Name = "Ford" };
var driver = new Driver { Name = "Henry" };
driver.Car = car;

db.Set<Car>().Add(car);

using(var ts = new TransactionScope())
{
    db.SaveChanges();
    car.Driver = driver;
    db.SaveChanges();
    ts.Complete();
}

Not too elegant, is it?

FYI: this is an elaboration of one of the options in this answer in which you may find better alternatives.

OneOrZero Relationship using Data Annotation in EF.6?

2 Answers2