Continuing from https://www.llblgen.com/TinyForum/Messages.aspx?ThreadID=27116

I found another wrong SQL in the following case.

I'm using v5.6.3RTM LLBLGen runtime framework Adapter

I have this small table UitgaveMagazijnMutatie with the following fields UitgaveId int32 MagazijnId int32 Datum DateTime Aantal int32 Vvp Decimal

I need an aggregate of this to sum up a count (stock on date) and amount, so pseudo: UitgaveId int32 MagazijnId int32 Voorraad int32 : sum of Aantal Waarde Decimal : sum of (Aantal * Vvp)

        public async Task<List<UitgaveVoorraadOpPeildatum>> GetUitgaveMagazijnVoorradenOpPeildatumAsync()
        {
            using var adapter  = _dataAdapterFactory.CreateDataAccessAdapter();
            var    metaData = new LinqMetaData(adapter);
        
            var q = metaData.UitgaveMagazijnMutatie
                
                .GroupBy(x => new {x.UitgaveId, x.MagazijnId})
                .Select(x => new UitgaveVoorraadOpPeildatum
                {
                    UitgaveId  = x.Key.UitgaveId,
                    Voorraad   = x.Sum(umm => umm.Aantal),
                    DoubleWaarde     = x.Sum(umm => umm.Aantal * Convert.ToDouble(umm.Vvp) ),
                    MagazijnId = x.Key.MagazijnId,
                });
            return await q.ToListAsync();
        }

It generates the following SQL:

SELECT [LPA_L1].[UitgaveId],
       [LPA_L1].[LPAV_]  AS [Voorraad],
       [LPA_L1].[LPAV_1] AS [DoubleWaarde],
       [LPA_L1].[MagazijnId]
FROM   (SELECT [LPA_L3].[UitgaveId],
               [LPA_L3].[MagazijnId],
               SUM([LPA_L3].[LPAV_]) AS [LPAV_],
               SUM([LPA_L3].[LPAV_]) AS [LPAV_1]
        FROM   (SELECT [LPLA_1].[UitgaveID]                          AS [UitgaveId],
                       [LPLA_1].[MagazijnID]                            AS [MagazijnId],
                       (CDbl([LPLA_1].[Aantal]) * CDbl([LPLA_1].[VVP])) AS [LPAV_]
                FROM   [UitgaveMagazijnMutatie] [LPLA_1]) [LPA_L3]
        GROUP  BY [LPA_L3].[UitgaveId],
                  [LPA_L3].[MagazijnId]) [LPA_L1] 

you can see it generates an alias for the expression (Aantal * VVP) named LPAV_ In the outer SELECT it gets the same LPAV_ in both the sum to LPAV_ and LPAV_1. Then Voorraad becomes LPAV_ and DoubleWaarde becomes LPAV_1, but then both have the same value. Expected was that Voorraad would have been Sum of Aantal instead of Sum of (Aantal*VVP)

Yeah, I noticed this on the original thread.

I can reproduce it using LLBLGen Pro v.5.7

Seems specific to Linq, in the meantime could you please try QuerySpec?

Something like the following should work:

var qf = new QueryFactory();
var q = qf.Create()
    .Select(UitgaveMagazijnMutatieFields.UitgaveId, UitgaveMagazijnMutatieFields.MagazijnId,
      (UitgaveMagazijnMutatieFields.Aantal * UitgaveMagazijnMutatieFields.Vvp).Sum().As("Waarde"), UitgaveMagazijnMutatieFields.Aantal.Sum())
    .GroupBy(UitgaveMagazijnMutatieFields.UitgaveId, UitgaveMagazijnMutatieFields.MagazijnId)
    .Where(UitgaveMagazijnMutatieFields.Datum == DateTime.Now);

var results = new DataTable();

adapter.FetchAsDataTable(q, results);

I have already have a temporary work around with the anonymous projection, but thanks anyway.

In my source code I try to use only one variant of fetching entities (linq prefetch), so I dont need any mental load deciding when to use linq prefetch or queryspec.

FYI, I prefer linq prefetch because it's short and has compile time checking of members. I might be loosing some expressiveness compared to queryspec, but in 95% of my cases I wouldn't need it.

I think the main issue is here that you project the group by to an anonymous type which results in a different tree than when you would use the group by into syntax. I'll see if that's the case. With groupby and linq it's very tricky to convert these to the right query as the linq expression tree isn't 1:1 convertible to SQL, so we have to interpret what's the intent and then rebuild it in a SQL query. My guess is that this interpretation step makes a mistake somewhere, likely due to the slightly different tree shapes it expects to see.

Looking more closely I think it's as stupid as seeing the umm.Aantal field and as it's already in the projection it will reference that already while one has an expression assigned to it and the other doesn't.

We'll look into it.

Regarding this issue: it's not what I previously assumed. We're still investigating this. (this isn't an ms access specific issue btw.)

All fixed! The where clause, the wrong referencing in the aggregates and the CDec issue.

See the new hotfix builds for 5.6.4 and 5.7.1 which are now available on our website and nuget.

thank you Frans!