Update generates invalid SQL for Oracle if OrderBy is included

[NetMastr5]

Here is what to include in your request to make sure we implement a solution as quickly as possible.

1. Description

Describe the issue or propose a feature.
The Update method generates invalid SQL on Oracle when the IQueryable includes an OrderBy call.

2. Exception

If you are seeing an exception, include the full exception details (message and stack trace).
ORA-00936: missing expression
https://docs.oracle.com/error-help/db/ora-00936/
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: Oracle.ManagedDataAccess.Client.OracleException: ORA-00936: missing expression
https://docs.oracle.com/error-help/db/ora-00936/

3. Fiddle or Project

This method will throw the error

public void temp()
{
	decimal stepId = 11;
	decimal groupId = 10;
	using(this.uow.beginTransaction())
		this.uow.workflowGroupStepPriorityRepository
			.GetByGroupId(groupId)
			.Where(x => x.workflowStepId == stepId)
			.Update(x => new WOF_GROUP_STEP_PRIORITY { priority = 0 - x.priority });
}

Here is the definition of the GetByGroupId method

public IQueryable<WOF_GROUP_STEP_PRIORITY> GetByGroupId(decimal groupId)
{
	return this.context.WOF_GROUP_STEP_PRIORITY
		.Where(p => p.groupId == groupId)
		.OrderBy(p => p.priority);
}

4. Any further technical details

Add any relevant detail can help us.

Entity definition

	[Table("WOF_GROUP_STEP_PRIORITY", Schema = "TSD_MKTG")]
	public class WOF_GROUP_STEP_PRIORITY
	{
		[Key, Column("GRP_ID", Order = 0)]
		public decimal groupId { get; set; }
		
		[Key, Column("WOF_STEP_ID", Order = 1)]
		public decimal workflowStepId { get; set; }

		[Column("PRIORITY")]
		[Required]
		public decimal priority { get; set; }

		[ForeignKey(nameof(groupId))]
		public virtual TSD_GRP foremanGroup { get; set; }

		[ForeignKey(nameof(workflowStepId))]
		public virtual WOF_WORK_FLOW_STEP workflowStep { get; set; }
	}

The code above generates the following query

  UPDATE "TSD_MKTG"."WOF_GROUP_STEP_PRIORITY"                                                     
  SET "PRIORITY" =  *                                                                             
  WHERE EXISTS ( SELECT 1 FROM (SELECT *                                                          
  FROM (                                                                                          
  SELECT                                                                                          
  "Project1"."GRP_ID" AS "GRP_ID",                                                                
  "Project1"."WOF_STEP_ID" AS "WOF_STEP_ID",                                                      
  "Project1"."PRIORITY" AS "PRIORITY"                                                             
  FROM ( SELECT                                                                                   
  	"Extent1"."GRP_ID" AS "GRP_ID",                                                                
  	"Extent1"."WOF_STEP_ID" AS "WOF_STEP_ID",                                                      
  	"Extent1"."PRIORITY" AS "PRIORITY"                                                             
  	FROM "TSD_MKTG"."WOF_GROUP_STEP_PRIORITY" "Extent1"                                            
  	WHERE (("Extent1"."GRP_ID" = :p__linq__0) AND ("Extent1"."WOF_STEP_ID" = :p__linq__1))         
  )  "Project1"                                                                                   
  ORDER BY "Project1"."PRIORITY" ASC                                                              
  )                                                                                               
  WHERE (ROWNUM <= (2147483647) )) B                                                              
                 WHERE "TSD_MKTG"."WOF_GROUP_STEP_PRIORITY"."GRP_ID" = B."GRP_ID"                 
  AND "TSD_MKTG"."WOF_GROUP_STEP_PRIORITY"."WOF_STEP_ID" = B."WOF_STEP_ID"                        
             )

If you remove the OrderBy clause, the query works and the following SQL is generated.

  UPDATE "TSD_MKTG"."WOF_GROUP_STEP_PRIORITY"                                                
  SET ("PRIORITY") = (SELECT                                                                 
  0 - B."PRIORITY" AS "C1" FROM (SELECT                                                      
  "Extent1"."GRP_ID" AS "GRP_ID",                                                            
  "Extent1"."WOF_STEP_ID" AS "WOF_STEP_ID",                                                  
  "Extent1"."PRIORITY" AS "PRIORITY"                                                         
  FROM "TSD_MKTG"."WOF_GROUP_STEP_PRIORITY" "Extent1"                                        
  WHERE (("Extent1"."GRP_ID" = :p__linq__0) AND ("Extent1"."WOF_STEP_ID" = :p__linq__1))) B  
                 WHERE "TSD_MKTG"."WOF_GROUP_STEP_PRIORITY"."GRP_ID" = B."GRP_ID"            
  AND "TSD_MKTG"."WOF_GROUP_STEP_PRIORITY"."WOF_STEP_ID" = B."WOF_STEP_ID" )                 
  WHERE EXISTS ( SELECT 1 FROM (SELECT                                                       
  "Extent1"."GRP_ID" AS "GRP_ID",                                                            
  "Extent1"."WOF_STEP_ID" AS "WOF_STEP_ID",                                                  
  "Extent1"."PRIORITY" AS "PRIORITY"                                                         
  FROM "TSD_MKTG"."WOF_GROUP_STEP_PRIORITY" "Extent1"                                        
  WHERE (("Extent1"."GRP_ID" = :p__linq__0) AND ("Extent1"."WOF_STEP_ID" = :p__linq__1))) B  
                 WHERE "TSD_MKTG"."WOF_GROUP_STEP_PRIORITY"."GRP_ID" = B."GRP_ID"            
  AND "TSD_MKTG"."WOF_GROUP_STEP_PRIORITY"."WOF_STEP_ID" = B."WOF_STEP_ID"                   
             )

Further technical details

• .Net Framework Version: 4.8.1
• EF version: EntityFramework 6.5.1
• EF Plus version: Z.EntityFramework.Plus.EF6 8.103.6.2
• Database Server version: Oracle 19c
• Database Provider version (NuGet): Oracle.ManagedDataAccess 23.6.1
• Oracle.ManagedDataAccess.EntityFramework 23.4.0

[JonathanMagnan]

Hello @NetMastr5 ,

I will look with my developer if we want to fix it.

Is there a reason in this case that you do not simply create a new method or one with an overload to remove the OrderBy from your LINQ query?

public IQueryable<WOF_GROUP_STEP_PRIORITY> GetByGroupIdWithoutOrder(decimal groupId)
{
	return this.context.WOF_GROUP_STEP_PRIORITY
		.Where(p => p.groupId == groupId);
}

Best Regards,

Jon

[NetMastr5]

That is the workaround we will be using. Just wanted to log the issue so it is known.

…

On Wed, Nov 27, 2024, 2:51 PM Jonathan Magnan ***@***.***> wrote: Hello @NetMastr5 <https://github.com/NetMastr5> , I will look with my developer if we want to fix it. Is there a reason in this case that you do not simply create a new method or one with an overload to remove the OrderBy from your LINQ query? public IQueryable<WOF_GROUP_STEP_PRIORITY> GetByGroupIdWithoutOrder(decimal groupId){ return this.context.WOF_GROUP_STEP_PRIORITY .Where(p => p.groupId == groupId);} Best Regards, Jon — Reply to this email directly, view it on GitHub <#819 (comment)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/ACDZGSFTWBFIM4QDBJILSHD2CYWEHAVCNFSM6AAAAABSTMLU4SVHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMZDKMBUG42DSNZVGI> . You are receiving this because you were mentioned.Message ID: ***@***.***>