如何优化.NET EF Core批量插入以提升校园订餐应用的学生账号创建效率？

Great question! The core issue here is that your current code is making thousands of round-trips to the database (1000 pupils × 3 checks each = 3000 separate queries), which is why it's taking so long. To fix this, we need to minimize database calls, batch operations, and replace the "check-then-insert/update" pattern with bulk-friendly approaches.

Step 1: Batch Process Accounts (Avoid Per-Pupil Checks) #

Instead of querying for each email individually, we can fetch all existing accounts in one go, then create only the new ones in bulk:

// Extract all unique emails from the pupils list
var allPupilEmails = pupils.Select(p => p.Email).Distinct().ToList();

// Fetch all existing accounts with these emails in a single query
var existingAccounts = await _context.Accounts
    .Where(a => allPupilEmails.Contains(a.Email))
    .ToListAsync();

// Create a dictionary for fast email-to-AccountId lookups
var existingEmailLookup = existingAccounts.ToDictionary(a => a.Email);

// Generate new accounts for emails that don't exist
var newAccounts = pupils
    .Where(p => !existingEmailLookup.ContainsKey(p.Email))
    .Select(p => new Accounts
    {
        Email = p.Email,
        FirstTime = true,
        FirstTimePassword = password,
        Password = hashedPassword,
        Name = p.Name,
        SurName = p.SurName,
        EmailSend = false
    })
    .ToList();

// Bulk add new accounts and save to generate their AccountIds
await _context.Accounts.AddRangeAsync(newAccounts);
await _context.SaveChangesAsync();

// Combine existing and new accounts into a single lookup
var allAccountsLookup = existingAccounts.Concat(newAccounts)
    .ToDictionary(a => a.Email);

Step 2: Batch Process AccountsTypes (Many-to-Many Links) #

Apply the same bulk logic to the many-to-many association table:

// Generate all required (AccountId, TypeId) pairs
var requiredAccountTypePairs = pupils
    .Select(p => new { AccountId = allAccountsLookup[p.Email].AccountId, TypeId = type.TypeId })
    .Distinct()
    .ToList();

// Fetch existing links in one query
var existingAccountTypes = await _context.AccountsTypes
    .Where(at => requiredAccountTypePairs.Any(p => p.AccountId == at.AccountId && p.TypeId == at.TypeId))
    .ToListAsync();

// Create a hash set of existing pairs for fast existence checks
var existingPairs = existingAccountTypes.Select(at => (at.AccountId, at.TypeId)).ToHashSet();

// Generate new links for missing pairs
var newAccountTypes = requiredAccountTypePairs
    .Where(p => !existingPairs.Contains((p.AccountId, p.TypeId)))
    .Select(p => new AccountsTypes
    {
        AccountId = p.AccountId,
        TypeId = p.TypeId
    })
    .ToList();

// Bulk add new association links
await _context.AccountsTypes.AddRangeAsync(newAccountTypes);

Step 3: Bulk Upsert Pupils (Insert or Update) #

For the Pupils table, use EF Core 7+'s ExecuteUpdate to bulk update existing records, then insert only the new ones. This replaces the inefficient "check-then-modify" pattern:

// Prepare pupil data with parsed Guids (avoids repeated parsing in loops)
var pupilUpdates = pupils.Select(p => new
{
    AccountId = allAccountsLookup[p.Email].AccountId,
    ClassId = Guid.Parse(p.Class[0].Id),
    CodeId = Guid.Parse(p.Code[0].Id),
    ModifiedDate = DateTime.Now
}).ToList();

// Bulk update existing pupils to refresh ModifiedDate
await _context.Pupils
    .Where(p => pupilUpdates.Any(up => up.AccountId == p.AccountId))
    .ExecuteUpdateAsync(setters => setters
        .SetProperty(p => p.ModifiedDate, DateTime.Now));

// Fetch AccountIds of existing pupils to avoid duplicate inserts
var existingPupilAccountIds = await _context.Pupils
    .Where(p => pupilUpdates.Any(up => up.AccountId == p.AccountId))
    .Select(p => p.AccountId)
    .ToHashSetAsync();

// Generate new pupils for AccountIds that don't exist in the table
var newPupils = pupilUpdates
    .Where(up => !existingPupilAccountIds.Contains(up.AccountId))
    .Select(up => new Pupils
    {
        AccountId = up.AccountId,
        ClassId = up.ClassId,
        CodeId = up.CodeId,
        ModifiedDate = up.ModifiedDate
    })
    .ToList();

// Bulk add new pupils
await _context.Pupils.AddRangeAsync(newPupils);

// Save all remaining changes
await _context.SaveChangesAsync();

Step 4: Finalize Pupil AccountIds #

Update the original pupils list with their assigned AccountIds:

foreach (var pupil in pupils)
{
    pupil.AccountId = allAccountsLookup[pupil.Email].AccountId;
}

return pupils;

Key Optimizations Explained: #

• Reduced Database Round-Trips: From 3000+ queries to ~5-6 total queries, which delivers the biggest performance improvement.
• Bulk Operations: Using AddRangeAsync instead of looping AddAsync minimizes the overhead of individual insert commands.
• In-Memory Lookups: Hash sets and dictionaries make existence checks fast in memory instead of hitting the database repeatedly.
• Atomicity (Optional): Wrap the entire operation in a transaction to guarantee all changes succeed or fail together:

  using var transaction = await _context.Database.BeginTransactionAsync();
  // ... all the code above ...
  await transaction.CommitAsync();
  

Bonus: Extreme Performance for Large Datasets #

For 10k+ records, consider using EF Core bulk extension libraries like EFCore.BulkExtensions. These libraries generate optimized SQL (like MERGE statements) that outperform native EF Core for bulk inserts/updates, and support direct BulkUpsert operations to further simplify your code.

内容的提问来源于stack exchange，提问作者samsepi