EFcore 8 UpdateFromQuery generates invalid sql

[lee-baker]

Description

We have recently updated to EFCore 8 and finding an error with UpdateFromQuery where the SQL generated is not valid

The generated SQL looks like this
exec sp_executesql N' UPDATE A SET A.[Status] = @zzz_BatchUpdate_0 FROM [] AS A INNER JOIN ( SELECT [p].[PaymentItemNumber], [p].[Amount], [p].[CardId], [p].[DestinationAccountName], [p].[DestinationAccountNumber], [p].[DestinationBsb], [p].[LodgementReference], [p].[PaymentId], [p].[PaymentRunId], [p].[PaymentType], [p].[SourceAccountName], [p].[SourceAccountNumber], [p].[SourceBsb], [p].[Status] FROM [PaymentServiceMemberPaymentData] AS [p] WHERE [p].[PaymentRunId] = @__paymentRunId_0 ) AS B ON A.[PaymentItemNumber] = B.[PaymentItemNumber] ',N'@__paymentRunId_0 int,@zzz_BatchUpdate_0 nvarchar(9)',@__paymentRunId_0=15139,@zzz_BatchUpdate_0=N'Requested'

Exception

Microsoft.Data.SqlClient.SqlException (0x80131904): An object or column name is missing or empty. For SELECT INTO statements, verify each column has a name. For other statements, look for empty alias names. Aliases defined as "" or [] are not allowed. Change the alias to a valid name.

Exception message:
Stack trace:
Microsoft.Data.SqlClient.SqlException (0x80131904): An object or column name is missing or empty. For SELECT INTO statements, verify each column has a name. For other statements, look for empty alias names. Aliases defined as "" or [] are not allowed. Change the alias to a valid name.
at Microsoft.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, 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.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString, Boolean isInternal, Boolean forDescribeParameterEncryption, Boolean shouldCacheForAlwaysEncrypted)
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.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, Action`1 batchUpdateBuilder)
at BatchUpdateExtensions.`1.()

Further technical details

• EF version: [EF Core v8.0.4]
• EF Extensions version: [EFE Core v8.0.3]
• Database Server version: [SQL Server 2022]
• Database Provider version (NuGet): [Microsoft.Data.SqlClient v5.1.2]

[lee-baker]

I have realised I was trying to run the update from query against a view.
In previous versions this did work.

[JonathanMagnan]

Hello @lee-baker ,

Thank you for reporting, my developer will try your scenario.

Best Regards,

Jon

[JonathanMagnan]

Hello @lee-baker ,

I just wanted to let you know that a fix has been made and should be available in 2-3 weeks.

I will let you know when the new version will be deployed.

Best Regards,

Jon

[lee-baker]

Thank you for that. Appreciate it Lee

…

-------- If we have data, let’s look at data. If all we have are opinions, let’s go with mine. * - *Jim Barksdale, former CEO of Netscape

On Fri, 10 May 2024 at 03:13, Jonathan Magnan ***@***.***> wrote: Hello @lee-baker <https://github.com/lee-baker> , I just wanted to let you know that a fix has been made and should be available in 2-3 weeks. I will let you know when the new version will be deployed. Best Regards, Jon — Reply to this email directly, view it on GitHub <#579 (comment)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/AAXFX2R6B4MJE3A4NXHQ3CLZBPDFTAVCNFSM6AAAAABG7K6RPWVHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMZDCMBTGI3DGMJZHE> . You are receiving this because you were mentioned.Message ID: ***@***.***>

[JonathanMagnan]

Hello @lee-baker ,

The v8.102.2.5 has been released.

Could you let us know if my developer has fixed the issue correctly?

Best Regards,

Jon