I’m having an issue while trying to update a list of items having many to many relationship using Entity Framework Core.

I have for now two classes DeviceModel and DetectedAppModel with a many-to-many relationship :

public class DeviceModel
{
    public string Id { get; set; }
    public string? UserId { get; set; }
    public string? DeviceName { get; set; }
    public IList<DetectedAppModel> Apps { get; set; }

    public DeviceModel()
    {
         Apps = new List<DetectedAppModel>();
         // Users = new List<UserModel>();
         // Groups = new List<GroupModel>();
    }
}

public class DetectedAppModel
{
    [Required]
    public string id { get; set; }
    [Required]
    public string displayName { get; set; }
    public string? version { get; set; }
    public string? publisher { get; set; }
    public string? platform { get; set; }

    public IList<DeviceModel> Devices { get; set; }

    public DetectedAppModel(string _id, string _displayName) 
    {
        id = _id;
        displayName = _displayName;
        Devices = new List<DeviceModel>();
    }
}

I plan to periodically export a great number of devices and all their detected apps from a repository to load it into a SQL database. I have approx 25000 device and each of them can have hundreds of applications.

I tried so far to implement the following code, but I have terrible performance – last attempt took 12 hours until my computer lost connection to the DB and screwed everything without even getting to the save context part.

var devicelist = JsonConvert.DeserializeObject<List<DeviceModel>>(_DeviceyJson);

_logger.LogInformation("Get devices in db");

// Retrieve the current list of devices from the database
var dbDevices = _context.Devices
                        .Include(d => d.Apps)
                        .ToList();

// Create a dictionary for quick lookups
var dbDevicesDictionary = dbDevices.ToDictionary(d => d.Id);

// Find new devices that are not in the database
_logger.LogInformation("Iterate over devices");

foreach (var device in devicelist)
{
    _logger.LogInformation("Processing " + device.DeviceName);

    if (dbDevicesDictionary.TryGetValue(device.Id, out var dbDevice))
    {
        _logger.LogInformation(device.DeviceName + " exists, checking changes");
        // Check if the main properties of the device have changed
        bool deviceChanged = !AreDevicePropertiesEqual(device, dbDevice);

        // Check if the related Apps have changed
        bool appsChanged = !AreCollectionsEqual(device.Apps, dbDevice.Apps, app => app.id);

        // If nothing has changed, skip this device
        if (!deviceChanged && !appsChanged)
        {
            _logger.LogInformation($"No changes detected for device {device.DeviceName}. Skipping update.");
            continue;
        }

        // Update existing device
        _context.Entry(dbDevice).CurrentValues.SetValues(device);

        // Synchronize Apps
        // Find apps to remove
        if (!appsChanged)
        {
            HashSet<string> dbDeviceAppsIds = new HashSet<string>(dbDevice.Apps.Select(a => a.id));

            var appsToRemoveIds = dbDeviceAppsIds.Except(device.Apps.Select(a => a.id)).ToList();

            foreach (var appIdToRemove in appsToRemoveIds)
            {
                var appToRemove = dbDevice.Apps.First(a => a.id == appIdToRemove);
                _logger.LogInformation("Remove " + appToRemove.displayName + " from " + dbDevice.DeviceName);
                dbDevice.Apps.Remove(appToRemove);
            }

            // Find apps to add
            foreach (var newApp in device.Apps)
            {
                if (!dbDevice.Apps.Any(a => a.id == newApp.id))
                {
                    _logger.LogInformation("check if app " + newApp.displayName + " is tracked");

                    // Check if the app is already being tracked by the context
                    var trackedApp = _context.ChangeTracker.Entries<DetectedAppModel>()
                                         .FirstOrDefault(e => e.Entity.id == newApp.id)?.Entity;

                    // If the app is not being tracked, add the new app
                    if (trackedApp == null)
                    {
                        _logger.LogInformation("Aapp " + newApp.displayName + " is not tracked");
                        dbDevice.Apps.Add(newApp);
                    }
                    else
                    {
                        // If the app is already being tracked, you can update the tracked entity if needed
                        // _context.Entry(trackedApp).CurrentValues.SetValues(newApp);
                        // Or simply use the tracked entity
                        _logger.LogInformation("App " + newApp.displayName + " is tracked");
                        dbDevice.Apps.Add(trackedApp);
                    }
                }
            }
        }
    }
    else
    {
        // Add new device, including related apps
        try
        { 
            // Before adding the new device, ensure its apps are either attached or new
            _logger.LogInformation(device.DeviceName + " is new");

            foreach (var app in device.Apps.ToList()) // Use ToList to avoid modifying the collection while iterating
            {
                var existingApp = _context.DetectedApps.Local.FirstOrDefault(a => a.id == app.id) // Check local (in-memory) cache
                        ?? await _context.DetectedApps.FirstOrDefaultAsync(a => a.id == app.id); // Check the database

                if (existingApp != null)
                {
                    // If the app already exists in the context or database, use the existing instance
                    device.Apps.Remove(app);
                    device.Apps.Add(existingApp);
                }

                // If the app does not exist, it will be added as a new entity along with the new device
            }

            // Now add the new device with its apps
            _context.Devices.Add(device);
        }
        catch (Exception e)
        {
            _logger.LogError("Error while adding device {DeviceName} to the context: {Message}", device.DeviceName, e.Message);
            // Consider handling the exception based on your  application's needs
            // Do not rethrow the exception unless you have a specific reason to do so
            throw e;
        }
    }

    // Find and remove devices that are no longer in the devicelist
    var removedDevices = dbDevices.Where(db => !devicelist.Any(d => d.Id == db.Id)).ToList();
    _context.Devices.RemoveRange(removedDevices);
}

_logger.LogInformation("Save context");

var totalEntities = _context.ChangeTracker.Entries().Count();
var processedEntities = 0;
int batchSize = 500;

while (processedEntities < totalEntities)
{
    // Process a batch of changes
    var batch = _context.ChangeTracker.Entries()
                        .Where(e => e.State == EntityState.Added || e.State == EntityState.Modified || e.State == EntityState.Deleted)
                        .Skip(processedEntities)
                        .Take(batchSize)
                        .ToList();

    // If there are no more entities to process, break out of the loop
    if (!batch.Any())
    {
        break;
    }

    try
    {
        // Save the current batch
        await _context.SaveChangesAsync();

        // Detach entities to free up memory
        foreach (var entry in batch)
        {
            _context.Entry(entry.Entity).State = EntityState.Detached;
        }

        // Update the number of processed entities
        processedEntities += batch.Count;

        // Log the progress
        _logger.LogInformation($"Saved batch: {processedEntities}/{totalEntities} entities processed.");
    }
    catch (Exception ex)
    {
        // Log the exception and decide how to handle it
        _logger.LogError(ex, "An error occurred while saving a batch of changes.");
        // Depending on your requirements, you might choose to  break, continue, or retry
        break;
    }
}

I tried this code with a subset of devices and it seemed to work, so at least I thing it’s working without any change tracking issue but it’s way too slow to be usable. If someone could help me optimizing it it would be great !