I have 3 entities: Person, User, and Location.

• A Person can have multiple Locations
• A User can have multiple Locations

My entities are set up as such:

public class Person
{
    public Guid Id { get; set; }
    public string Name { get; set; }
    public virtual IList<Location>? Locations { get; set; }
}

public class PersonEntityTypeConfiguration : IEntityTypeConfiguration<Person>
{
    public void Configure(EntityTypeBuilder<Person> builder)
    {
        builder
            .HasMany(b => b.Locations)
            .WithOne(b => b.Person)
            .HasForeignKey(b => b.PersonId)
            .IsRequired(false);
    }
}



public class User
{
    public Guid Id { get; set; }
    public Guid? Username { get; set; }
    public virtual IList<Location>? Locations { get; set; }
}

public class UserEntityTypeConfiguration : IEntityTypeConfiguration<User>
{
    public void Configure(EntityTypeBuilder<User> builder)
    {
        builder
            .HasMany(b => b.Locations)
            .WithOne(b => b.User)
            .HasForeignKey(b => b.UserId)
            .IsRequired(false);
    }
}



public class Location : UdbObject
{
    public Guid Id { get; set; }
    public string Description { get; set; }

    [ForeignKey(nameof(Person))]
    public Guid? PersonId { get; set; }
    public virtual Person? Person { get; set; }

    [ForeignKey(nameof(User))]
    public Guid? UserId { get; set; }
    public virtual User? User { get; set; }
}

Problem: I tried to insert a User into my SQL Server DB. This user has one Location object within its IList<Location>? Locations collection. I am getting the following error: The INSERT statement conflicted with the FOREIGN KEY constraint "FK_Locations_Persons_PersonId".

Here is where it is going wrong: Since Person.Id is a Guid? object, it automatically gets set to the equivalent of Guid.Empty before it is submitted to the DB. This causes the FK conflict, since the DB sees that there is no Person object in the DB with an Id set to the equivalent of Guid.Empty.

What I've tried: I saw that in previous version of EF Core, there is a .WithOptional() method that can be used in the Fluent API, but unfortunately this method is not recognized in EF Core 7. I tried to use the .IsRequired(false) method in the API, and it probably works from the DB standpoint, but my problem is that the GUID-based Id property is being set to Guid.Empty on the server before being passed to the DB, so .IsRequired(false) doesn't have the opportunity to do its job.

Am I missing something? Is there some other way to configure this?

Solution: I had a PersonDto that had a property public Guid Id { get; set; } instead of Guid? and it was being mapped back to the Person object with Guid.Empty loaded in it. Duh.

CodePudding user response：

Just make them M2M relationships and the foreign keys will all be in bridge tables. eg

public class Location : UdbObject
{
    public Guid Id { get; set; }
    public string Description { get; set; }

    public virtual ICollection<Person> Persons { get; } = new HashSet<Person>();

    public virtual ICollection<User> Users { get; } = new HashSet<User>();
}

CodePudding user response：

May this example could help you.

To set up a nullable foreign key on a Guid property using Entity Framework (EF) Core 7, you can use the following steps:

Define the entity classes that will participate in the relationship. For example, if you have a Course entity and a Student entity, you can define them as follows:

public class Course
{
    public Guid CourseId { get; set; }
    public string Name { get; set; }

    public Guid? StudentId { get; set; }
    public Student Student { get; set; }
}

public class Student
{
    public Guid StudentId { get; set; }
    public string Name { get; set; }
}

In the OnModelCreating method of your DbContext class, use the HasOne and WithOne methods to configure the relationship between the Course and Student entities. You can specify that the foreign key property, StudentId, is nullable by using the IsRequired(false) method:

modelBuilder.Entity<Course>()
    .HasOne(c => c.Student)
    .WithOne(s => s.Course)
    .HasForeignKey<Course>(c => c.StudentId)
    .IsRequired(false);

When you create a migration and update the database, EF Core will create the foreign key column StudentId in the Courses table with a nullable constraint. You can then use the StudentId property on the Course entity to set or get the foreign key value for a given Course. If the StudentId property is null, it means that the course does not have a student associated with it.