Lambda query with LLBLGen.Linq.Prefetch does not translate alias

Posts   
 
    
Puser
User
Posts: 228
Joined: 20-Sep-2012
# Posted on: 09-Jun-2016 16:59:29   

Hi,

I have some Linq code that does not translate an alias. Am I doing something wrong here?

I'm using:

LLBLGen 5.0 (5.0.1) RTM LLBLGen.Linq.Prefetch 1.0.6 LLBLGen Runtime Framework

I get this error in the following code:

An exception was caught during the execution of a retrieval query: Waarden voor een of meer vereiste parameters ontbreken.. Check InnerException, QueryExecuted and Parameters of this exception to examine the cause of this exception.

this is the code i'm using where the predicate consists of DrukId=5860 (any number):


        private DrukEntity GetAggregate(Expression<Func<DrukEntity, bool>> predicate)
        {
            using (var adapter = _dataAdapterFactory.CreateDataAccessAdapter())
            {
                var metaData = new LinqMetaData(adapter);

                var query = metaData.Druk.With(
                    d => d.Calculaties.Where(c => [b]c.CalcMomentId == d.CalcMomentId[/b])
                    );
                return query.FirstOrDefault(predicate);
}
}

the query that has an error (not a resolved alias)


SELECT [Calc].[AantalSom],
       [Calc].[AfzetPrognose12Maanden],
       [Calc].[AfzetPrognoseAllTime],
       [Calc].[AfzetPrognoseEersteOplage],
       [Calc].[AltCalcMomentID]         AS [AltCalcMomentId],
       [Calc].[AutoStaffelBerekening],
       [Calc].[BBR]                     AS [Bbr],
       [Calc].[BrutoBijdrageSom],
       [Calc].[BrutoInclSom],
       [Calc].[BtwDeelLaag],
       [Calc].[BtwIDHoog]                 AS [BtwIdhoog],
       [Calc].[BtwIDLaag]                 AS [BtwIdlaag],
       [Calc].[BtwNettoSom],
       [Calc].[BtwPerc],
       [Calc].[CalcID]                   AS [CalcId],
       [Calc].[CalcMomentID]               AS [CalcMomentId],
       [Calc].[CalcScenarioID]           AS [CalcScenarioId],
       [Calc].[DrukID]                   AS [DrukId],
       [Calc].[ExtraStukKostenVariabel],
       [Calc].[FormaatID]                 AS [FormaatId],
       [Calc].[FormaatNotities],
       [Calc].[Gewicht],
       [Calc].[Herdruk],
       [Calc].[Hoofdoplage],
       [Calc].[*KOSTEN*]                   AS [Kosten],
       [Calc].[KostenTotaal],
       [Calc].[KostenVariabel],
       [Calc].[KostenVast],
       [Calc].[Kostprijs],
       [Calc].[KostprijsSom],
       [Calc].[NettoExclSom],
       [Calc].[OUDExtraStukKostenVariabel] AS [OudextraStukKostenVariabel],
       [Calc].[OverigeVoorzieningen],
       [Calc].[OverigeVoorzieningenPerc],
       [Calc].[PapiersoortID]             AS [PapiersoortId],
       [Calc].[PapiersoortNotities],
       [Calc].[Presenten],
       [Calc].[ProductieOmvang],
       [Calc].[PW]                       AS [Pw],
       [Calc].[RabatSom],
       [Calc].[RoyaltyPercentage],
       [Calc].[RoyaltyPercentageVast],
       [Calc].[RoyaltySom],
       [Calc].[RoyaltyVoorschot],
       [Calc].[StaffelID]                 AS [StaffelId],
       [Calc].[SubCalcnr],
       [Calc].[ToeslagDistributie],
       [Calc].[ToeslagDistributiePerc],
       [Calc].[ToeslagOverhead],
       [Calc].[ToeslagOverheadPerc],
       [Calc].[ToeslagPromotie],
       [Calc].[ToeslagPromotiePerc],
       [Calc].[*VERKOOP*]                 AS [Verkoop],
       [Calc].[VerkoopHerrekenen],
       [Calc].[Verschijningsdatum],
       [Calc].[VerschijningsvormID]     AS [VerschijningsvormId],
       [Calc].[VerschijningsvormNotities]
FROM   [Calc]
WHERE  ((([Calc].[DrukID] = @p1))
    AND ([b][Calc].[CalcMomentID] = [Druk].[CalcMomentID][/b])) 
5860 /* @p1 */

In the code above you see in the second last line:

([Calc].[CalcMomentID] = [Druk].[CalcMomentID]) 

this is where [Druk].[CalcMomentID] should have been replaced by the real CalcMomentID from table Druk for this query has run before, see List below:

SELECT TOP 1 [LPA_L1].[Afgesloten],
             [LPA_L1].[AutoPresentenVanafVolgordeID]     AS [AutoPresentenVanafVolgordeId],
             [LPA_L1].[BestmateExportDatum],
             [LPA_L1].[BestmateExportVanafVolgordeID]   AS [BestmateExportVanafVolgordeId],
             [LPA_L1].[BestmateGeexporteerd],
             [LPA_L1].[CalcMomentID]                     AS [CalcMomentId],
             [LPA_L1].[CalcScenarioID]                 AS [CalcScenarioId],
             [LPA_L1].[DatumAfgesloten],
             [LPA_L1].[DrukAfleveringScenarioID]         AS [DrukAfleveringScenarioId],
            [b] [LPA_L1].[DrukID]                          AS [DrukId][/b],
             [LPA_L1].[Druknr],
             [LPA_L1].[DruknrToevoeging],
             [LPA_L1].[DrukOpdrachtDatum],
             [LPA_L1].[DruksoortID]                   AS [DruksoortId],
             [LPA_L1].[DrukTitel],
             [LPA_L1].[FATransactieID]                 AS [FatransactieId],
             [LPA_L1].[FAVerwerkingID]                 AS [FaverwerkingId],
             [LPA_L1].[FiatScenarioID]                 AS [FiatScenarioId],
             [LPA_L1].[GewichtInGram],
             [LPA_L1].[IsbnMeldingVolgordeID]           AS [IsbnMeldingVolgordeId],
             [LPA_L1].[IsbnVerplichtVolgordeID]       AS [IsbnVerplichtVolgordeId],
             [LPA_L1].[KenmerkScenarioID]               AS [KenmerkScenarioId],
             [LPA_L1].[KostprijsActueel],
             [LPA_L1].[LogistiekJournaalVanafVolgordeID] AS [LogistiekJournaalVanafVolgordeId],
             [LPA_L1].[OHWKostenBerekend]               AS [OhwkostenBerekend],
             [LPA_L1].[OHWKostenRealisatie]           AS [OhwkostenRealisatie],
             [LPA_L1].[OHWSaldo]                         AS [Ohwsaldo],
             [LPA_L1].[Oplage],
             [LPA_L1].[OplageAfleverPrognose],
             [LPA_L1].[OplageAfzetControleVolgordeID]   AS [OplageAfzetControleVolgordeId],
             [LPA_L1].[OplageCommercieel],
             [LPA_L1].[OplageDirectie],
             [LPA_L1].[OplageGeleverd],
             [LPA_L1].[---Order---]                   AS [Order],
             [LPA_L1].[OrderAantalGewijzigd],
             [LPA_L1].[OrderAfwijkingDetailPercentage],
             [LPA_L1].[OrderAfwijkingSignaleren],
             [LPA_L1].[OrderAfwijkingTotaalPercentage],
             [LPA_L1].[OrderDatum],
             [LPA_L1].[OrderOpschatting],
             [LPA_L1].[OrderOpschattingTotaal],
             [LPA_L1].[OrderPrognoseTotaal],
             [LPA_L1].[OrderStandTotaal],
             [LPA_L1].[OudDruknr],
             [LPA_L1].[PlanningScenarioID]             AS [PlanningScenarioId],
             [LPA_L1].[PrognoseVerschijningsdatum],
             [LPA_L1].[ProjectID]                       AS [ProjectId],
             [LPA_L1].[PWAfgesproken]                   AS [Pwafgesproken],
             [LPA_L1].[PWBijwerkenVanafVolgordeID]     AS [PwbijwerkenVanafVolgordeId],
             [LPA_L1].[PWCommercieel]                   AS [Pwcommercieel],
             [LPA_L1].[PWControleVanafVolgordeID]       AS [PwcontroleVanafVolgordeId],
             [LPA_L1].[PWDirectie]                     AS [Pwdirectie],
             [LPA_L1].[RealisatieVerschijningsdatum],
             [LPA_L1].[RechtstreeksBelgie],
             [LPA_L1].[StatusID]                         AS [StatusId],
             [LPA_L1].[UitgaveID]                       AS [UitgaveId],
             [LPA_L1].[Verschijningsdatum],
             [LPA_L1].[VolgordeID]                     AS [VolgordeId],
             [LPA_L1].[---VOORSTEL EN FIAT---]         AS [VoorstelEnFiat],
             [LPA_L1].[VVPBijwerkenVanafVolgordeID]   AS [VvpbijwerkenVanafVolgordeId]
FROM   [Druk] [LPA_L1]
WHERE  ((([LPA_L1].[DrukID] = @p1))) 
5860 /* @p1 */

Walaa avatar
Walaa
Support Team
Posts: 14946
Joined: 21-Aug-2005
# Posted on: 09-Jun-2016 17:07:31   

var query = metaData.Druk.With( d => d.Calculaties.Where(c => c.CalcMomentId == d.CalcMomentId) );

You are passing a field to the predicate, not a variable or a constant.

I'm not sure whether you want to join and filter on a field. Or you want to filter on a field's value in a pre-fetched entity.

Puser
User
Posts: 228
Joined: 20-Sep-2012
# Posted on: 09-Jun-2016 17:27:25   

I dont have a prefetched entity, so the query has to fetch it from Druk and apply it to the filter in Calculaties. Mind that there is already a join on DrukID, see below:

In Druk there is - DrukID - CalcMomentID (it is the active 'moment' of a calculation)

In Calculatie there is also - DrukID - CalcMomentID (there can be multiple records for the the same DrukID with different CalcMomentID values)

In the designer there is a 1-Many relationship on DrukID. So that one is taken into account.

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39588
Joined: 17-Aug-2003
# Posted on: 09-Jun-2016 17:38:08   

Please provide a repro case on northwind or adventure works, or if that fails your own schema so we can reproduce it here. Also please state what the query should do (what data you're trying to retrieve). Additionally, to avoid it being a problem with '.With()', please try the general prefetch path methods WithPath first.

Frans Bouma | Lead developer LLBLGen Pro
Otis avatar
Otis
LLBLGen Pro Team
Posts: 39588
Joined: 17-Aug-2003
# Posted on: 10-Jun-2016 09:24:23   

Btw: d => d.Calculaties.Where(c => c.CalcMomentId == d.CalcMomentId)

the where is unnecessary, as d.Calculaties already implies c.CalcMomentId==d.CalcMomentId. I think the additional where clause makes things go wrong. Could you check?

Frans Bouma | Lead developer LLBLGen Pro
Puser
User
Posts: 228
Joined: 20-Sep-2012
# Posted on: 10-Jun-2016 11:19:42   

Thank you for your replies.

I have attached a printscreen of the relationship, so you know I have to add that extra filter to ensure only one record of the 1-n relationship returns. Llblgen doesnt know of the extra filter by itself.

In my old version based on LLBLGen Pro 4.2 i used paths:

            var druk = new DrukEntity(drukId);
            var drukPath = new PrefetchPath2(EntityType.DrukEntity);

            //Calculatie componenten
            var calcPath = drukPath.Add(DrukEntity.PrefetchPathCalculaties, 1, new PredicateExpression(CalcFields.CalcMomentId == DrukFields.CalcMomentId), new RelationCollection(DrukEntity.Relations.CalcEntityUsingDrukId)).SubPath;

This worked but I need an IQueryable.

Attachments
Filename File size Added on Approval
Llblgen 5 snip.JPG 40,117 10-Jun-2016 11:20.11 Approved
Puser
User
Posts: 228
Joined: 20-Sep-2012
# Posted on: 10-Jun-2016 11:31:48   

Changing it to

                var q1 = (from d in metaData.Druk select d)
                    .WithPath(p => p.Prefetch(d => d.Calculaties.Where(c => c.CalcMomentId == d.CalcMomentId)));

or

                var q1 = (from d in metaData.Druk select d)
                    .WithPath(p => p.Prefetch<CalcEntity>(d => d.Calculaties.Where(c => c.CalcMomentId == d.CalcMomentId)));

does not help either.

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39588
Joined: 17-Aug-2003
# Posted on: 10-Jun-2016 11:37:13   

Puser wrote:

Thank you for your replies.

I have attached a printscreen of the relationship, so you know I have to add that extra filter to ensure only one record of the 1-n relationship returns. Llblgen doesnt know of the extra filter by itself.

No, that's not true. The relationship is Druk 1:n Calc. E.g. Customer 1:n Order

So you try to do

var query = metaData.Druk.With(
                    d => d.Calculaties.Where(c => [b]c.CalcMomentId == d.CalcMomentId[/b])
                    );

which is saying: fetch Druk and its Calculations (related 1:n to druk) where the FK field in calc is equal to the pk field in druk, which is always the case otherwise the calc instance wouldn't be related to the druk instance. The d=>d.Calculaties already creates a prefetch path element which contains the relationship Druk 1:n Calc, and will use that to build the graph and queries, you don't need to specify any filter. simple_smile

If I fetch customers I don't need to specify the FK-PK relationship between Order and Customer, that's already there.

In my old version based on LLBLGen Pro 4.2 i used paths:

            var druk = new DrukEntity(drukId);
            var drukPath = new PrefetchPath2(EntityType.DrukEntity);

            //Calculatie componenten
            var calcPath = drukPath.Add(DrukEntity.PrefetchPathCalculaties, 1, new PredicateExpression(CalcFields.CalcMomentId == DrukFields.CalcMomentId), new RelationCollection(DrukEntity.Relations.CalcEntityUsingDrukId)).SubPath;

This worked but I need an IQueryable.

Yes, but that too states a join which isn't needed. Additionally, it specifies 1 as a limit on calc, but you specify FirstOrDefault in your IQueryable query which is a limit on druk. To specify the limit on Calc you have to specify it with the With() call (and remove the Where!)

Frans Bouma | Lead developer LLBLGen Pro
Puser
User
Posts: 228
Joined: 20-Sep-2012
# Posted on: 10-Jun-2016 11:52:20   

Hello Frans,

i think you missed that the relationship is on DrukID and not CalcMomentID. this last identifier is used to get 1 (or 0) Calc's from the Calculations. (i.e. read if you would have a foreign key for Customer which would state the most beautiful order with a MustBeautifulOrderId in Customer. Then you would create a join/where on both CustomerId AND Customer.MustBeatifulOrderId==Order.OrderId)

Anyhow, I have got it working, be it feeling a bit 'unnatural':

                var q1 = (from d in metaData.Druk select d)
                    .WithPath(p => p.Prefetch<CalcEntity>(d => d.Calculaties).FilterOn(c => c.Druk.CalcMomentId== c.CalcMomentId));

and

                var query = metaData.Druk.With(
                    d => d.Calculaties.Where(c => c.CalcMomentId == c.Druk.CalcMomentId)
                    );

then the questions remains, must I be cautious to use aliasses from earlier expressions (like the d.CalcMomentId) or is there something wrong?

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39588
Joined: 17-Aug-2003
# Posted on: 10-Jun-2016 12:55:30   

Puser wrote:

Hello Frans,

i think you missed that the relationship is on DrukID and not CalcMomentID. this last identifier is used to get 1 (or 0) Calc's from the Calculations. (i.e. read if you would have a foreign key for Customer which would state the most beautiful order with a MustBeautifulOrderId in Customer. Then you would create a join/where on both CustomerId AND Customer.MustBeatifulOrderId==Order.OrderId)

Aha! I indeed missed that and assumed a different relationship!

Anyhow, I have got it working, be it feeling a bit 'unnatural':

                var q1 = (from d in metaData.Druk select d)
                    .WithPath(p => p.Prefetch<CalcEntity>(d => d.Calculaties).FilterOn(c => c.Druk.CalcMomentId== c.CalcMomentId));

and

                var query = metaData.Druk.With(
                    d => d.Calculaties.Where(c => c.CalcMomentId == c.Druk.CalcMomentId)
                    );

then the questions remains, must I be cautious to use aliasses from earlier expressions (like the d.CalcMomentId) or is there something wrong?

I now see why the other one couldn't work. The problem is that 'd' isn't in scope technically in the filter when the prefetch path is executed. For Linq this query compiles as it thinks everything is executed in 1 query, but 'WithPath' is a directive for a future query, so 'd' in that query isn't correct. The filter you specified is however, as you only specify elements which are in scope for that query, namely everything related from the calc definition.

It's sadly unavoidable to have these queries compile correctly but fail at runtime because the compiler doesn't know that 'WithPath' isn't a statement translating to an element for the same query.

So if you remember that everything you specify in a method following a WithPath (e.g. in a FilterOn()) can't be using elements from an outer scope (in your case, the 'd') as these elements aren't available at runtime.

As the compiler creates an expression tree which is parsable by the linq provider, it doesn't see this as a problem, however you'll run into the problem when the sql is executed.

Frans Bouma | Lead developer LLBLGen Pro
Puser
User
Posts: 228
Joined: 20-Sep-2012
# Posted on: 10-Jun-2016 15:29:12   

Thank you for this in depth explanation.

I run integration tests all the time, so next time I see this error I know what to do!

Happy regards