Upgrading issue. Refactor "Newsletter" migration to work with large number of records

[Manuel-Innovapps]

nopCommerce version: 4.70.0

Steps to reproduce the problem:

1. Have a database that has collected user data and newsletter subscription informations for mutliple years
2. Start an update to 4.70.0
3. Update fails with the following stack trace:

   Microsoft.Data.SqlClient.SqlException (0x80131904): The query processor ran out of internal resources and could not produce a query plan. This is a rare event and only expected for extremely complex queries or queries that reference a very large number of tables or partitions. Please simplify the query. If you believe you have received this message in error, contact Customer Support Services for more information.
   at Microsoft.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   at Microsoft.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   at Microsoft.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, SqlCommand command, Boolean callerHasConnectionLock, Boolean asyncClose)
   at Microsoft.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
   at Microsoft.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean isAsync, Int32 timeout, Boolean asyncWrite)
   at Microsoft.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource`1 completion, Boolean sendToPipe, Int32 timeout, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry, String methodName)
   at Microsoft.Data.SqlClient.SqlCommand.ExecuteNonQuery()
   at LinqToDB.Data.DataConnection.ExecuteNonQuery(DbCommand command)
   at LinqToDB.Data.DataConnection.ExecuteNonQuery()
   at LinqToDB.Data.DataConnection.QueryRunner.ExecuteNonQueryImpl(DataConnection dataConnection, ExecutionPreparedQuery executionQuery)
   at LinqToDB.Data.DataConnection.QueryRunner.ExecuteNonQuery()
   at LinqToDB.Linq.QueryRunner.NonQueryQuery(Query query, IDataContext dataContext, Expression expr, Object[] parameters, Object[] preambles)
   at LinqToDB.Linq.QueryRunner.<>c__DisplayClass32_0.<SetNonQueryQuery>b__0(IDataContext db, Expression expr, Object[] ps, Object[] preambles)
   at LinqToDB.Linq.ExpressionQuery`1.System.Linq.IQueryProvider.Execute[TResult](Expression expression)
   at LinqToDB.LinqExtensions.Merge[TTarget,TSource](IMergeable`2 merge)
   at Nop.Data.DataProviders.MsSqlNopDataProvider.UpdateEntities[TEntity](IEnumerable`1 entities) in D:\verticasbasisshop\Libraries\Nop.Data\DataProviders\MsSqlDataProvider.cs:line 334
   at Nop.Data.Migrations.UpgradeTo470.DataMigration.Up() in D:\verticasbasisshop\Libraries\Nop.Data\Migrations\UpgradeTo470\DataMigration.cs:line 200
   at FluentMigrator.MigrationBase.GetUpExpressions(IMigrationContext context)
   at FluentMigrator.Runner.MigrationRunner.<>c.<ApplyMigrationUp>b__70_0(IMigration m, IMigrationContext c)
   at FluentMigrator.Runner.MigrationRunner.ExecuteMigration(IMigration migration, Action`2 getExpressions)
   at FluentMigrator.Runner.MigrationRunner.ApplyMigrationUp(IMigrationInfo migrationInfo, Boolean useTransaction)
   ClientConnectionId:08d071fb-dcdc-4a4a-9bbd-181205d957d6
   Fehlernummer (Error Number):8623,Status (State):1,Klasse (Class):16

These are the lines that are failing, especially line 200 https://github.com/nopSolutions/nopCommerce/blob/82a5e6da7429ac994172175fbb3efe87ebd3fef4/src/Libraries/Nop.Data/Migrations/UpgradeTo470/DataMigration.cs#L190-L201

Happens to use in a database with 20780 NewsLetterSubscription while testing the upgrade process locally.

We should propably update the entities in batches, 100 or 1000 at a time.

[AndreiMaz]

Continuation of https://github.com/nopSolutions/nopCommerce/issues/6978

[skoshelev]

Closed #7194

[Manuel-Innovapps]

Thanks! Can confirm this is working as expected now.