LLBLGen pro 5.5 (5.5.1) RTM adapter using MDB as database

I have a table with only int fields (in MDB they're call long integers). When I issue an aggregated linq on it with a boolean expression it creates the wrong SQL. This is the Linq expression:

        public async Task<List<OmzetUitgaveRelatieReferentieOmzetDto>> GetOmzetUitgaveRelatieReferentieOmzettenAsync(IEnumerable<int> uitgaveIds, IEnumerable<int> verkoopsoortIds)
        {
            using (var adapter = _turnoverAdapterFactory.CreateDataAccessAdapter())
            {
                var s = new DomainTurnover.Linq.LinqMetaData(adapter).Omzet
                    .Where(
                        x =>
                            uitgaveIds.Contains(x.UitgaveId) &&
                            verkoopsoortIds.Contains(x.VerkoopsoortId));
                var g = from omzet in s
                        group omzet by
                            new
                            {
                                omzet.UitgaveId,
                                omzet.RelatieId,
                                omzet.Dag,
                                omzet.VerkoopsoortId,
                                IsRetour = omzet.Aantal < 0
                            };
                var q = from uitgaveGroep in g
                        select new OmzetUitgaveRelatieReferentieOmzetDto
                        {
                            UitgaveId = uitgaveGroep.Key.UitgaveId,
                            RelatieId = uitgaveGroep.Key.RelatieId,
                            Dag = uitgaveGroep.Key.Dag,
                            VerkoopsoortId = uitgaveGroep.Key.VerkoopsoortId,
                            IsRetour = uitgaveGroep.Key.IsRetour,
                            Aantal = uitgaveGroep.Sum(x => x.Aantal),
                            BrutoExcl = uitgaveGroep.Sum(x => x.BrutoExcl),
                            NettoExcl = uitgaveGroep.Sum(x => x.NettoExcl)
                        };
                return await q.ToListAsync();
            }
        }

this is the generated SQL (notice the fifth row, it does not reference the sub query, but again creates the alias):

SELECT [LPA_L1].[UitgaveId],
       [LPA_L1].[RelatieId],
       [LPA_L1].[Dag],
       [LPA_L1].[VerkoopsoortId],
       IIF(([LPA_L1].[Aantal] < 0),
           1,
           0)           AS [IsRetour], <---ERROR: [LPA_L1].[Aantal] does not exist in sub query, [LPA_L1].[IsRetour] does
       [LPA_L1].[LPAV_]  AS [Aantal],
       [LPA_L1].[LPAV_1] AS [BrutoExcl],
       [LPA_L1].[LPAV_2] AS [NettoExcl]
FROM   (SELECT [LPLA_1].[UitgaveID]   AS [UitgaveId],
               [LPLA_1].[RelatieID]   AS [RelatieId],
               [LPLA_1].[Dag],
               [LPLA_1].[VerkoopsoortID] AS [VerkoopsoortId],
               IIF(([LPLA_1].[Aantal] < 0),
                   1,
                   0)                   AS [IsRetour],
               SUM([LPLA_1].[Aantal])   AS [LPAV_],
               SUM([LPLA_1].[BrutoExcl]) AS [LPAV_1],
               SUM([LPLA_1].[NettoExcl]) AS [LPAV_2]
        FROM   [Omzet] [LPLA_1]
        WHERE  (((([LPLA_1].[UitgaveID] IN (1, 2, 3, 4,
                                            5, 6, 7, 8,
                                            9, 10, 11, 12,
                                            13, 3686, 14, 15,
                                            16, 17, 18, 19,
                                            9783, 20, 21, 22,
                                            23, 24, 12264, 25,
                                            26, 27, 28, 29,
                                            30, 31, 32, 33,
                                            34, 35, 36, 37,
                                            38, 39, 40, 41,
                                            42, 43, 44, 45,
                                            46, 47))
              AND ([LPLA_1].[VerkoopsoortID] IN (1, 2)))))
        GROUP  BY [LPLA_1].[UitgaveID],
                  [LPLA_1].[RelatieID],
                  [LPLA_1].[Dag],
                  [LPLA_1].[VerkoopsoortID],
                  IIF(([LPLA_1].[Aantal] < 0),
                      1,
                      0)) [LPA_L1] 

but it should be:

SELECT [LPA_L1].[UitgaveId],
       [LPA_L1].[RelatieId],
       [LPA_L1].[Dag],
       [LPA_L1].[VerkoopsoortId],
       [LPA_L1].[IsRetour],  <---SHOULD BE
       [LPA_L1].[LPAV_]  AS [Aantal],
       [LPA_L1].[LPAV_1] AS [BrutoExcl],
       [LPA_L1].[LPAV_2] AS [NettoExcl]
FROM   (SELECT [LPLA_1].[UitgaveID]   AS [UitgaveId],
               [LPLA_1].[RelatieID]   AS [RelatieId],
               [LPLA_1].[Dag],
               [LPLA_1].[VerkoopsoortID] AS [VerkoopsoortId],
               IIF(([LPLA_1].[Aantal] < 0),
                   1,
                   0)                   AS [IsRetour],
               SUM([LPLA_1].[Aantal])   AS [LPAV_],
               SUM([LPLA_1].[BrutoExcl]) AS [LPAV_1],
               SUM([LPLA_1].[NettoExcl]) AS [LPAV_2]
        FROM   [Omzet] [LPLA_1]
        WHERE  (((([LPLA_1].[UitgaveID] IN (1, 2, 3, 4,
                                            5, 6, 7, 8,
                                            9, 10, 11, 12,
                                            13, 3686, 14, 15,
                                            16, 17, 18, 19,
                                            9783, 20, 21, 22,
                                            23, 24, 12264, 25,
                                            26, 27, 28, 29,
                                            30, 31, 32, 33,
                                            34, 35, 36, 37,
                                            38, 39, 40, 41,
                                            42, 43, 44, 45,
                                            46, 47))
              AND ([LPLA_1].[VerkoopsoortID] IN (1, 2)))))
        GROUP  BY [LPLA_1].[UitgaveID],
                  [LPLA_1].[RelatieID],
                  [LPLA_1].[Dag],
                  [LPLA_1].[VerkoopsoortID],
                  IIF(([LPLA_1].[Aantal] < 0),
                      1,
                      0)) [LPA_L1] 

EDIT: of course the stacktrace:

ORMQueryExecutionException
Exception type: System.Data.OleDb.OleDbException
Message: U probeert een query uit te voeren waarbij de opgegeven expressie IIf([LPLA_1].[Aantal]<@p2,1,0) geen deel uitmaakt van een statistische functie.

Stack-trace:
   bij System.Data.OleDb.OleDbCommand.ExecuteCommandTextErrorHandling(OleDbHResult hr)
   bij System.Data.OleDb.OleDbCommand.ExecuteCommandTextForSingleResult(tagDBPARAMS dbParams, Object& executeResult)
   bij System.Data.OleDb.OleDbCommand.ExecuteCommandText(Object& executeResult)
   bij System.Data.OleDb.OleDbCommand.ExecuteCommand(CommandBehavior behavior, Object& executeResult)
   bij System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior behavior, String method)
   bij System.Data.OleDb.OleDbCommand.ExecuteReader(CommandBehavior behavior)
   bij System.Data.OleDb.OleDbCommand.ExecuteDbDataReader(CommandBehavior behavior)
   bij System.Data.Common.DbCommand.ExecuteDbDataReaderAsync(CommandBehavior behavior, CancellationToken cancellationToken)
