Nrthugu

Target Framework: .Net Core 2.1

EntityFrameworkCore: 2.1.4

I've encountered some behaviour I don't understand when calling SaveChanges(). As the title says: I'm having valid GUIDs that have a Foreign Key relation with the same table being replaced with NULLs when inserting new records.

This is only happening for a specific column though, and only when EF generates the SQL for the changes made in the context. I can insert the same values fine inside SSMS.

For the purposes of demonstration I have created a minimal repository on GitHub that you can use to recreate the exact problem I'm encountering, but in a nutshell if you have an entity like this:

public class Account

{

    [Key]

    public Guid AccountId { get; set; }



    #region Audit



    public Guid? AddedByAccountId { get; set; }



    public DateTime AddedOnUtc { get; set; }



    public Guid? ModifiedByAccountId { get; set; }



    public DateTime ModifiedOnUtc { get; set; }



    #endregion Audit



    #region Navigation Properties



    [ForeignKey(nameof(AddedByAccountId))]

    public virtual Account AddedByAccount { get; set; }



    [ForeignKey(nameof(ModifiedByAccountId))]

    public virtual Account ModifiedByAccount { get; set; }



    #endregion Navigation Properties

}

And a derived DbContext implementation like this:

public class EntityFrameworkDbContext : DbContext

{

    public EntityFrameworkDbContext(DbContextOptions<EntityFrameworkDbContext> options)

        : base(options)

    {

    }



    public DbSet<Account> Accounts { get; set; }



    protected override void OnModelCreating(ModelBuilder modelBuilder)

    {

        modelBuilder.Entity<Account>(entity =>

        {

            // For some reason this self referencing key generates a unique

            // constraint in the migration script if we don't set this here.



            // "ModifiedByAccountId" does not have this issue! I suspect this might 

            // the root cause of the NULL data issue.

            entity.HasIndex(e => e.AddedByAccountId).IsUnique(false);

        });

    }

}

Inserting anything into the context should yield the same results. For example:

private void InitialiseDatabase()

{

    var systemAccount = new Account

    {

        AccountId = Guid.Parse("35c38df0-a959-4232-aadd-40db2260f557"),

        AddedByAccountId = null,

        AddedOnUtc = DateTime.UtcNow,

        ModifiedByAccountId = null,

        ModifiedOnUtc = DateTime.UtcNow

    };



    var otherAccounts = new List<Account>

    {

        new Account

        {

            AccountId = Guid.Parse("015b76fc-2833-45d9-85a7-ab1c389c1c11"),

            AddedByAccountId = Guid.Parse("35c38df0-a959-4232-aadd-40db2260f557"),

            AddedOnUtc = DateTime.UtcNow,

            ModifiedByAccountId = Guid.Parse("35c38df0-a959-4232-aadd-40db2260f557"),

            ModifiedOnUtc = DateTime.UtcNow

        },

        new Account

        {

            AccountId = Guid.Parse("538ee0dd-531a-41c6-8414-0769ec5990d8"),

            AddedByAccountId = Guid.Parse("35c38df0-a959-4232-aadd-40db2260f557"),

            AddedOnUtc = DateTime.UtcNow,

            ModifiedByAccountId = Guid.Parse("35c38df0-a959-4232-aadd-40db2260f557"),

            ModifiedOnUtc = DateTime.UtcNow

        },

        new Account

        {

            AccountId = Guid.Parse("8288d9ac-fbce-417e-89ef-82266b284b78"),

            AddedByAccountId = Guid.Parse("35c38df0-a959-4232-aadd-40db2260f557"),

            AddedOnUtc = DateTime.UtcNow,

            ModifiedByAccountId = Guid.Parse("35c38df0-a959-4232-aadd-40db2260f557"),

            ModifiedOnUtc = DateTime.UtcNow

        },

        new Account

        {

            AccountId = Guid.Parse("4bcfe9f8-e4a5-49f0-b6ee-44871632a903"),

            AddedByAccountId = Guid.Parse("35c38df0-a959-4232-aadd-40db2260f557"),

            AddedOnUtc = DateTime.UtcNow,

            ModifiedByAccountId = Guid.Parse("35c38df0-a959-4232-aadd-40db2260f557"),

            ModifiedOnUtc = DateTime.UtcNow

        }

    };



    _dbContext.Add(systemAccount);

    _dbContext.AddRange(otherAccounts);



    try

    {

        _dbContext.SaveChanges();

    }

    catch (Exception ex)

    {

        // Just checking to see if anything was being raised.

    }            

}

