EF Core - Creating Unexpected Incomplete Rows in Sql Server LocalDb

Question

I have a new VS2022 solution with EFCore 6.0.1 available at: https://github.com/RobBowman/BillByTime

I've created a db context to represent the following:

I've used EFCore Power Tools from with VS2022 and compared the diagram it creates against my intended schema - they match!

Here is the db context C#:

public class BillByTimeContext : DbContext
    {
        public BillByTimeContext(DbContextOptions<BillByTimeContext> options) : base(options)
        {
            RelationalDatabaseCreator databaseCreator =
                        (RelationalDatabaseCreator)this.Database.GetService<IDatabaseCreator>();
            databaseCreator.EnsureCreated();
        }

        public DbSet<ClientOrg>? ClientOrg { get; set; }
        public DbSet<Contract>? Contract { get; set; }
        public DbSet<PurchaseOrder>? PurchaseOrder { get; set; }
        public DbSet<Tenant> Tenant { get; set; }
        public DbSet<TenantManager>? TenantManager { get; set; }
        public DbSet<Timesheet>? Timesheet { get; set; }
        public DbSet<TimesheetHistory>? TimesheetHistory { get; set; }
        public DbSet<Worker>? Worker { get; set; }

        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            modelBuilder.Entity<Tenant>()
                .HasIndex(x => x.Name)
                .IsUnique();

            modelBuilder.Entity<TenantManager>()
                .HasIndex(x => x.Email)
                .IsUnique();

            modelBuilder.Entity<Worker>()
                .HasMany(p => p.Contracts)
                .WithOne(t => t.Worker)
                .OnDelete(DeleteBehavior.NoAction);

            modelBuilder.Entity<Timesheet>()
                .HasMany(p => p.TimesheetHistories)
                .WithOne(t => t.Timesheet)
                .OnDelete(DeleteBehavior.NoAction);

            modelBuilder.Entity<Worker>()
                .HasMany(p => p.TimesheetHistories)
                .WithOne(t => t.Worker)
                .OnDelete(DeleteBehavior.NoAction);

            modelBuilder.Entity<Contract>()
                .Property(x => x.UnitCharge)
                .HasPrecision(10, 2);

            modelBuilder.Entity<PurchaseOrder>()
                .Property(x => x.Amount)
                .HasPrecision(10, 2);

            modelBuilder.Entity<Timesheet>()
                .Property(x => x.Monday)
                .HasPrecision(10, 2);
            modelBuilder.Entity<Timesheet>()
                .Property(x => x.Tuesday)
                .HasPrecision(10, 2);
            modelBuilder.Entity<Timesheet>()
                .Property(x => x.Wednesday)
                .HasPrecision(10, 2);
            modelBuilder.Entity<Timesheet>()
                .Property(x => x.Thursday)
                .HasPrecision(10, 2);
            modelBuilder.Entity<Timesheet>()
                .Property(x => x.Friday)
                .HasPrecision(10, 2);
            modelBuilder.Entity<Timesheet>()
                .Property(x => x.Saturday)
                .HasPrecision(10, 2);
            modelBuilder.Entity<Timesheet>()
                .Property(x => x.Sunday)
                .HasPrecision(10, 2);
        }

    }

I have an xunit test that calls the following SeedData method:

public static void SeedData(BillByTimeContext context)
        {

            context.Database.EnsureCreated();

            var timesheetHistory = new TimesheetHistory
            {
                Timestamp = new DateTime(2022, 1, 7, 14, 30, 0),
                StatusId = TimesheetStatus.PendingApproval
            };

            var timesheet = new Timesheet
            {
                Monday = 1,
                Tuesday = .5M,
                Wednesday = 1,
                Thursday = 0,
                Friday = 1,
                WeekCommencingMonday = new DateTime(2022, 01, 03),
                TimesheetHistories = new List<TimesheetHistory> { timesheetHistory },
            };

            var purchaseOrder = new PurchaseOrder
            {
                DateIssued = DateTime.Now,
                Amount = 5462.5M,
                Timesheets = new List<Timesheet> { timesheet }
            };

            var clientManager = new ClientManager
            {
                FirstName = "Paul",
                LastName = "Arndel",
                Email = "p.arndel@brc.com",
                SmsNumber = "867428764",
                TimesheetHistories = new List<TimesheetHistory> { timesheetHistory }
            };

            var contract = new Contract
            {
                UnitId = ContractUnits.Days,
                UnitCharge = 550,
                Timesheets = new List<Timesheet> { timesheet }
            };

            var clientOrg = new ClientOrg
            {
                Name = "BRC",
                PurchaseOrders = new List<PurchaseOrder> { purchaseOrder },
                ClientManagers = new List<ClientManager> { clientManager },
                Contracts = new List<Contract> { contract },
                Timesheets = new List<Timesheet> { timesheet }
            };

            var tenantManager = new TenantManager
            {
                FirstName = "Rob",
                LastName = "Bowman",
                Email = "rob@biztalkers.com",
                ClientOrgs = new List<ClientOrg> { clientOrg }
            };

            var worker = new Worker
            {
                FirstName = "Fabio",
                LastName = "Capello",
                Email = "f.cap@ital.com",
                Contracts = new List<Contract> { contract },
                TimesheetHistories= new List<TimesheetHistory> { timesheetHistory }
            };

            var tenant = context.Tenant.FirstOrDefault(x => x.Name == "BizTalkers");
            if (tenant == null)
            {
                context.Tenant.Add(new Tenant
                {
                    Name = "BizTalkers",
                    TenantManagers = new List<TenantManager> { tenantManager },
                    Workers = new List<Worker> { worker }
                }); ;
            }

            context.SaveChanges();

If I clear all data from the db and run the test, it gives the following error:

---- Microsoft.Data.SqlClient.SqlException : Cannot insert duplicate key row in object 'dbo.Tenant' with unique index 'IX_Tenant_Name'. The duplicate key value is ()

Can anyone please let me know why it's trying to create multiple records in the "Tenant" table?

Answer 1

Oh.. just noticed you github linked your entire project - which was useful and confirmed what I suspected:

//worker class
public Tenant Tenant { get; set; } = new();


//tenantmanager class
public Tenant Tenant { get; set; } = new();

Every time you make a new worker, it makes a new Tenant. EF will see this as an object that has to be saved. Your graph contains at least 1 worker (with 1 tenant with a blank name) and 1 tenantmanager (with 1 tenant with a blank name) so it looks like

{Tenant "BizTalkers"} --has-some--> [ {Worker "Fabio"} --has-one--> { Tenant "" } ]
           \
            `--has-some--> [ {TenantManager "Rob"} --has-one--> { Tenant "" } ]

At the moment I don't know why EFCPT has generated the entities that way (if it even did?) - mine doesn't. I'm not sure if it's an option you've toggled on, or a consequence of something in the DB end.. I'll update the answer if I find something, but for now your problem is, I believe, being caused by this. Either remove the = new() from these props, or set them null in your initializer, or build the graph the other way (set the Tenant you create to be the Tenant of the Worker you create, overwriting the new() Tenant it has, rather than setting the Worker you create to be one of the Workers of the Tenant you create)

Also, footnote, generally I would say "avoid wholesale replacing the entire list of child entities" - these collections are usually new'd in a constructor/class prop initializer as empty HashSets for a reason, and you'd simply Add to them rather than replacing them. If you replace them the change tracker might think you're deleting stuff and all sorts of crazy will happen