--- Einde van stacktracering vanaf vorige locatie waar uitzondering is opgetreden ---
   bij System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
   bij System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   bij SD.Tools.OrmProfiler.Interceptor.ProfilerDbCommand.<ExecuteDbDataReaderAsync>d__13.MoveNext() in C:\Myprojects\VS.NET Projects\OrmProfiler\SD.Tools.OrmProfiler.Interceptor\ProfilerDbCommand.cs:regel 130
   at System.Runtime.CompilerServices.AsyncTaskMethodBuilder`1.Start(TStateMachine&)
   at System.Data.Common.DbCommand.ExecuteReaderAsync(CommandBehavior, CancellationToken)
   at SD.LLBLGen.Pro.ORMSupportClasses.RetrievalQuery+<>c__DisplayClass16_0.<ExecuteAsync>b__0()
   at SD.LLBLGen.Pro.ORMSupportClasses.RetrievalQuery.TagAndExecuteCommand(Func`1)
   at SD.LLBLGen.Pro.ORMSupportClasses.RetrievalQuery+<ExecuteAsync>d__16.MoveNext()
   at System.Runtime.CompilerServices.AsyncTaskMethodBuilder`1.Start(TStateMachine&)
   at SD.LLBLGen.Pro.ORMSupportClasses.RetrievalQuery.ExecuteAsync(CommandBehavior, CancellationToken)
   at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterCore+<FetchDataReaderAsync>d__171.MoveNext()
   at System.Runtime.CompilerServices.AsyncTaskMethodBuilder`1.Start(TStateMachine&)
   at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterCore.FetchDataReaderAsync(IRetrievalQuery, CommandBehavior, CancellationToken)
   at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterCore+<FetchProjectionAsync>d__214.MoveNext()
   at System.Runtime.CompilerServices.AsyncTaskMethodBuilder.Start(TStateMachine&)
   at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterCore.FetchProjectionAsync(List`1, IGeneralDataProjector, IRetrievalQuery, Boolean, Boolean, Dictionary`2, CancellationToken)
   at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterCore+<FetchProjectionAsync>d__173.MoveNext()
   at System.Runtime.CompilerServices.AsyncTaskMethodBuilder.Start(TStateMachine&)
   at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterCore.FetchProjectionAsync(List`1, IGeneralDataProjector, QueryParameters, CancellationToken)
   at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase+<>c__DisplayClass31_0.<FetchProjectionAsync>b__0()
   at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase+<ExecuteWithActiveRecoveryStrategyAsync>d__48.MoveNext()
   at System.Runtime.CompilerServices.AsyncTaskMethodBuilder.Start(TStateMachine&)
   at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.ExecuteWithActiveRecoveryStrategyAsync(Func`1, CancellationToken)
   at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.FetchProjectionAsync(List`1, IGeneralDataProjector, QueryParameters, CancellationToken)
   at SD.LLBLGen.Pro.LinqSupportClasses.LLBLGenProProvider2+<ExecuteValueListProjectionAsync>d__7.MoveNext()
   at System.Runtime.CompilerServices.AsyncTaskMethodBuilder`1.Start(TStateMachine&)
   at SD.LLBLGen.Pro.LinqSupportClasses.LLBLGenProProvider2.ExecuteValueListProjectionAsync(QueryExpression, CancellationToken)
   at SD.LLBLGen.Pro.LinqSupportClasses.LLBLGenProProviderBase+<ExecuteExpressionAsync>d__33.MoveNext()
   at System.Runtime.CompilerServices.AsyncTaskMethodBuilder`1.Start(TStateMachine&)
   at SD.LLBLGen.Pro.LinqSupportClasses.LLBLGenProProviderBase.ExecuteExpressionAsync(Expression, Type, Boolean, CancellationToken)
   at SD.LLBLGen.Pro.LinqSupportClasses.LLBLGenProProviderBase+<PerformExecuteAsync>d__30.MoveNext()
   at System.Runtime.CompilerServices.AsyncTaskMethodBuilder`1.Start(TStateMachine&)
   at SD.LLBLGen.Pro.LinqSupportClasses.LLBLGenProProviderBase.PerformExecuteAsync(Expression, Type, Boolean, CancellationToken)
   at SD.LLBLGen.Pro.LinqSupportClasses.LLBLGenProProviderBase+<ExecuteAsync>d__29`1.MoveNext()
   at System.Runtime.CompilerServices.AsyncTaskMethodBuilder`1.Start(TStateMachine&)
   at SD.LLBLGen.Pro.LinqSupportClasses.LLBLGenProProviderBase.ExecuteAsync(Expression, CancellationToken)
   at SD.LLBLGen.Pro.LinqSupportClasses.LLBLGenProQuery`1+<ExecuteAsync>d__14`1.MoveNext()
   at System.Runtime.CompilerServices.AsyncTaskMethodBuilder`1.Start(TStateMachine&)
   at SD.LLBLGen.Pro.LinqSupportClasses.LLBLGenProQuery`1.ExecuteAsync(CancellationToken)
   at SD.LLBLGen.Pro.LinqSupportClasses.LLBLGenProQuery`1.SD.LLBLGen.Pro.LinqSupportClasses.ILLBLGenProQuery.ExecuteAsync(CancellationToken)
   at SD.LLBLGen.Pro.LinqSupportClasses.QueryableExtensionMethods.ExecuteAsync(IQueryable, CancellationToken)
   at SD.LLBLGen.Pro.LinqSupportClasses.QueryableExtensionMethods+<ToListAsync>d__3`1.MoveNext()
   at System.Runtime.CompilerServices.AsyncTaskMethodBuilder`1.Start(TStateMachine&)
   at SD.LLBLGen.Pro.LinqSupportClasses.QueryableExtensionMethods.ToListAsync(IQueryable`1, CancellationToken)
   at SD.LLBLGen.Pro.LinqSupportClasses.QueryableExtensionMethods.ToListAsync(IQueryable`1)
   at VA.Pac.Application.Logistiek.Repositories.OmzetRepository+<GetOmzetUitgaveRelatieReferentieOmzettenAsync>d__9.MoveNext() in C:\ws\PAC3\VA.Pac\VA.Pac.Application\Logistiek\Repositories\OmzetRepository.cs:line 242
   at System.Runtime.CompilerServices.AsyncTaskMethodBuilder`1.Start(TStateMachine&)

Please cut down the query to the bare minimum so we can try to reproduce it. It's way too big to debug it. Also provide the relevant info regarding relevant fields.

        public async Task<List<UitgaveRelatieReferentieDagOmzetDto>> GetOmzetUitgaveRelatieReferentieOmzettenAsync()
        {
            using (var adapter = _turnoverAdapterFactory.CreateDataAccessAdapter())
            {
                var s = new DomainTurnover.Linq.LinqMetaData(adapter).Omzet;

                var g = from omzet in s
                        group omzet by
                            new
                            {
                                IsRetour = omzet.Aantal < 0
                            };
                var q = from uitgaveGroep in g
                        select new UitgaveRelatieReferentieDagOmzetDto
                        {
                            IsRetour = uitgaveGroep.Key.IsRetour,
                            Aantal = uitgaveGroep.Sum(x => x.Aantal),
                        };
                return await q.ToListAsync();
            }
        }

wrong SQL

SELECT IIF(([LPA_L1].[Aantal] < 0),
           1,
           0)          AS [IsRetour],
       [LPA_L1].[LPAV_] AS [Aantal]
FROM   (SELECT IIF(([LPLA_1].[Aantal] < 0),
                   1,
                   0)                AS [IsRetour],
               SUM([LPLA_1].[Aantal]) AS [LPAV_]
        FROM   [Omzet] [LPLA_1]
        GROUP  BY IIF(([LPLA_1].[Aantal] < 0),
                      1,
                      0)) [LPA_L1] 

right SQL

SELECT [LPA_L1].IsRetour  AS [IsRetour],
       [LPA_L1].[LPAV_] AS [Aantal]
FROM   (SELECT IIF(([LPLA_1].[Aantal] < 0),
                   1,
                   0)                AS [IsRetour],
               SUM([LPLA_1].[Aantal]) AS [LPAV_]
        FROM   [Omzet] [LPLA_1]
        GROUP  BY IIF(([LPLA_1].[Aantal] < 0),
                      1,
                      0)) [LPA_L1] 

Relevant field info: Field Aantal is 'Long integer' size 4 (mdb) (same as int32). What other 'relevant info' do you need/mean?

Reproduced, on SQL Server Northwind db.

var q = from product in metadata.Product
    group product by new { IsActive = product.Discontinued == false } into g
    select new { IsActive = g.Key.IsActive, Units = g.Sum(x => x.UnitsInStock) };

I think it's caused by the wrapping due to the aggregate where the grouped field is cloned, but not stripped of expression/aggregate. At least that's what I'm thinking, will look into it. I suspected the 2 sum's being the issue (as with multiple aggregate functions the inner query is 'folded' into a subquery which requires derived table targeting fields on the outer projection) but it's simpler.

Bug is specific for grouping on a predicate result. It's skipped in the conversion of the anonymous type to a derived table targeting element.

Looks like this apparently works on ms access but not on sqlserver. After fixing this issue, I get:

SELECT [LPA_L1].[IsActive],
       [LPA_L1].[LPAV_] AS [Units]
FROM   (SELECT CASE
                 WHEN ([LPLA_1].[UnitsInStock] > 5 /* @p1 */) THEN 1
                 ELSE 0
               END                        AS [IsActive],
               SUM([LPLA_1].[UnitsInStock]) AS [LPAV_]
        FROM   [Northwind].[dbo].[Products] [LPLA_1]
        GROUP  BY CASE
                    WHEN ([LPLA_1].[UnitsInStock] > 5 /* @p2 */) THEN 1
                    ELSE 0
                  END) [LPA_L1]

(Altered the predicate a bit)

which is invalid sql as it doesn't see 'UnitsInStock' as being part of an aggregate...

The right sql is:

SELECT [LPA_L1].[IsActive],
       Sum([LPA_L1].[UnitsInStock])
FROM   (SELECT CASE
                 WHEN ([LPLA_1].[UnitsInStock] > 5) THEN 1
                 ELSE 0
               END                        AS [IsActive],
               [LPLA_1].[UnitsInStock]
        FROM   [Northwind].[dbo].[Products] [LPLA_1]
        ) [LPA_L1] 
group by IsActive

I.o.w. it has to fold the query of the group by into a derived table and group on these values.

We have code in place to do this for other situations, will check if I can trigger that code for this situation as well, as otherwise this fix is of no use.

The workaround is:

public async Task<List<UitgaveRelatieReferentieDagOmzetDto>> GetOmzetUitgaveRelatieReferentieOmzettenAsync()
{
    using (var adapter = _turnoverAdapterFactory.CreateDataAccessAdapter())
    {
        var s = new DomainTurnover.Linq.LinqMetaData(adapter).Omzet;

        var g1 = from omzet in s
                 select new { IsRetour = omzet.Aantal < 0, A = omzet.Aantal };
        var g = select x from g1
                group x by x.IsRetour into g
                select new { IsRetour = g.Key.IsRetour, Aantal = g.Sum(y=>y.A)};
        return await q.ToListAsync();
    }
}

The critical part here is that you do the folding of the query to group on yourself. This should also work with the current runtime btw. I know this sucks, but it's what currently works. The idea is that you first gather the data you'll work on in a separate query, so without grouping. Then you apply the group by and the projection.

Equivalent:

[Test]
public void GroupByOnFunctionCallResultTest2()
{
    using(var adapter = new DataAccessAdapter())
    {
        var metaData = new LinqMetaData(adapter);
        var q1 = from product in metaData.Product
                select new {IsActive = product.UnitsInStock.Value > 5, UnitsInStock = product.UnitsInStock};
        var q = from x in q1
                group x by x.IsActive into g
                select new {g.Key, Units = g.Sum(y=>y.UnitsInStock)};
        var result = q.ToList();
    }
}

Results in:

SELECT [LPA_L1].[IsActive] AS [Key],
       [LPA_L1].[LPAV_] AS [Units]
FROM   (SELECT [LPA_L3].[IsActive],
               SUM([LPA_L3].[UnitsInStock]) AS [LPAV_]
        FROM   (SELECT CASE
                         WHEN ([LPLA_1].[UnitsInStock] > @p1) THEN 1
                         ELSE 0
                       END AS [IsActive],
                       [LPLA_1].[UnitsInStock]
                FROM   [Northwind].[dbo].[Products] [LPLA_1]) [LPA_L3]
        GROUP  BY [LPA_L3].[IsActive]) [LPA_L1] 

We do have a folding method, but it's not as simple as it looks, as we have to augment the groupby key as well. We don't know if this is feasible with the code we have, as linq providers aren't simple so changing something at spot X might keel over a tree of use cases you didn't expect. (yes it's that brittle, sadly).

The workaround I gave above is also more closer to the sql you get. But I do understand that it might not be possible to do in your actual use case. Therefore we hope we can build a code path that will deal with this in the runtime.

Hi Frans,

Thanks for the insights. It's doable that way. I have got it working. Just need to select all columns upfront in the anonymous projection. Makes it a bit longer, but gets the job done.

Though, have to write this down for future

Keep me posted about any update on this.

best regards!

Glad you can work with the workaround

We've decided to postpone the change. We do think we have found a way to do it, but as we don't know if it will work in all cases, we don't want to risk releasing a fix which might break a lot of queries. The main issue is that the group by in itself has to be converted into a group by on a derived table with as projection the grouped fields but also that the fields used later on have to be present in the derived table we're creating. The only solution for that is to add all fields of the original source (here the entity).

this can lead to issues in other edge cases (yeah linq is never dull ) so we have to analyze this more deeply and work on a solid generic way to do this (as in: detect we're in this edge case, then apply the tree rewriting, and only then).

So we'll look at implementing this in 5.7 (or later, if time is short). It's an edge case that's likely not touched a lot (you're the first in 12 years reporting this), so priority is lower. We did make a small change so it at least generates sql that matches the sql you posted as 'correct' which might work on access but not on other databases. This will be available in the next runtime build. As it doesn't affect your situation (you still need the workaround) we're not releasing a new hotfix with this build at the moment, but it will be automatically be available when a new hotfix/ RTM is released.