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 */

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.

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.

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.

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?

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.

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.

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.

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!)

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?

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.

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