Will generate the following SQL (captured using SQL Server Profiler and formatted for readability):

exec sp_executesql N'SET NOCOUNT ON;



INSERT INTO [Accounts] 

(

    [AccountId], 

    [AddedByAccountId], 

    [AddedOnUtc], 

    [ModifiedByAccountId], 

    [ModifiedOnUtc]

)

VALUES 

(

    @p5, 

    @p6, 

    @p7, 

    @p8, 

    @p9

),

(

    @p10, 

    @p11, 

    @p12, 

    @p13, 

    @p14

),

(

    @p15, 

    @p16, 

    @p17, 

    @p18, 

    @p19

),

(

    @p20, 

    @p21, 

    @p22, 

    @p23, 

    @p24

);

',N'@p5 uniqueidentifier,

@p6 uniqueidentifier,

@p7 datetime2(7),

@p8 uniqueidentifier,

@p9 datetime2(7),

@p10 uniqueidentifier,

@p11 uniqueidentifier,

@p12 datetime2(7),

@p13 uniqueidentifier,

@p14 datetime2(7),

@p15 uniqueidentifier,

@p16 uniqueidentifier,

@p17 datetime2(7),

@p18 uniqueidentifier,

@p19 datetime2(7),

@p20 uniqueidentifier,

@p21 uniqueidentifier,

@p22 datetime2(7),

@p23 uniqueidentifier,

@p24 datetime2(7)',

@p5='015B76FC-2833-45D9-85A7-AB1C389C1C11',

@p6=NULL,

@p7='2018-11-10 14:29:25.5363017',

@p8='35C38DF0-A959-4232-AADD-40DB2260F557',

@p9='2018-11-10 14:29:25.5363022',

@p10='538EE0DD-531A-41C6-8414-0769EC5990D8',

@p11=NULL,

@p12='2018-11-10 14:29:25.5363031',

@p13='35C38DF0-A959-4232-AADD-40DB2260F557',

@p14='2018-11-10 14:29:25.5363034',

@p15='8288D9AC-FBCE-417E-89EF-82266B284B78',

@p16=NULL,

@p17='2018-11-10 14:29:25.5363039',

@p18='35C38DF0-A959-4232-AADD-40DB2260F557',

@p19='2018-11-10 14:29:25.5363042',

@p20='4BCFE9F8-E4A5-49F0-B6EE-44871632A903',

@p21='35C38DF0-A959-4232-AADD-40DB2260F557',

@p22='2018-11-10 14:29:25.5363047',

@p23='35C38DF0-A959-4232-AADD-40DB2260F557',

@p24='2018-11-10 14:29:25.5363047'

I suspect that this issue has its roots in the "IsUnique(false") line of code I highlighted in the second snippet as this only happens for the AddedByAccountId property/column. However, if I don't include this line then the migration script created will add on a unique constraint (which I don't want).

Has anyone encountered issues with either constraints being created when they shouldn't or (more importantly) NULL values replacing actual data on insert?

Cheers.

// For some reason this self referencing key generates a unique

// constraint in the migration script if we don't set this here.

// "ModifiedByAccountId" does not have this issue! I suspect this might

// the root cause of the NULL data issue.

You suspect it right. EF Core is confused from the 2 self referencing navigation properties and wrongly decides (could be a bug) that they represent a single One-to-one relationship:

One to one relationships have a reference navigation property on both sides. They follow the same conventions as one-to-many relationships, but a unique index is introduced on the foreign key property to ensure only one dependent is related to each principal.

Of course what you want is two one-to-many relationships, so rather than fixing the index (which won't help as you already saw), just map them explicitly:

modelBuilder.Entity<Account>().HasOne(e => e.AddedByAccount).WithMany();

modelBuilder.Entity<Account>().HasOne(e => e.ModifiedByAccount).WithMany();