HasRequired and WithOptional not generating one-to-one relationship

Question

I have, two very simple objects:

public class GenericObject
{
    public int Id { get; set; }
    public string description { get; set; }
    public User UserWhoGeneratedThisObject { get; set; }
}

public class User
{
    public int Id { get; set; }  
    public string Name { get; set; }
    public string Lastname { get; set; }
}

I am overriding the OnModelCreating function to declare the objects' relationships:

    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        modelBuilder.Entity<GenericObject>().HasRequired(u => u.UserWhoGeneratedThisObject)
                                   .WithOptional();
    }

Then, in my application, I do the following:

using (var ctx = new TestContext())
{
    GenericObject myObject = new GenericObject();
    myObject.description = "testobjectdescription";
    User usr = new User() { Name = "Test"};
    ctx.Users.Add(usr);
    //myObject.UserWhoGeneratedThisObject = usr;
    ctx.GenericObjects.Add(myObject);
    ctx.SaveChanges();
}

Shouldn't I be hitting an exception when I am adding the myObject to the database, since I haven't assigned it a User object? (The commented out line) I expected to see an exception in this case, but the code works OK. To make matters worse, if I add the following two lines after ctx.SaveChanges():

var u = ctx.GenericObjects.Find(1);
System.Console.WriteLine(u.ToString());

I see that the variable u which is a GenericObjects actually points to the user that I created in the database, even though I didn't assign the user to the GenericObject.

Answer 1

you should read: Difference between .WithMany() and .WithOptional()?

the code:

using (var ctx = new Tests6Context()) {
    GenericObject myObject = new GenericObject();
    myObject.description = "testobjectdescription";
    User usr = new User() { Name = "Test" };
    ctx.Users.Add(usr);
    //myObject.UserWhoGeneratedThisObject = usr;
    ctx.GenericObjects.Add(myObject);


    myObject = new GenericObject();
    myObject.description = "testobjectdescription 2";
    usr = new User() { Name = "Test 2" };
    ctx.Users.Add(usr);
    //myObject.UserWhoGeneratedThisObject = usr;
    ctx.GenericObjects.Add(myObject);

    ctx.SaveChanges();
}

throws:

System.Data.Entity.Infrastructure.DbUpdateException: Unable to determine the principal end of the 'ef6tests.GenericObject_UserWhoGeneratedThisObject' relationship. Multiple added entities may have the same primary key.

In simple case one new User and one new GenericObject EF infers the only relation possible between two entities in the same state. In other cases he throws.

Answer 2

TL;DR

This behavior is not specific for one-to-one relationship, one-to-many behaves exactly the same way.

If you want to make EF throw an exception with your current code then map separate foreign key for GenericObject

modelBuilder
    .Entity<GenericObject>()
    .HasRequired(u => u.UserWhoGeneratedThisObject)
    .WithOptional()
    .Map(config =>
    {
        config.MapKey("UserId");
    });

Actual answer

Actually one-to-many relationship suffers from exactly the same problem. Let's do some tests.

One-to-one

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

    public string Description { get; set; }

    public UserObject UserWhoGeneratedThisObject { get; set; }
}

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

    public string Name { get; set; }
}

Configuration

modelBuilder
    .Entity<GenericObject>()
    .HasRequired(u => u.UserWhoGeneratedThisObject)
    .WithOptional();

In this case GenericObject.Id is a primary key and a foreign key references UserObject entity at the same time.

Test 1. Create only GenericObject

var context = new AppContext();

GenericObject myObject = new GenericObject
{
    Description = "some description"
};

context.GenericObjects.Add(myObject);
context.SaveChanges();

Executed query

INSERT [dbo].[GenericObjects]([Id], [Description])
VALUES (@0, @1)

-- @0: '0' (Type = Int32)

-- @1: 'some description' (Type = String, Size = -1)

Exception

The INSERT statement conflicted with the FOREIGN KEY constraint "FK_dbo.GenericObjects_dbo.UserObjects_Id". The conflict occurred in database "TestProject", table "dbo.UserObjects", column 'Id'. The statement has been terminated.

Since GenericObject is the only entity EF executes insert query and it fails because there is no UserObject with Id equals 0 in database.

Test 2. Create 1 UserObject and a GenericObject

var context = new AppContext();

GenericObject myObject = new GenericObject
{
    Description = "some description"
};

UserObject user = new UserObject
{
    Name = "user"
};

context.UserObjects.Add(user);
context.GenericObjects.Add(myObject);
context.SaveChanges();

Executed queries

INSERT [dbo].[UserObjects]([Name])
VALUES (@0)
SELECT [Id]
FROM [dbo].[UserObjects]
WHERE @@ROWCOUNT > 0 AND [Id] = scope_identity()

-- @0: 'user' (Type = String, Size = -1)

INSERT [dbo].[GenericObjects]([Id], [Description])
VALUES (@0, @1)

-- @0: '10' (Type = Int32)

-- @1: 'some description' (Type = String, Size = -1)

