Problem with Union operator in query

Posts   
 
    
ladamik
User
Posts: 2
Joined: 15-Feb-2024
# Posted on: 15-Feb-2024 12:36:41   

Hi, I use LLBLGen 5.11.0, Postgres version: 15.5 and .NET 8.0. I tried to create SQL query and use "union" operator:

var q1 = from eec in linqMetaData.EmployeeTable
         select new { eec.Number, Date = eec.StartDate };

var q2 = from eec in linqMetaData.EmployeeTable
         join ssi in linqMetaData.InfoTable on eec.InfoId equals ssi.InfoId
         select new { eec.Number, Date = ssi.StartDate };

var q3 = q1.Union(q2);

var result =  q3.ToList();

When I want to fetch result, I get an error:

System.InvalidOperationException: 'BUG: Converting a unioned query to QueryParameters failed as the query is of type 'System.Linq.IQueryable`1[[<>f__AnonymousType42`2[[System.String, System.Private.CoreLib, Version=8.0.0.0, Culture=neutral, PublicKeyToken=7cec85d7bea7798e],[System.DateTime, System.Private.CoreLib, Version=8.0.0.0, Culture=neutral, PublicKeyToken=7cec85d7bea7798e]], Epos.Payroll.Api, Version=1.0.0.0, Culture=neutral, PublicKeyToken=null]]' and not of type QueryExpression'

Stack trace:
   at SD.LLBLGen.Pro.LinqSupportClasses.ExpressionClasses.QueryExpression.AppendUnionedQueriesAsQueryParameters(QueryParameters toAppendTo, Func`2 preprocessorFunc)
   at SD.LLBLGen.Pro.LinqSupportClasses.ExpressionClasses.QueryExpression.GetQueryParameters(SetAlias aliasSuggestion, Func`2 unionedQueriesPreprocessorFunc)
   at SD.LLBLGen.Pro.LinqSupportClasses.LLBLGenProProvider2.ExecuteValueListProjection(QueryExpression toExecute)
   at SD.LLBLGen.Pro.LinqSupportClasses.LLBLGenProProviderBase.ExecuteExpression(Expression handledExpression, Type typeForPostProcessing)
   at SD.LLBLGen.Pro.LinqSupportClasses.LLBLGenProProviderBase.PerformExecute(Expression expression, Type resultType)
   at SD.LLBLGen.Pro.LinqSupportClasses.LLBLGenProProviderBase.System.Linq.IQueryProvider.Execute(Expression expression)
   at SD.LLBLGen.Pro.LinqSupportClasses.LLBLGenProQuery`1.Execute()
   at SD.LLBLGen.Pro.LinqSupportClasses.LLBLGenProQuery`1.System.Collections.Generic.IEnumerable<T>.GetEnumerator()
   at System.Collections.Generic.List`1..ctor(IEnumerable`1 collection)
   at System.Linq.Enumerable.ToList[TSource](IEnumerable`1 source)

My question is if in this case, should I create a view in SQL or I can resolve this issue in another way.

Walaa avatar
Walaa
Support Team
Posts: 14975
Joined: 21-Aug-2005
# Posted on: 16-Feb-2024 03:22:24   

Could you please try assigning an alias to the first projected field in both queries?

select new { Number = eec.Number...

ladamik
User
Posts: 2
Joined: 15-Feb-2024
# Posted on: 16-Feb-2024 07:59:34   

I added an alias but it still doesn't work and I get the same error

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39712
Joined: 17-Aug-2003
# Posted on: 16-Feb-2024 08:24:11   

The BUG description suggests the code ran into a situation which shouldn't happen. We'll look into it

Frans Bouma | Lead developer LLBLGen Pro
Otis avatar
Otis
LLBLGen Pro Team
Posts: 39712
Joined: 17-Aug-2003
# Posted on: 16-Feb-2024 09:34:45   

The issue is caused by C# compiling the join + select into a .Join(...., projection) so q2 isn't processed further to a queryexpression but is still a tree, at that point. The workaround is to swap the queries, so

var q3 = q2.Union(q1);

We'll look into fixing this.

Frans Bouma | Lead developer LLBLGen Pro
Otis avatar
Otis
LLBLGen Pro Team
Posts: 39712
Joined: 17-Aug-2003
# Posted on: 16-Feb-2024 10:43:59   

Fixed in hotfix builds 5.10.3 and 5.11.1

Frans Bouma | Lead developer LLBLGen Pro