如何用Entity Core向关系型数据库添加无重复关联数据？

Great question! EF Core doesn’t include a built-in AddIfNotExists or direct equivalent to EF6’s AddOrUpdate, but we can implement a clean, efficient solution that meets all your requirements: avoiding duplicate clients/vehicles, reusing existing entities for associations, and handling intra-file duplicates without hitting the database repeatedly.

Core Approach #

We’ll use in-memory caching to track clients/vehicles we’ve already processed in the current CSV batch, plus database checks for entities that might already exist outside the batch. This balances performance (minimizing DB calls) and correctness (ensuring we don’t create duplicates).

Modified Code Implementation #

Here’s how to adjust your LoadCsv method to handle duplicates properly:

public void LoadCsv(string path) 
{ 
    var lines = File.ReadAllLines(path).Where(x => !string.IsNullOrWhiteSpace(x)); 
    var now = DateTime.Now; 
    var count = 0; 

    // In-memory caches to track processed/existing entities (avoids duplicate DB checks and intra-file duplicates)
    var existingClients = new Dictionary<string, ClientDetails>(); // Key: Client's unique identifier (e.g., record[0])
    var existingVehicles = new Dictionary<string, ClientVehicle>(); // Key: Vehicle license plate (unique business key)

    foreach (var line in lines) 
    { 
        if (count > 0) 
        { 
            var record = line.Split('|').ToList(); 
            var clientUniqueId = record[0]; // Adjust index to match your CSV's client ID field
            var vehicleLicensePlate = record[2]; // Adjust index to match your CSV's license plate field

            // Step 1: Get or create ClientDetails (no duplicates)
            if (!existingClients.TryGetValue(clientUniqueId, out var client))
            {
                // Check if client already exists in the database
                client = _context.ClientDetails.FirstOrDefault(c => c.Id == clientUniqueId);
                
                // If not found, create new client and add to context
                if (client == null)
                {
                    client = AddClientDetails(record, now);
                    _context.Add(client);
                }
                
                // Cache the client for future lines in the batch
                existingClients[clientUniqueId] = client;
            }

            // Step 2: Get or create ClientVehicle (no duplicates, associate with existing client)
            if (!existingVehicles.TryGetValue(vehicleLicensePlate, out var vehicle))
            {
                // Check if vehicle already exists in the database
                vehicle = _context.ClientVehicles.FirstOrDefault(v => v.LicensePlate == vehicleLicensePlate);
                
                // If not found, create new vehicle and link to the client
                if (vehicle == null)
                {
                    vehicle = AddClientVehicle(record, now);
                    vehicle.ClientDetails = client; // Use navigation property for safe association
                    _context.Add(vehicle);
                }
                
                // Cache the vehicle for future lines in the batch
                existingVehicles[vehicleLicensePlate] = vehicle;
            }

            // Step 3: Create and associate ClientOrder (always add, no duplicate check needed per your requirements)
            var order = AddClientOrder(record, now);
            vehicle.ClientOrders.Add(order);
            // No need to explicitly add the order to the context—EF Core tracks associated entities automatically
        } 
        count++; 
    } 

    // Save all changes in one batch
    _context.SaveChanges(); 
}

Key Details Explained #

1. In-Memory Caching: The existingClients and existingVehicles dictionaries ensure we only process each unique client/vehicle once per CSV batch, avoiding redundant database queries and duplicate entity creation.
2. Database Checks: For each entity, we first check the cache—if not found, we query the database to see if it already exists. Only if it’s new do we create and add it to the context.
3. Safe Associations: Instead of manually setting foreign key IDs, we use EF Core navigation properties (vehicle.ClientDetails = client) to ensure associations are correctly tracked and avoid foreign key constraint errors.
4. Order Handling: Orders are always created and linked to the relevant vehicle, regardless of whether the client/vehicle was new or existing—fulfilling your requirement to never skip order creation.

Bonus: EF Core 7+ Upsert Option (If Needed) #

If you ever need to update existing entities instead of just skipping them, EF Core 7 introduced ExecuteUpdate and ExecuteDelete which can be used to build an upsert pattern. For example:

// Upsert client (update if exists, insert if not)
_context.ClientDetails
    .Where(c => c.Id == clientUniqueId)
    .ExecuteUpdate(setters => setters
        .SetProperty(c => c.LastUpdated, now)
        // Add other properties to update as needed
    );

if (_context.ChangeTracker.Entries().All(e => e.Entity.Id != clientUniqueId))
{
    var newClient = AddClientDetails(record, now);
    _context.Add(newClient);
}

This is optional—your original requirement only calls for skipping duplicates, so the first solution is more appropriate.

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