Bulk insert with child entities using the EF extension saving duplicate records

Question

I'm doing a bulk insert of an entity with related entities and I need to avoid saving duplicate records. Unfortunately, all entities are getting saved even if they already exist. I'm using the EF extensions library here.

This question is a possible duplicate of this one but its comments have not solved my problem.

Below is my bulkInsert code:

 await _context.BulkInsertAsync(Registrations, options: o =>
    { 
        o.IncludeGraph = true;
        o.IncludeGraphOperationBuilder = operation =>
        {
            switch (operation)
            {
                case BulkOperation<School> School:
                    School.InsertIfNotExists = true;
                    School.ColumnPrimaryKeyExpression = c => new { c.SchoolId, c.SchoolIdentifier };
                    School.AllowDuplicateKeys = false;
                    break;
                case BulkOperation<Student> Student:
                    Student.InsertIfNotExists = true;
                    Student.ColumnPrimaryKeyExpression = c => new{c.StudentId, c.StudentIdentifier};
                    Student.AllowDuplicateKeys = false;
                    break;
                case BulkOperation<Registration> Registration:
                    Registration.InsertIfNotExists = true;
                    Registration.ColumnPrimaryKeyExpression = m => new { m.SchoolId, m.StudentId };
                    Registration.AllowDuplicateKeys = false;
                    break;
            }
        };
    });

And the entity definitions:

public class Registration
{
    [Key]
    public int RegistrationId { get; set; }
    public DateTime StartDate { get; set; }
    public DateTime EndDate { get; set; }

    public int StudentId { get; set; }
    public Student Student { get; set; }

    public int SchoolId { get; set; }
    public School School { get; set; } 
}
    
public class School 
{
    [Key]
    public int SchoolId { get; set; }
    public string Name { get; set; }
    public string Email { get; set; }
    public int SchoolIdentifier { get; set; } 
}
    
public class Student
{
    [Key]
    public int StudentId { get; set; }

    public string FirstName { get; set; }
    public string LastName { get; set; }
    public string StudentIdentifier { get; set; } 
}

score 2 · Answer 1 · answered Feb 16 '22 at 16:28

So I fixed the issue by using the BulkMerge method. In addition, instead of saving related entities with the registration list, I saved them separately.

I also got a help from the creator of the library and he provided a code sample here. I'm putting the sample code below in case the link stops working.

// Entity Framework Extensions
// Doc: https://entityframework-extensions.net/bulk-savechanges

// @nuget: Microsoft.EntityFrameworkCore.SqlServer
// @nuget: Z.EntityFramework.Extensions.EFCore

using Microsoft.EntityFrameworkCore;
using System;
using System.Collections.Generic;
using System.Linq;
using Microsoft.Data.SqlClient;
using System.ComponentModel.DataAnnotations;
                    
public class Program
{
    public static void Main()
    {
        using (var context = new EntityContext())
        {
            context.Database.EnsureCreated();
        }
        
        using (var context = new EntityContext())
        {
            var students = new List<Student>();
            var schools = new List<School>();
            var registrations = new List<Registration>();
            
            var student1 = new Student() { FirstName = "Go_To_School_A", StudentIdentifier = "100100"};
            var student2 = new Student() { FirstName = "Go_To_School_B", StudentIdentifier = "100200"};
            var student3 = new Student() { FirstName = "Go_To_School_A_B", StudentIdentifier = "100300"};
            
            var school1 = new School() { Name = "School_A", SchoolIdentifier = 500100};
            var school2 = new School() { Name = "School_B", SchoolIdentifier = 500200};
            
            students.Add(student1);
            students.Add(student2);
            students.Add(student3);
            
            schools.Add(school1);
            schools.Add(school2);
            
            registrations.Add(new Registration() { School = school1, Student = student1 });
            registrations.Add(new Registration() { School = school2, Student = student2 });
            registrations.Add(new Registration() { School = school1, Student = student3 });
            registrations.Add(new Registration() { School = school2, Student = student3 });
            
            context.BulkMerge(students, options: o =>
            {
                o.ColumnPrimaryKeyExpression = x => new {x.StudentIdentifier};
            });
            context.BulkMerge(schools, options: o =>
            {
                o.ColumnPrimaryKeyExpression = x => new {x.SchoolIdentifier};
            });
            context.BulkMerge(schools);
            
            registrations.ForEach(x => {
                x.SchoolId = x.School.SchoolId;
                x.StudentId = x.Student.StudentId;
            });
            
            context.BulkMerge(registrations, options => {
                options.ColumnPrimaryKeyExpression = m => new { m.SchoolId, m.StudentId };
            });
            
            FiddleHelper.WriteTable("1 - Students", context.Students.AsNoTracking().ToList());
            FiddleHelper.WriteTable("2 - Schools", context.Schools.AsNoTracking().ToList());
            FiddleHelper.WriteTable("3 - Registrations", context.Registrations.AsNoTracking().ToList());
        }
    }
    
    public class EntityContext : DbContext
    {
        public EntityContext() 
        {
        }

        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            optionsBuilder.UseSqlServer(new SqlConnection(FiddleHelper.GetConnectionStringSqlServer()));

            base.OnConfiguring(optionsBuilder);
        }
        
        public DbSet<School> Schools { get; set; }
        public DbSet<Student> Students { get; set; }
        public DbSet<Registration> Registrations { get; set; }
    }

    public class Registration
    {
           [Key]
           public int RegistrationId { get; set; }
           public DateTime StartDate { get; set; }
           public DateTime EndDate { get; set; }

           public int StudentId { get; set; }
           public Student Student { get; set; }

           public int SchoolId { get; set; }
           public School School { get; set; } 
    }

    public class School 
    {
           [Key]
           public int SchoolId { get; set; }
           public string Name { get; set; }
           public string Email { get; set; }
           public int SchoolIdentifier { get; set; } 
    }

    public class Student
    {
           [Key]
           public int StudentId { get; set; }

           public string FirstName { get; set; }
           public string LastName { get; set; }
           public string StudentIdentifier { get; set; } 
    }
}

Bulk insert with child entities using the EF extension saving duplicate records

1 Answers1