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 = "[email protected]",
                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 = "[email protected]",
                ClientOrgs = new List<ClientOrg> { clientOrg }
            };

            var worker = new Worker
            {
                FirstName = "Fabio",
                LastName = "Capello",
                Email = "[email protected]",
                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?

Does the error message actually say The duplicate key value is ()? Looks like the Name is blank/null if so too... — Martin Smith
– Martin Smith, Commented Jan 5, 2022 at 9:07
@MartinSmith yes, seems it’s trying to insert a row in the Tenant table with an empty value for the Name column — Rob Bowman
– Rob Bowman, Commented Jan 5, 2022 at 9:09
Should you be seeding your data in the unit test ? Shouldnt the data be seeded in your first EF migration and then never again ? Otherwise you can only ever run this unit test once unless as you said, you clear all the data — Chris Holden
– Chris Holden, Commented Jan 5, 2022 at 9:37
Although it makes use of xunit, it's not a unit test and never run from the CI pipeline. It's just a handy way to check I've setup the db context to work with the db schema I'm after. — Rob Bowman
– Rob Bowman, Commented Jan 5, 2022 at 9:40
when you clear the data to restart are you dropping it all and creating it again ? or just doing a DELETE manually ? — Chris Holden
– Chris Holden, Commented Jan 5, 2022 at 9:42

Caius Jard · Accepted Answer · 2022-01-05 11:47:47Z

2

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

edited Jan 5, 2022 at 11:47

answered Jan 5, 2022 at 11:33

Caius Jard

75k6 gold badges61 silver badges95 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Rob Bowman Over a year ago

To clarify - EFCPT didn’t generate the entities, I only used it to generate a diagram. So, the fault of this issue was entirely made by me and my use of the =new(); for the navigation properties

Caius Jard Over a year ago

Phew, I was thinking I'd have to call ErikEJ in here! You can get EFCPT to generate them by the way, and a nice job it makes too. It shortens the dev cycle a little to pop open SSMS, make a new database diagram, draw the tables etc like you have your ERD there, drag the relationships etc etc, and then save to create the DB and tell EFCPT to Reverse Engineer it into a bunch of classes for you. It does all the setup (similar to Scaffold-DbContext) but with a few more tweaks and such possible

Rob Bowman Over a year ago

Thanks Caius, I only discovered EFCPT today but it looks like it is going to be a great help

Collectives™ on Stack Overflow

EF Core - Creating Unexpected Incomplete Rows in Sql Server LocalDb

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related