How do I perform an ExecuteUpdate on a MySQL DB Context DbSet<T>, given a List<T> of the current information?

Question

I have three separate databases (Site A, Site B, Site C), using the same database software with some minor structural differences, from which the same types of data need retrieving, in some cases combining (Site A and B), and putting up to a separate dashboard.

As this data refreshes daily, the data needs inserting when new, updating when changed, and deleted when no longer present in the live time period, either by being deleted outright or the data aging out.

Because of the three separate sites, and the databases involved being old, cumbersome, not developer friendly (there are no up to date nuget libraries for them, the database type is officially no longer supported etc), and servicing live environments, I'm pulling the data out into a separate MySQL DB to manipulate.

The existing process for populating the dashboards is manually extracting the data into CSVs, combining the CSVs where necessary, then replacing the CSVs stored against each dashboard. As the dashboard setups are accepted as they are, I have to keep the same overall format.

Since DbContexts love to have each table be its own object type and can't recognise the tables correctly without having specific types relevant to the table names, each table object type extends a base object type.

I have the following (simplified) MySQL DBContext:

public class MySqlDbContext : DbContext
{
    public MySqlDbContext(DbContextOptions<MySqlDbContext> options) : base(options)
    {
    }

    protected override void OnModelCreating(ModelBuilder builder)
    {
        builder.Entity<Ball_A>()
            .HasKey(nameof(Ball_A.Site),
                    nameof(Ball_A.ID));

        builder.Entity<Ball_B>()
            .HasKey(nameof(Ball_B.Site),
                    nameof(Ball_B.ID));

        builder.Entity<Ball_AB>()
            .HasKey(nameof(Ball_AB.Site),
                    nameof(Ball_AB.ID));

        builder.Entity<Ball_C>()
            .HasKey(nameof(Ball_C.Site),
                    nameof(Ball_C.ID));

        builder.Entity<Box_A>()
            .HasKey(nameof(Box_A.Site),
                    nameof(Box_A.ID));

        builder.Entity<Box_B>()
            .HasKey(nameof(Box_B.Site),
                    nameof(Box_B.ID));

        builder.Entity<Box_AB>()
            .HasKey(nameof(Box_AB.Site),
                    nameof(Box_AB.ID));

        builder.Entity<Box_C>()
            .HasKey(nameof(Box_C.Site),
                    nameof(Box_C.ID));
    }

    public DbSet<Ball_A> Ball_A { get; set; }
    public DbSet<Ball_B> Ball_B { get; set; }
    public DbSet<Ball_AB> Ball_AB { get; set; }
    public DbSet<Ball_C> Ball_C { get; set; }
    public DbSet<Box_A> Box_A { get; set; }
    public DbSet<Box_B> Box_B { get; set; }
    public DbSet<Box_AB> Box_AB { get; set; }
    public DbSet<Box_C> Box_C { get; set; }
}

The following objects relating to the above context:

public class Ball
{
    public string Site { get; set; };
    public int ID { get; set; }
    public DateTime Created { get; set; }
    public DateTime LastBounced { get; set; }
    public int Radius { get; set; }
}

[Table("ball_a")]
public class Ball_A : Ball {}
[Table("ball_b")]
public class Ball_B : Ball {}
[Table("ball_ab")]
public class Ball_AB : Ball {}
[Table("ball_c")]
public class Ball_C : Ball {}

public class Box
{
    public string Site { get; set; };
    public int ID { get; set; }
    public DateTime Created { get; set; }
    public DateTime LastOpened { get; set; }
    public int Length { get; set; }
    public int Width { get; set; }
}

[Table("box_a")]
public class Box_A : Box {}
[Table("box_b")]
public class Box_B : Box {}
[Table("box_ab")]
public class Box_AB : Box {}
[Table("box_c")]
public class Box_C : Box {}

I'm able to successfully retrieve data from the site(s) and temporarily store information as a List<Ball_A>, List<Box_A> etc. When I'm trying to update existing data in the (currently empty) MySQL database, however, I'm getting a problem with the join:

Example LINQ:

List<Ball_A> latestBallA = new(); // confirmed as populated from the live Site A DB; for brevity, just showing the object type.

int ballUpdate = from existingBallData in _mySqlDbContext.Ball_A
                join latestBallData in latestBallA on new { existingBallData.Site, existingBallData.ID } equals new { latestBallData.Site, latestBallData.ID }
                select new { existingBallData, latestBallData }).ExecuteUpdate(s =>
                s.SetProperty(x => x.existingBallData.LastBounced, x => x.latestBallData.LastBounced)
                s.SetProperty(x => x.existingBallData.Radius, x => x.latestBallData.Radius)
                );

