Skip to content

EF7 generating incorrect SQL for the Concat/Union All #30273

@onlypritam

Description

@onlypritam

It seems the same code that used to work in EF core 6.4.4 is not breaking in EF 7 and creating an incorrect SQL statement.

Because of this incorrect SQL we are getting an error: "All queries combined using a UNION, INTERSECT or EXCEPT operator must have an equal number of expressions in their target lists."

//Just create a Student class with 2 or 3 fields (with one of the varchar column name as City), and create the table from it. Enter some dummy data (use Delhi, Bangalore And Kolkata in the city column or change the filters in the code below accordingly) Then execute the following code 
//Basically the "Union All" SQL query that it's creating is incorrect

public static string M1()
{
BDbContext db = new BDbContext();
IQueryable<Student> DelhiStudents = (from N in db.students where N.City== "Delhi" select N).Distinct();
IQueryable<Student> BlrStudents = (from N in db.students where N.City == "Bangalore" select N).Distinct();
IQueryable<Student> KolStudents = (from N in db.students where N.City == "Kolkata" select N).Distinct();
IQueryable<Student> AllStudents= DelhiStudents.Concat(BlrStudents).Concat(KolStudents);
IList<string>Students= AllStudents.Select(x=>x.Name).ToList(); //<< this is the line of code that throws the exception>>
return "Success";
}

Include stack traces

Microsoft.Data.SqlClient.SqlException
HResult=0x80131904
Message=All queries combined using a UNION, INTERSECT or EXCEPT operator must have an equal number of expressions in their target lists.
  Source=Core Microsoft SqlClient Data Provider
  StackTrace:
  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, 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.SqlDataReader.TryConsumeMetaData()
  at Microsoft.Data.SqlClient.SqlDataReader.get_MetaData()
  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.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
 at Microsoft.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior)
 at Microsoft.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior)
 at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReader(RelationalCommandParameterObject parameterObject)
 at Microsoft.EntityFrameworkCore.Query.Internal.SingleQueryingEnumerable`1.Enumerator.InitializeReader(Enumerator enumerator)
 at Microsoft.EntityFrameworkCore.Query.Internal.SingleQueryingEnumerable`1.Enumerator.<>c.<MoveNext>b__21_0(DbContext _, Enumerator enumerator)
 at Microsoft.EntityFrameworkCore.SqlServer.Storage.Internal.SqlServerExecutionStrategy.Execute[TState,TResult](TState state, Func`3 operation, Func`3 verifySucceeded)
 at Microsoft.EntityFrameworkCore.Query.Internal.SingleQueryingEnumerable`1.Enumerator.MoveNext()
 at System.Collections.Generic.List`1..ctor(IEnumerable`1 collection)
 at System.Linq.Enumerable.ToList[TSource](IEnumerable`1 source)
 at Beginers.Program.M1() 
 at Beginers.Program.Main(String[] args) 

### Include provider and version information

EF Core version: 7.x
Database provider: (e.g. Microsoft.EntityFrameworkCore.SqlServer)
Target framework: ( .NET 7.0)
Operating system: Windows 10
IDE: (Visual Studio 2022 17.4)

Metadata

Metadata

Assignees

Type

Projects

No projects

Milestone

Relationships

None yet

Development

No branches or pull requests

Issue actions