EFCore 6 + FromSqlRaw + UpdateFromQuery

[itan-mcp]

1. Description

When using "FromSqlRaw" to populate an entity from a derived query, a System.ArgumentException exception is thrown

EFCore: 6.0.2/6.0.1
Z.EntityFramework.Plus.EFCore: 6.13.10

Code sample:

IQueryable<ExportedOrder> exportedOrdersQuery = dbContext.ExportedOrders.FromSqlRaw("SELECT * FROM ExportedOrders");

exportedOrdersQuery.UpdateFromQuery(x => new ExportedOrder
{
    SomeProp = "Some Text"
});

2. Exception

Exception message: No mapping exists from object type System.Object[] to a known managed provider native type.
Stack trace: 
   at Microsoft.Data.SqlClient.MetaType.GetMetaTypeFromValue(Type dataType, Object value, Boolean inferLen, Boolean streamAllowed)
   at Microsoft.Data.SqlClient.MetaType.GetMetaTypeFromType(Type dataType)
   at Microsoft.Data.SqlClient.SqlParameter.GetMetaTypeOnly()
   at Microsoft.Data.SqlClient.SqlParameter.Validate(Int32 index, Boolean isCommandProc)
   at Microsoft.Data.SqlClient.SqlCommand.BuildParamList(TdsParser parser, SqlParameterCollection parameters, Boolean includeReturnValue)
   at Microsoft.Data.SqlClient.SqlCommand.BuildExecuteSql(CommandBehavior behavior, String commandText, SqlParameterCollection parameters, _SqlRPC& rpc)
   at Microsoft.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean isAsync, Int32 timeout, Task& task, Boolean asyncWrite, Boolean inRetry, SqlDataReader ds, Boolean describeParameterEncryptionRequest)
   at Microsoft.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry, String method)
   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 Z.EntityFramework.Extensions.BatchUpdate.<>c.(DbCommand , Nullable`1 )
   at Z.EntityFramework.Extensions.BatchUpdate.Execute[T](IQueryable`1 query, Expression`1 updateFactory)
   at BatchUpdateExtensions.UpdateFromQuery[T](IQueryable`1 query, Expression`1 updateFactory)

4. Any further technical details

Same happens for DeleteFromQuery

[JonathanMagnan]

Hello @itan-mcp ,

Thank you for reporting, we will look at this.

Best Regards,

Jon

---

Sponsorship
Help us improve this library

Performance Libraries
context.BulkInsert(list, options => options.BatchSize = 1000);
Entity Framework Extensions • Bulk Operations • Dapper Plus

Runtime Evaluation
Eval.Execute("x + y", new {x = 1, y = 2}); // return 3
C# Eval Function • SQL Eval Function

[itan-mcp]

Hello @JonathanMagnan

After quick look using with Executing Interceptor, I found that for FromSqlRaw case both UpdateFromQuery/DeleteFromQuery always add additional redundant parameter to DbCommand.Parameters with empty object array value (object[0]) which is not supported by Microsoft.Data.SqlClient.

So, for now the workaround might be just to remove such parameters from collection by using Executing Interceptor, until the fix

IQueryable<ExportedOrder> exportedOrdersQuery = dbContext.ExportedOrders.FromSqlRaw("SELECT * FROM ExportedOrders");

exportedOrdersQuery.UpdateFromQuery(x => new ExportedOrder
{
    SomeProp = "Some Text"
},
(batch) =>
{
    batch.Executing = (dbCommand) =>
    {
        var sqlCommand = dbCommand as Microsoft.Data.SqlClient.SqlCommand;

        if (sqlCommand?.Parameters?.Count > 0)
        {
            var parameters = sqlCommand.Parameters.OfType<Microsoft.Data.SqlClient.SqlParameter>().ToArray(); 

            foreach(var parameter in parameters)
            {
                if (parameter.Value != null && parameter.Value is object[] objectArr && objectArr.Length == 0)
                {
                    sqlCommand.Parameters.Remove(parameter);
                }
            }
        }
    };
});

when using only one db provider (MSSQL) it can be done without casting

[JonathanMagnan]

Hello @itan-mcp ,

At this moment, I believe you will need to keep using this workaround.

We could certainly support the FromSqlRaw with no parameter the way you proposed but the problem is when there is some parameter, depending on how they are passed, we didn't succeed to fix it (when passed with an anonymous type).

Since EF Core 7 is redoing that feature on their side, we will probably wait to see if it is really worth time for us to fix it or they will already handle all these kinds of case on their side.