The following InvalidOperationException is thrown:

System.InvalidOperationException
  HResult=0x80131509
  Message=The LINQ expression 'DbSet<Ball_A>()
    .Join(
        inner: __p_0, 
        outerKeySelector: existingBallData => new { 
            Site = existingBallData.Site, 
            ID = existingBallData.ID
         }, 
        innerKeySelector: latestBallData => new { 
            Site = latestBallData.Site, 
            ID = latestBallData.ID
         }, 
        resultSelector: (existingBallData, latestBallData) => new { 
            existingBallData = existingBallData, 
            latestBallData = latestBallData
         })' could not be translated. Either rewrite the query in a form that can be translated, or switch to client evaluation explicitly by inserting a call to 'AsEnumerable', 'AsAsyncEnumerable', 'ToList', or 'ToListAsync'. See https://go.microsoft.com/fwlink/?linkid=2101038 for more information.
  Source=Microsoft.EntityFrameworkCore
  StackTrace:
   at Microsoft.EntityFrameworkCore.Query.Internal.NavigationExpandingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)
   at System.Dynamic.Utils.ExpressionVisitorUtils.VisitArguments(ExpressionVisitor visitor, IArgumentProvider nodes)
   at System.Linq.Expressions.ExpressionVisitor.VisitMethodCall(MethodCallExpression node)
   at Microsoft.EntityFrameworkCore.Query.Internal.NavigationExpandingExpressionVisitor.ProcessUnknownMethod(MethodCallExpression methodCallExpression)
   at Microsoft.EntityFrameworkCore.Query.Internal.NavigationExpandingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)
   at Microsoft.EntityFrameworkCore.Query.Internal.NavigationExpandingExpressionVisitor.Expand(Expression query)
   at Microsoft.EntityFrameworkCore.Query.QueryTranslationPreprocessor.Process(Expression query)
   at Microsoft.EntityFrameworkCore.Query.QueryCompilationContext.CreateQueryExecutor[TResult](Expression query)
   at Microsoft.EntityFrameworkCore.Storage.Database.CompileQuery[TResult](Expression query, Boolean async)
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.CompileQueryCore[TResult](IDatabase database, Expression query, IModel model, Boolean async)
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.<>c__DisplayClass9_0`1.<Execute>b__0()
   at Microsoft.EntityFrameworkCore.Query.Internal.CompiledQueryCache.GetOrAddQuery[TResult](Object cacheKey, Func`1 compiler)
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.Execute[TResult](Expression query)
   at Microsoft.EntityFrameworkCore.Query.Internal.EntityQueryProvider.Execute[TResult](Expression expression)
   at Microsoft.EntityFrameworkCore.RelationalQueryableExtensions.ExecuteUpdate[TSource](IQueryable`1 source, Expression`1 setPropertyCalls)
   at Example.BallAFetcher.<Fetch>d__6.MoveNext() in C:\GitHub\Example\BallAFetcher.cs:line 101

  This exception was originally thrown at this call stack:
    [External Code]
    Example.BallAFetcher.Fetch() in BallAFetcher.cs

Trying to "switch to client evaluation explicitly", by making int ballUpdate = from existingBallData in _mySqlDbContext.Ball_A -> int ballUpdate = from existingBallData in _mySqlDbContext.Ball_A.AsEnumerable() leads to ExecuteUpdate being flagged, as IEnumerable doesn't contain a definition for ExecuteUpdate. The same happens when trying to make the list latestBallA -> latestBallA.AsQueryable().

I don't know how to revise my query, as it follows the same format as the many examples out there (on SO, Microsoft documentation, generally on the internet) for joins on composite primary keys, as well as examples of joins for performing ExecuteUpdate.

I have tried reversing the order of the LINQ join, so it goes List->DbSet instead of DbSet->List, which gets past the initial InvalidOperationException, but provides a different InvalidOperationException - There is no method 'ExecuteUpdate' on type 'Microsoft.EntityFrameworkCore.RelationalQueryableExtensions' that matches the specified arguments - that I assume is because the application is thinking I'm trying to perform the ExecuteUpdate from the List perspective, despite the logic in the ExecuteUpdate.

From looking at the most similar questions, my problem seems to be because I'm joining a database DbSet<T> to an in-memory List<T>? Do I need to create a table (i.e. a "temp_ball_a", with "DbSet<Temp_Ball_A> Temp_Ball_A") to store the list data in and perform the CRUD operations between tables to keep it all in the DB context, or is there another (potentially better) way to accomplish this?