EF Core : define 1 to 1 AND 1 to many between the same tables

c# ef-core-2.1 ef-core-2.2 entity-framework-core

Question

I have two tables, for the sake of argument let's call them Profile and Session. A Profile has a currently active session (optional), and all sessions have to be linked to a profile (even those that are not active).

table definition

So what we have is a 1 to 1 relationship between Session and Profile on CurrentSessionId and also a 1 to many relationship between Profile and Session.

The question is how to build this in Entity Framework code-first?

I have my two entities

public class Profile
{
    public Guid Id { get; set; }

    public Session CurrentSession { get; set; }
}

public class Session
{
    public Guid Id { get; set; }
}

and two type configurations

public class ProfileTypeConfiguration : IEntityTypeConfiguration<Profile>
{
    public void Configure(EntityTypeBuilder<Profile> builder)
    {
        builder.ToTable("Profile");
        builder.HasKey(x => x.Id);

        builder.HasOne(x => x.CurrentSession)
            .WithOne()
            .HasForeignKey<Profile>("CurrentSessionId")
            .HasConstraintName("FK_CurrentSession_Profile")
            .IsRequired(false);
    }
}

public class SessionTypeConfiguration : IEntityTypeConfiguration<Session>
{
    public void Configure(EntityTypeBuilder<Session> builder)
    {
        builder.ToTable("Session");
        builder.HasKey(x => x.Id);

        builder.HasOne<Profile>()
            .WithMany()
            .HasForeignKey("ProfileId")
            .HasConstraintName("FK_Profile_CurrentSession")
            .IsRequired();
    }
}

All of this generates the database schema as expected with the correct columns and foreign keys. The problem comes when I try and insert a profile into the database

var profile = new Profile
{
    Id = Guid.NewGuid(),
    CurrentSession = new Session
    {
         Id = Guid.NewGuid()
    }
};

ctx.Profiles.Add(profile);
ctx.SaveChanges();

At which point I get a SqlException saying

The INSERT statement conflicted with the FOREIGN KEY constraint "FK_Profile_CurrentSession"

Looking in profiler the insert does not include the Profile record and it is only trying to insert the Session

exec sp_executesql N'SET NOCOUNT ON;
INSERT INTO [Session] ([Id], [ProfileId])
VALUES (@p0, @p1);
',N'@p0 uniqueidentifier,@p1 uniqueidentifier',@p0='AE96BD5F-FB0D-4E02-8E97-FBEFE4EF1382',@p1='00000000-0000-0000-0000-000000000000'

Is what I am trying to do possible? Should it be defined in another way?

  • EF Core version: 2.2.1
  • Database Provider: Microsoft.EntityFrameworkCore.SqlServer
  • Operating system: Windows 10
1
1
1/10/2019 8:54:06 PM

Popular Answer

I was able to get it working by explicitly defining the keys and then by creating the profile and session separately before setting the current session.

The entities

public class Profile
{
    public Guid Id { get; set; }
    public Guid? CurrentSessionId { get; set; }
    public Session CurrentSession { get; set; }
}

public class Session
{
    public Guid Id { get; set; }
    public Guid ProfileId { get; set; }
}

The type configuartion

public class ProfileTypeConfiguration : IEntityTypeConfiguration<Profile>
{
    public void Configure(EntityTypeBuilder<Profile> builder)
    {
        builder.ToTable("Profile");
        builder.HasKey(x => x.Id);

        builder.HasMany<Session>()
            .WithOne()
            .HasForeignKey(e => e.ProfileId)
            .HasConstraintName("FK_Profile_CurrentSession")
            .IsRequired();

        builder.HasOne(x => x.CurrentSession)
            .WithOne()
            .HasForeignKey<Profile>("CurrentSessionId")
            .HasConstraintName("FK_CurrentSession_Profile")
            .IsRequired(false);
    }
}

public class SessionTypeConfiguration : IEntityTypeConfiguration<Session>
{
    public void Configure(EntityTypeBuilder<Session> builder)
    {
        builder.ToTable("Session");
        builder.HasKey(x => x.Id);
    }
}

The program.

var profileId = Guid.NewGuid();
var sessionId = Guid.NewGuid();

var profile = new Profile
{
    Id = profileId,
};

var session = new Session
{
    Id = sessionId,
    ProfileId = profileId
};

ctx.Profiles.Add(profile);
ctx.Sessions.Add(session);
ctx.SaveChanges();

profile.CurrentSession = session;
ctx.SaveChanges();

If you try to apply the session directly to the profile, it creates a circular dependency, so you have to create them first and save. Then, set the current and save again.

0
1/10/2019 9:53:23 PM


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow