Hi,

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 before/on some date, so pseudo: UitgaveId int32 MagazijnId int32 Voorraad int32 : sum of Aantal Waarde Decimal : sum of (Aantal * Vvp) where Datum<= [some date]

This is the code I use to fetch the results

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

This is the SQL it generates:

SELECT [LPA_L1].[UitgaveId],
       [LPA_L1].[LPAV_]  AS [Voorraad],
       [LPA_L1].[LPAV_1] AS [Waarde],
       [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],
                       (CDec([LPLA_1].[Aantal]) * [LPLA_1].[VVP]) AS [LPAV_]
                FROM   [UitgaveMagazijnMutatie] [LPLA_1]) [LPA_L3]
        WHERE  ((([LPA_L3].[Datum] <= @p1)))
        GROUP  BY [LPA_L3].[UitgaveId],
                  [LPA_L3].[MagazijnId]) [LPA_L1] 

There are two errors in this: 1. Function CDec in (CDec([LPLA_1].[Aantal]) * [LPLA_1].[VVP]) AS [LPAV_] 2. Where on Datum against the wrong alias: WHERE ((([LPA_L3].[Datum] <= @p1)))

1. this function does not work under Access Queries, it needs a second parameter, and then still it has a confirmed bug source:

https://stackoverflow.com/questions/31327402/cdec-in-access-sql-is-not-behaving-the-same-as-when-used-from-access-vba

Solution: it might be that the whole casting is not necessary, so you might leave out the CDec altogether. It might also be to have a convert to CDbl but it slows down the query. I have not noticed anyone having this issue on your support forum so I guess noone else is using this case.

1. The Where clause is going against alias LPA_L3 while this sub query does not fetch the Datum field, so it's out of scope of LPA_L3. But could be run on LPLA_1.

Workarounds I have created temporary work arounds for both issues 1 and 2. 1. Use Double instead of Decimal. I add a field DoubleWaarde (to be clear) and convert Vvp to double (Aantal is not needed but is convert in the SQL as well). 2. If I create an intermediate anonymous projection to force the where and the expression early, then It works as you can see below:

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

the resulting SQL works and is

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].[Voorraad])  AS [LPAV_],
               SUM([LPA_L3].[DoubleWaarde]) AS [LPAV_1]
        FROM   (SELECT [LPLA_1].[UitgaveID]                          AS [UitgaveId],
                       [LPLA_1].[MagazijnID]                            AS [MagazijnId],
                       [LPLA_1].[Aantal]                                AS [Voorraad],
                       (CDbl([LPLA_1].[Aantal]) * CDbl([LPLA_1].[VVP])) AS [DoubleWaarde]
                FROM   [UitgaveMagazijnMutatie] [LPLA_1]
                WHERE  ((([LPLA_1].[Datum] <= '2018-01-01T00:00:00')))) [LPA_L3]
        GROUP  BY [LPA_L3].[UitgaveId],
                  [LPA_L3].[MagazijnId]) [LPA_L1] 

You could try this yourself with some table in Northwind if necessary.

I'd gladly like to hear your thoughts.

Thanks

Since you have mentioned Northwind, have you tried this on SQL Server, and got the same issues, regardless of the CDec syntax?

Sorry, no. I will not sit on your chair anymore You could try with your preferred (access) db.

I was just asking, coz you said test it on Northwind. So which version of Access are you using, as the stackoverflow thread you have mentioned is 5 years old.

I'm using the Access 2000 database format. But as far as I know you don't use any version distinctions in your code. Not that I can find in the drivers source code you supply, anyway. And the CDec function is 'defect' like this in queries from Acc2000-Acc2019

I couldn't reproduce the CDec. The generated SQL as I see it on the output window doesn't have any kind of casting.

I'm using OleDB ACE driver v12, and hitting an MS Access from Office 365. The field is a Currency field on the database, and it's clearly mapped to a decimal field, in LLBLGen Pro Designer.

As for the other aliasing issues, as said on the other thread, I can reproduce them with Linq, but you can work around it, using QuerySpec.

Walaa wrote:

I couldn't reproduce the CDec. The generated SQL as I see it on the output window doesn't have any kind of casting.

I'm using OleDB ACE driver v12, and hitting an MS Access from Office 365. The field is a Currency field on the database, and it's clearly mapped to a decimal field, in LLBLGen Pro Designer.

If the resulting type is decimal and the source type is decimal there shouldn't be any casting, but the expression also uses an Int32 (Aantal) and that however should result in a CDec cast. In any case, this is what I expect Llblgen/functionmapper it to do. Or else I would like to know how to get rid of the casting altogether (if it's not needed).

We'll look into both issues.

Regarding 1: it appears to be a known bug in MS Access and isn't going to be fixed. The workaround is a custom function but we can't use that. For you the workaround likely is to use a Currency typed field and not Double. Using floating point types for currencies is asking for trouble as they're not precise enough. When I remove the CDec function mapping the query will execute without the CDec conversion and in this case work (at least not give the error, it'll end in tears elsewhere).

So we'll remove the CDec function mapping and will make queries using that at least work fine.

Regarding 2: we'll look into that combined with the other linq issues you reported as they all occur in this same query.

Regarding 1: it appears to be a known bug in MS Access and isn't going to be fixed. The workaround is a custom function but we can't use that. For you the workaround likely is to use a Currency typed field and not Double. Using floating point types for currencies is asking for trouble as they're not precise enough.

The database type of Vvp is currency. The destination type of Waarde is decimal. There are no doubles in this case in the Db. I dont know how to force the CCur here. Where do I put what type in the IQueryable to force the CCur? Convert.To...?

So we'll remove the CDec function mapping and will make queries using that at least work fine.

Thank you

Then I misunderstood you, I thought you had a decimal field and changed it to double to make the query work. In any case, the CDec removal makes the query work with the vanilla types.

Regarding 2: we've fixed this issue. The root cause is the folding of the source into a derived table, due to the multiply, and the field in the where is re-aliased properly but as it's not part of the projection it's not going to work. This is a general issue which affects all databases.

This will be available in the hotfix builds for 5.6.4 and 5.7.1 released (hopefully) later today. I'll now close this thread, as the other issue is in another thread. So you can consider this fixed.

Thanks Frans