Foreign Key Constraint Violation Issue with Bogus and EF Core

[makinibryan]

Version Information

Software	Version(s)
Bogus NuGet Package	34.0.2
.NET Core?	7
.NET Full Framework?
Windows OS?	windows 11
Linux OS?
Visual Studio?	2022

What locale are you using with Bogus? en_KE

What's the problem?

Description:

I'm currently working on a project where I need to generate fake employee data and store it in a database using Entity Framework Core. I've been using the Bogus library for generating the fake data. However, I've run into a problem, and I could use some guidance on how to resolve it.

The Problem:

I'm encountering a "SqlException" with a message that states, "The MERGE statement conflicted with the FOREIGN KEY constraint." This error indicates that there's an issue with how the foreign keys are being handled when inserting data into the database.

One unusual aspect of this problem is that the foreign key values are being generated as zero. Normally, foreign keys should reference existing records in the related table, and zero is not a valid reference to any record.

Here are some specific points related to the problem:

Data Generation with Bogus: I suspect that the Bogus library might be generating data for foreign key columns in a way that results in zero values or incorrect references.

Data Model and Relationships: In my data model, I have an "Employee" class and a related "JobInfo" class, which has a foreign key relationship to the "Employee" class. I'm concerned that this relationship might not be configured correctly.

Configuration or Seed Data: I'm using Entity Framework Core's migrations or seed data functionality to populate the database with fake data. There could be issues with how I'm configuring or populating the database with valid foreign key relationships.

What possible solutions have you considered?

Initially, I generated the data using a single instance of Faker. However, I decided to separate the employee and job info fake generators, thinking that this separation might help resolve the issue with foreign key generation. Despite this change, the problem persists.

Do you have sample code to show what you're trying to do?

```

public List GenerateEmployees(int count)
{
var faker = new Faker()
.RuleFor(f => f.FirstName, g => g.Person.FirstName)
.RuleFor(l => l.LastName, m => m.Person.LastName)
.RuleFor(e => e.Email, f=> f.Person.Email)
.RuleFor(p => p.PassportNumber, GenerateRandomPassportNumber) // Directly referenced method
.RuleFor(d => d.DateOfBirth, e => e.Person.DateOfBirth)
.RuleFor(h=> h.HomeAddress, i => i.Person.Address.City)
.RuleFor(p => p.ProfileImage, o=> o.Person.Avatar)
.RuleFor(g => g.Gender, h => h.Person.Gender.ToString())

        .GenerateLazy(count)
        .ToList();

    return faker;
}
private string GenerateRandomPassportNumber()
{
    const string characters = "ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789";
    Random random = new Random();
    string passportNumber = new string(Enumerable.Repeat(characters, 8)
        .Select(s => s[random.Next(s.Length)]).ToArray());
    return passportNumber;
}

}

public class JobInfoGenerator : IJobInfoGenerator
{
private readonly IConfiguration _configuration;

public JobInfoGenerator(IConfiguration configuration)
{
    _configuration = configuration;
}

public List<JobInfo> GenerateJobInfo(int count)
{
    var departments = _configuration.GetSection("Departments").Get<string[]>();
    var titles = _configuration.GetSection("Titles").Get<string[]>();

    var faker = new Faker<JobInfo>()
        .RuleFor(d => d.Department, e => e.PickRandom(departments))
        .RuleFor(t => t.Title, x => x.PickRandom(titles))
        .RuleFor(s => s.Salary, t => t.Finance.Amount())
        .RuleFor(l => l.Location, m => m.Person.Address.City)
        .RuleFor(h => h.PerformanceRating, i=> i.Random.Int(1,9))
        .GenerateLazy(count)
        .ToList();
    return faker;

}

}
here are my classes,
public class Employee
{
[Key]
public int EmployeeId { get; set; }
public string FirstName { get; set; }
public string LastName { get; set; }
public string Email { get; set; }
public string PassportNumber { get; set; }
public DateTime DateOfBirth { get; set; }
public JobInfo JobInfo { get; set; }
public string HomeAddress { get; set; }
public string ProfileImage { get; set; }
public string Gender { get; set; }
}

public class JobInfo : IJobInfo
{
[Key]
public int JobInfoId { get; set; }
public string Title { get; set; }
public string Department { get; set; }

 [Precision(18, 2)]
 public decimal Salary { get; set; }
 public string Location { get; set; }
 public DateTime HireDate { get; set; }
 public int PerformanceRating { get; set; }

 [ForeignKey(nameof(EmployeeId))]
 public int EmployeeId { get; set; }
 public Employee Employee { get; set; }

Ommitted unecessary methods

}
my db context
public class AppDbContext : DbContext
{
private readonly IEmployeeGenerator _employeeGenerator;
private readonly IJobInfoGenerator _jobInfoGenerator;

public AppDbContext(DbContextOptions<AppDbContext> options, 
    IEmployeeGenerator employeeGenerator , IJobInfoGenerator jobInfoGenerator
    
   ) : base(options)
{
    _employeeGenerator = employeeGenerator;
    _jobInfoGenerator = jobInfoGenerator;
}
public DbSet<Employee> Employees { get; set; }
public DbSet<JobInfo> JobInfos { get; set; }
public void SeedFakeData(int count)
{
    var fakeemployees = _employeeGenerator.GenerateEmployees(count);
    Employees.AddRange(fakeemployees);
    var fakeJobInfo = _jobInfoGenerator.GenerateJobInfo(count);
    JobInfos.AddRange(fakeJobInfo);
    SaveChanges();
}

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    base.OnModelCreating(modelBuilder);

    modelBuilder.Entity<Employee>()
        .HasOne(e => e.JobInfo) // Employee has one JobInfo
        .WithOne(j => j.Employee) // JobInfo has one Employee
        .HasForeignKey<JobInfo>(j => j.EmployeeId); // Define the foreign key relationship
}

}

_(Please be complete. Provide all code necessary to run your example in LINQPad.)_
_(The more complete code examples are, the more accurate answers will be.)_
_(https://www.linqpad.net)_