Efcore: How to insert values into a table with optional relationship in ef?

[embryologist]

Describe what is not working as expected.

I have those two entities, PatientRegistry and PatientAccount. The relationship between them is 1-0..1. I am trying to seed in my primary table PatientRegistry but I keep getting null errors for required fields in my optional table PatientAccount, How can I insert values in my primary table without the need to seed in my optional table as well?

Exception message:
> Unhandled Exception: Microsoft.EntityFrameworkCore.DbUpdateException:
> An error occurred while updating the entries. See the inner exception
> for details. ---> System.Data.SqlClient.SqlException: Cannot insert
> the value NULL into column 'Password', table
> 'ArtCoreDb.dbo.AspPatientsAccount'; column does not allow nulls.
> UPDATE fails.

Steps to reproduce

public class PatientRegistry {
            [DatabaseGenerated (DatabaseGeneratedOption.Identity)]
            [Display (Name = "Record Id")]
            public long RecordId { get; set; }

            [Key, DatabaseGenerated (DatabaseGeneratedOption.None)]
            [Display (Name = "Patient File Number")]
            public long PatientFileId { get; set; }

            [Required, StringLength (50)]
            [Display (Name = "First Name")]
            public string FirstName { get; set; }
            public virtual ICollection<PartnerRegistry> Partners { get; set; }
            public virtual PatientAccount PatientAccount { get; set; }
    }

    public class PatientAccount {
            [Key, DatabaseGenerated (DatabaseGeneratedOption.Identity)]
            public long RecordId { get; set; }
            [Required]
            public string Password { get; set; }
            [Required, StringLength (15)]
            public string MobileNo { get; set; }
            public bool IsConfirmedPhoneNumber { get; set; } = false;
            public bool IsConfirmedEmailAddress { get; set; } = false;
            public bool IsLocked { get; set; } = false;
            public int? FailedAttempts { get; set; }
            public DateTimeOffset? LastLoggedIn { get; set; }
            public DateTimeOffset DateCreated { get; set; }
            [Timestamp]
            public byte[] RowVersion { get; set; }
            public long? PatientFileId { get; set; }
            public virtual PatientRegistry PatientFile { get; set; }
        }

And my fluent API,

    protected override void OnModelCreating (ModelBuilder builder) {
            base.OnModelCreating (builder);
            builder.Entity<PatientRegistry> ()
                    .HasOne (a => a.PatientAccount)
                    .WithOne (b => b.PatientFile)
                    .HasForeignKey<PatientAccount> (c => c.PatientFileId)
                    .OnDelete (DeleteBehavior.Cascade);

    }

Further technical details

EF Core version: 2.0.2
Database Provider: (Microsoft.EntityFrameworkCore.SqlServer)
Operating system: Mac OS
IDE: (VS CODE)

[smitpatel]

Share the code you are using to seed the table or insert data into the table.

[embryologist]

@smitpatel

if (!context.PatientsRegistry.Any ()) {

    context.PatientsRegistry.AddRange (
        new PatientRegistry {
            PatientFileId = 1111,
                FirstName = "John"
        }
    );
    context.SaveChanges ();

}

works only if I add ,,

PatientAccount =
new PatientAccount {
    PatientFileId = 1111,
        CountryCodeId = context.Countries.Where (g => g.Name == "United States of America").SingleOrDefault ().Id,
        AreaCode = 424,
        Email = "[email protected]",
        MobileNo = "3244990",
        IsConfirmedEmailAddress = false,
        IsConfirmedPhoneNumber = false,
        IsLocked = false,
        Password = "213123",
},

in reality, I want to be able to insert in PatientsRegistry table without the need to insert into PatientAccount

[smitpatel]

If you are adding PatientRegistry with PatientAccount navigation set, EF will insert both the records in the database. Using Add (or AddRange) marks all reachable entities in the graph in added state, which will be inserted into database at SaveChanges. If you want to add only PatientRegistry without adding associated PatientAccount then use entity entry APIs for individual entities.

Further, the Password property is marked as required (hence non-nullable in the database). The exception message suggests that you are inserting new PatientAccount without password set. Which is not what is happening in your insert code you shared. Make sure you are only marking entities as added which you want to insert. And all the entities which are supposed to be inserted has required columns value provided (or generated)

[embryologist]

@smitpatel , thanks so much