Not able to use the package with .NET 9

[Thovenaar]

The following error comes up when you upgrade to .NET 9.

Warning As Error: Detected package version outside of dependency constraint: Pomelo.EntityFrameworkCore.MySql 8.0.2 requires Microsoft.EntityFrameworkCore.Relational (>= 8.0.2 && <= 8.0.999) but version Microsoft.EntityFrameworkCore.Relational 9.0.0 was resolved.

I think we need to either upgrade Pomelo.EntityFrameworkCore to support .NET 9 or find a replacement.

[Thovenaar]

This is specifically for the MySql package which I am not using at the moment. I decided to install just the SqlServer package and that worked for me. Closing this issue.

[borisdj]

Will check it out for MY as well, soon when new version gets added.

[benpsnyder]

I didn't have success with PGSQL either. I will re-check it in the coming days.

[Thovenaar]

I will reopen the issue as the actual problem is still not solved due to an external dependency.

[borisdj]

Pomelo for MySql has preRelease 9 but even that has some companitibilty issues.
Still they said that Release version would be published this or next week, so will update it to 9 as soon as they publish it.
If it does not get published till the end of November will then try to publish prerelease of Bulk 9 as well.

[borisdj]

v9.0.0-rc.1 is now published on nuget.
Will be updated to full release as soon as Mysql v9 is also fully release, expecting it to be in a few days.

[evicio1]

I have installed the preview version and tried it. Getting the same error that I mentioned in #1620

at EFCore.BulkExtensions.IQueryableExtensions.ToParametrizedSql(IQueryable query)
at EFCore.BulkExtensions.BatchUtil.GetBatchSql(IQueryable query, DbContext context, Boolean isUpdate)
at EFCore.BulkExtensions.SqlAdapters.SqlQueryBuilder.MergeTable[T](DbContext context, TableInfo tableInfo, OperationType operationType, IEnumerable1 entityPropertyWithDefaultValue) at EFCore.BulkExtensions.SqlAdapters.SqlServer.SqlServerAdapter.<MergeAsync>d__91.MoveNext()
at EFCore.BulkExtensions.SqlAdapters.SqlServer.SqlServerAdapter.d__91.MoveNext() at EFCore.BulkExtensions.SqlAdapters.SqlServer.SqlServerAdapter.<MergeAsync>d__81.MoveNext()
at EFCore.BulkExtensions.SqlBulkOperation.d__51.MoveNext() at EFCore.BulkExtensions.DbContextBulkTransaction.<ExecuteAsync>d__11.MoveNext()

[borisdj]

@evicio1 are you using one of configs SynchronizeFilter or SynchronizeSoftDelete ?

[evicio1]

Yes, SynchronizeFilter.

ex:

await context.BulkInsertOrUpdateOrDeleteAsync(dto, options =>
            {
                options.UpdateByProperties = ["Code"];
                options.PropertiesToExcludeOnUpdate = [
                    "Percentage","Description","CountryCode",];
                options.SetSynchronizeFilter<MonthlySalary>(x => x.code == "TEST");
            }, cancellationToken: cancellationToken);
            

[borisdj]

@evicio1 Can you write entire test for this (or alter existing one for BulkSync), not able to repreduce it.
Still I can see where the bug is but don't have the sollution at the moment.

[smolleman]

This is specifically for the MySql package which I am not using at the moment. I decided to install just the SqlServer package and that worked for me. Closing this issue.

We're not using any MySql packages but I am not able to update Microsoft.EntityFrameworkCore.SqlServer package to 9.0.0 because of the following error:
EFCore.BulkExtensions 8.1.2 -> EFCore.BulkExtensions.MySql 8.1.2 -> Pomelo.EntityFrameworkCore.MySql 8.0.2 -> Microsoft.EntityFrameworkCore.Relational (>= 8.0.2 && <= 8.0.999).

These are our relevant nuget packages:
EFCore.BulkExtensions v8.1.2
Microsoft.EntityFrameworkCore v8.0.11
Microsoft.EntityFrameworkCore.SqlServer v8.0.11

[evicio1]

@borisdj
It seems the issue lies with IQueryableExtensions.ToParametrizedSql(). In EF Core 9, it appears that retrieving RelationalCommandCache using the following approach is no longer working:
var relationalCommandCache = (RelationalCommandCache?)enumerator.Private(relationalCommandCacheText);

I implemented a small fix that seems to resolve the issue. Could you please review it?

 string relationalCommandResolverText = "_relationalCommandResolver";
var relationalCommandResolver = enumerator.Private<Delegate>(relationalCommandResolverText);
command = (IRelationalCommand)relationalCommandResolver.DynamicInvoke(parameterValues);

---