Now context contains UserObject (Id = 0) and GenericObject (Id = 0). EF considers that GenericObject references to the UserObject because its foreign key equals 0 as well as UserObject primary key equals 0. So at first EF inserts UserObject as a principal and because it consider GenericObject dependent on that user it takes returned UserObject.Id and performs second insert with it and eveything is fine.

Test 3. Create 2 UserObject and a GenericObject

var context = new AppContext();

GenericObject myObject = new GenericObject
{
    Description = "some description"
};

UserObject user = new UserObject
{
    Name = "user"
};
UserObject user2 = new UserObject
{
    Name = "user"
};

context.UserObjects.Add(user);
context.UserObjects.Add(user2);
context.GenericObjects.Add(myObject);
context.SaveChanges();

Exception

'System.Data.Entity.Infrastructure.DbUpdateException' occurred in EntityFramework.dll

Additional information: Unable to determine the principal end of the 'TestConsole.Data.GenericObject_UserWhoGeneratedThisObject' relationship. Multiple added entities may have the same primary key.

EF sees that there are 2 UserObject in context with Id equals 0 and GenericObject.Id equals 0 as well, so the framework just unable to definitely connect entities because there are multiple possible options.

One-to-many

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

    public string Description { get; set; }

    public int UserId { get; set; } //this property is essential to illistrate the problem

    public UserObject UserWhoGeneratedThisObject { get; set; }
}

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

    public string Name { get; set; }
}

Configuration

modelBuilder
    .Entity<GenericObject>()
    .HasRequired(o => o.UserWhoGeneratedThisObject)
    .WithMany()
    .HasForeignKey(o => o.UserId);

In this case GenericObject has separate UserId as a foreign key referencing UserObject entity.

Test 1. Create only GenericObject

Same code as in one-to-one. It executes the same query and yields the same exception. Reason is the same.

Test 2. Create 1 UserObject and a GenericObject

Same code as in one-to-one.

Executed queries

INSERT [dbo].[UserObjects]([Name])
VALUES (@0)
SELECT [Id]
FROM [dbo].[UserObjects]
WHERE @@ROWCOUNT > 0 AND [Id] = scope_identity()

-- @0: 'user' (Type = String, Size = -1)

-- Executing at 14.03.2019 18:52:35 +02:00

INSERT [dbo].[GenericObjects]([Description], [UserId])
VALUES (@0, @1)
SELECT [Id]
FROM [dbo].[GenericObjects]
WHERE @@ROWCOUNT > 0 AND [Id] = scope_identity()

-- @0: 'some description' (Type = String, Size = -1)

-- @1: '3' (Type = Int32)

Executed queries are pretty similar to one-to-one test 2. The only difference is that now GenericObject has separate UserId foreign key. Reasoning is pretty the same. Context contains UserObject entity with Id equal 0 and GenericObject with UserId equals now so EF considers them connected and performs insert of UserObject then it takes UserObject.Id and performs second insert with it.

Test 3. Create 2 UserObject and a GenericObject

Same code as in one-to-one. It executes the same query and yields the same exception. Reason is the same.

As you can see from these tests the problem is not specific to one-to-one relationship.

But what if we don't add UserId to GenericObject? In this case EF will generate UserWhoGeneratedThisObject_Id foreign key for us and now there is foreign key in database but no property mapped to it. In this case every single test will immediately throw the following exception

Entities in 'AppContext.GenericObjects' participate in the 'GenericObject_UserWhoGeneratedThisObject' relationship. 0 related 'GenericObject_UserWhoGeneratedThisObject_Target' were found. 1 'GenericObject_UserWhoGeneratedThisObject_Target' is expected.

Why this happens? Now EF unable to determine whether GenericObject and UserObject are connected because there is no foreign key property on GenericObject. In this case EF can rely only on navigation property UserWhoGeneratedThisObject which is null and therefore an exception is raised.

It means if you can achieve this situation for one-to-one when there is a foreign key in database but no property is mapped to it EF will throw the same exception for the code in your question. It's easy to accomplish by updating configuration

modelBuilder
    .Entity<GenericObject>()
    .HasRequired(u => u.UserWhoGeneratedThisObject)
    .WithOptional()
    .Map(config =>
    {
        config.MapKey("UserId");
    });

The Map method tells EF to create separate UserId foreign key in GenericObject. With this change the code in your question will throw an exception

using (var ctx = new TestContext())
{
    GenericObject myObject = new GenericObject();
    myObject.description = "testobjectdescription";
    User usr = new User() { Name = "Test"};
    ctx.Users.Add(usr);
    //myObject.UserWhoGeneratedThisObject = usr;
    ctx.GenericObjects.Add(myObject);
    ctx.SaveChanges();
}

Entities in 'AppContext.GenericObjects' participate in the 'GenericObject_UserWhoGeneratedThisObject' relationship. 0 related 'GenericObject_UserWhoGeneratedThisObject_Target' were found. 1 'GenericObject_UserWhoGeneratedThisObject_Target' is expected.