One to zero...one relationship

Question

I can't seem to get the relationship between Foo and Bar right.

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

    public int Name { get; set; }

    public virtual Bar Bar { get; set; }
}


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

    public int Name { get; set; }

    public int FooId { get; set; }

    public virtual Foo Foo { get; set; }
}

With this structure I would normally have

public virtual ICollection<Bar> Bars { get; set; }

in Foo, but in this instance, that isn't a workable representation of my data.

You see....

Foo won't always have a Bar ...but... Bar will always have a Foo and its FooId will always be unique across Bar.

public class MyContext : DbContext
{
    public DbSet<Foo> Foos { get; set; }

    public DbSet<Bar> Bars { get; set; }

    protected override void OnModelCreating(System.Data.Entity.DbModelBuilder modelBuilder)
    {
        base.OnModelCreating(modelBuilder);

        // ...............

        modelBuilder.Entity<Foo>()
                    .HasOptional(cpi => cpi.Bar)
                    .WithRequired(api => api.Foo);

        // ...............
    }
}

I have tried multiple attempts at defining the relationship, but can't seem to get it right! I also could add a nullable BarId to Foo if that would enable me to do what I want (but I would prefer not to). (see edit 1)

I have googled it lots (and searched through, and tried), many stack overflow posts that were similar (but not the same).

Any input would be appreciated.

Thanks

Edit 1 (in response to spenders comment):

These tables already exist in a number of live environments and are full of data so I would like to find a solution without editing the db table definitions... but if that is not possible I can add a column to achieve what I need.

Previously I had it defined as...

public virtual ICollection<Bar> Bars { get; set; }

... but that won't work for a piece of code I am writing using these tables.

Edit 2:

The above code gives the following error when I try and add a Bar.

"Cannot insert explicit value for identity column in table 'Bar' when IDENTITY_INSERT is set to OFF."

Identity insert is not turned off, I use it for all tables, except in this instance where EF seems to be turning it off based on my definition of these tables.

"I also could add a nullable BarId to Foo if that would enable me to do what I want (but I would prefer not to)" When the right answer is something that you would prefer not to do, you should provide a strong justification as to why this is not appropriate in your case. Why don't you want to do this? — spender, Sep 22 '16 at 15:17
So there must be a foreign key in your database schema that allows null? — spender, Sep 22 '16 at 15:23
Not at the minute. Up until now, it was ok to treat Foo/Bar as a one to many relationship. But I could add whatever is needed... — Beakie, Sep 22 '16 at 15:32
What is *not working*? Your current code is exactly how EF expects a 1 to 0..1 relationship to be coded, including your `OnModelCreating()`. — Erik Philips, Sep 22 '16 at 15:35

Erik Philips · Answer 1 · 2016-09-22T15:50:41.177

0

So your real problem is

Cannot insert explicit value for identity column in table 'Bar' when IDENTITY_INSERT is set to OFF

First off this error message means: your are trying to insert a value into an identity column when it is currently configured to not allow that action (off).

Entity Framework is probably trying to insert zero because you have not told EF about the Identity Column; Which means you need to explicitly tell EF that your this column is generating your Ids from the database...

protected override void OnModelCreating(System.Data.Entity.DbModelBuilder modelBuilder)
{
    base.OnModelCreating(modelBuilder);

    modelBuilder.Entity<Foo>()
                .HasOptional(cpi => cpi.Bar)
                .WithRequired(api => api.Foo);

    // needed
    modelBuilder.Entity<Bar>()
      .HasKey(b => b.Id)
      .Property(b => b.Id)
      .HasDatabaseGeneratedOption(DatabaseGeneratedOption.Identity);
}

edited Sep 22 '16 at 15:50

answered Sep 22 '16 at 15:48

Erik Philips

53,428
11
128
150

It was inserting using identity into that column perfectly correctly before I tried the one to one relationship version of the table definition. I wasn't defining it explicitly before. – Beakie Sep 22 '16 at 15:50
I'm telling you how to solve this specific problem, if it worked before as some other relationship, that's fine, but this error message has a solution for this relationship. – Erik Philips Sep 22 '16 at 15:52
I now get..... A dependent property in a ReferentialConstraint is mapped to a store-generated column. Column: Id – Beakie Sep 22 '16 at 16:19
I dont understand why an annotation of Key didn't achieve this? – Beakie Sep 22 '16 at 16:19

score 0 · Answer 2 · answered Sep 22 '16 at 15:50

0

Shall we try with DataAnnotations ?

public class Foo
{
    public int Id { get; set; }

    public string  Name { get; set; }

    public virtual Bar Bar { get; set; }
}


public class Bar
{

    [Key, ForeignKey("Foo")]
    public override int Id {get;set;}

    public string Name { get; set; }

    public virtual Foo Foo { get; set; }
}

answered Sep 22 '16 at 15:50

Sampath

63,341
64
307
441

My apologies, the tables ARE defined with key, but in making my Foo/Bar example, I didn't include those annotations. – Beakie Sep 22 '16 at 15:54
Added them now, see above. – Beakie Sep 22 '16 at 15:55
do you need `1:1` no ? why above my one is not working for you ? – Sampath Sep 22 '16 at 15:56
Where is your FooId? – Beakie Sep 22 '16 at 15:59
if you need `1:0:1` relationship then you don't need it.both tables have one `PK` value. `Foo` is a `principal` end. `Bar` is `dependant` end.try this. – Sampath Sep 22 '16 at 16:01
I don't see where your FooId is. I need FooId for existing code! – Beakie Sep 22 '16 at 16:07
no need.you cannot built a `1:0:1` relationship like that.that is by design.you have an error due to your mysterious `FooId`.try my post. – Sampath Sep 22 '16 at 16:09
any feedback for this ? if it's not working please tell me. – Sampath Sep 25 '16 at 11:29

score 0 · Answer 3 · answered Sep 22 '16 at 16:02

Objects are nullable, so the relationship being optional doesn't require you to do anything different than your .HasOptional() declaration.

However, you're missing Foreign Keys with your relationships.

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

    public int Name { get; set; }

    public int BarId { get; set; } // Add this!

    [ForeignKey(nameof(BarId))] // Add this!
    public virtual Bar Bar { get; set; }
}

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

    public int Name { get; set; }

    public int FooId { get; set; }

    [ForeignKey(nameof(FooId))] // Add this!
    public virtual Foo Foo { get; set; }
}

One to zero...one relationship

3 Answers3