public static class IQueryableExtensions
{
    /// <summary>
    /// Extension method to paramatize sql query
    /// </summary>
    /// <param name="query"></param>
    /// <returns></returns>
    /// <exception cref="InvalidOperationException"></exception>
    public static (string, IEnumerable<DbParameter>) ToParametrizedSql(this IQueryable query)
    {
        string relationalQueryContextText = "_relationalQueryContext";
        string relationalCommandCacheText = "_relationalCommandCache";
        string relationalCommandResolverText = "_relationalCommandResolver";
        

        string cannotGetText = "Cannot get";

#pragma warning disable CS8602 // Dereference of a possibly null reference.
        var enumerator = query.Provider.Execute<IEnumerable>(query.Expression).GetEnumerator();
#pragma warning restore CS8602 // Dereference of a possibly null reference.
        var queryContext = enumerator.Private<RelationalQueryContext>(relationalQueryContextText) ?? throw new InvalidOperationException($"{cannotGetText} {relationalQueryContextText}");
        var parameterValues = queryContext.ParameterValues;

#pragma warning disable EF1001 // Internal EF Core API usage.
        var relationalCommandCache = (RelationalCommandCache?)enumerator.Private(relationalCommandCacheText);
        var relationalCommandResolver = enumerator.Private<Delegate>(relationalCommandResolverText);
#pragma warning restore EF1001

        IRelationalCommand command;
        if (relationalCommandResolver != null)
        {
#pragma warning disable EF1001 // Internal EF Core API usage.
            command = (IRelationalCommand)relationalCommandResolver.DynamicInvoke(parameterValues);
            //command = (IRelationalCommand)relationalCommandCache.GetRelationalCommandTemplate(parameterValues);
#pragma warning restore EF1001
        }
        else
        {
            string selectExpressionText = "_selectExpression";
            string querySqlGeneratorFactoryText = "_querySqlGeneratorFactory";
            SelectExpression selectExpression = enumerator.Private<SelectExpression>(selectExpressionText) ?? throw new InvalidOperationException($"{cannotGetText} {selectExpressionText}");
            IQuerySqlGeneratorFactory factory = enumerator.Private<IQuerySqlGeneratorFactory>(querySqlGeneratorFactoryText) ?? throw new InvalidOperationException($"{cannotGetText} {querySqlGeneratorFactoryText}");
            command = factory.Create().GetCommand(selectExpression);
        }
        string sql = command.CommandText;

        IList<DbParameter> parameters;
        try
        {
            using var dbCommand = SqlAdaptersMapping.DbServer.QueryBuilder.CreateCommand(); // Use a DbCommand to convert parameter values using ValueConverters to the correct type.
            foreach (var param in command.Parameters)
            {
                var values = parameterValues[param.InvariantName];
                param.AddDbParameter(dbCommand, values);
            }
            parameters = new List<DbParameter>(dbCommand.Parameters.OfType<DbParameter>());
            dbCommand.Parameters.Clear();
        }
        catch (Exception ex) // Fix for BatchDelete with 'uint' param on Sqlite. TEST: RunBatchUint
        {
            var npgsqlSpecParamMessage = "Npgsql-specific type mapping ";
            // Full Msg:
            // "Npgsql-specific type mapping Npgsql.EntityFrameworkCore.PostgreSQL.Storage.Internal.Mapping.NpgsqlArrayListTypeMapping being used with non-Npgsql parameter type SqlParameter"
            // "Npgsql-specific type mapping NpgsqlTimestampTzTypeMapping being used with non-Npgsql parameter type SqlParameter" // for Batch (a < date)

            if (ex.Message.StartsWith("No mapping exists from DbType") && ex.Message.EndsWith("to a known SqlDbType.") || // example: "No mapping exists from DbType UInt32 to a known SqlDbType."
                ex.Message.StartsWith(npgsqlSpecParamMessage)) // Fix for BatchDelete with Contains on PostgreSQL
            {
                var parameterNames = new HashSet<string>(command.Parameters.Select(p => p.InvariantName));
                parameters = parameterValues.Where(pv => parameterNames.Contains(pv.Key)).Select(pv => SqlAdaptersMapping.DbServer.QueryBuilder.CreateParameter("@" + pv.Key, pv.Value)).ToList();
            }
            else
            {
                throw;
            }
        }
        return (sql, parameters);
    }

[borisdj]

@evicio1 I have integrate it and made a commit.
Can you take the latest source and check that it works for you case.

[borisdj]

Also provider specific nugets for MS, PG and LT are now published in full release 9.0.0.
MY is not and also Main, that has all providers, is not since MySql {review version has dependency conflict with Release.

[evicio1]

I have tested and it's all good. Thank you @borisdj