Hi

I have the code below.

How can I left join on VoorwaardenAdvies table?

The code below doesn't work for me.

            
            var weergaveAdviezen = new EntityCollection<WeergaveAdviesEntity>();
            var weergaveAdviesFilter = new RelationPredicateBucket();

            // fetch all weergaveAdviezen which have the given ingangsklachtId and VerwijderdOp is null
            weergaveAdviesFilter.Relations.Add(WeergaveAdviesEntity.Relations.AdviesEntityUsingAdviesId);
            weergaveAdviesFilter.Relations.Add(WeergaveAdviesEntity.Relations.VoorwaardenAdviesEntityUsingWeergavetekstId, JoinHint.Left);

            weergaveAdviesFilter.PredicateExpression.Add(WeergaveAdviesFields.IngangsklachtId == ingangsklachtId);
            weergaveAdviesFilter.PredicateExpression.Add(WeergaveAdviesFields.VerwijderdOp == DBNull.Value);
            weergaveAdviesFilter.PredicateExpression.Add(AdviesFields.VerwijderdOp == DBNull.Value);
            
            // load for all weergaveadviezen which have records with the given ingangsklachtId
            var path = new PrefetchPath2(EntityType.WeergaveAdviesEntity);
            var voorwaardenAdviesPath = WeergaveAdviesEntity.PrefetchPathVoorwaardenAdviezen;
            voorwaardenAdviesPath.SubPath.Add(VoorwaardenAdviesEntity.PrefetchPathUrgentiecategorie);
            voorwaardenAdviesPath.SubPath.Add(VoorwaardenAdviesEntity.PrefetchPathTriagecriterium);
            voorwaardenAdviesPath.SubPath.Add(VoorwaardenAdviesEntity.PrefetchPathTriagecriteriumAntwoord);
            path.Add(WeergaveAdviesEntity.PrefetchPathAdvies);
            path.Add(voorwaardenAdviesPath);
            
            // perform the fetch
            using (var adapter = Queryhelper.Adapter)
            {
                adapter.FetchEntityCollection(weergaveAdviezen, weergaveAdviesFilter, path);
                return weergaveAdviezen.OrderBy(x => x.AdviesNaam).ToList();
            }

Why do you want to Left-Join to VoorwaardenAdvies ? Is it for filtering purposes, or you just want to fetch related VoorwaardenAdvies entity as well.

I'm asking because I don't see any filtering predicate involving VoorwaardenAdvies while it's a Left-Join. Also I see you using a prefetchPath for VoorwaardenAdvies which means you want to fetch them.

I want to fetch the related VoorwaardenAdvies records as wel. But it can happen that we do not have a related record in this table.

Then you don't need the to add the Left-Join relation

weergaveAdviesFilter.Relations.Add(WeergaveAdviesEntity.Relations.VoorwaardenAdviesEntityUsingWeergavetekstId, JoinHint.Left);

The prefetchPath is what you need, this is how you define the related entities that you want to fetch as well.

Yes, but if the WeergaveAdviesEntity has no VoorwaardenAdviesEntity, there will be no WeergaveAdviesEntity shown.

Right?

This is the SQL that reflects the data I want to retrieve.

SELECT *
FROM dbo.WeergaveAdvies wa
INNER JOIN dbo.Advies a ON wa.advies_id = a.advies_id
LEFT JOIN dbo.Voorwaardenadvies v ON wa.weergavetekst_id = v.weergavetekst_id
LEFT JOIN dbo.Urgentiecategorie u ON v.urgentiecategorie_id = u.urgentiecategorie_id
LEFT JOIN dbo.Triagecriterium t ON v.triagecriterium_id = t.triagecriterium_id
LEFT JOIN dbo.TriagecriteriumAntwoord ta ON t.triagecriterium_id = ta.triagecriterium_id
WHERE wa.ingangsklacht_id = 13
ORDER BY a.naam

So how should i do that in LLBLGen?

You should first decide what you want to fetch: an entity graph or a flat list of data. The query in your last post is a flat list of data.

Your first post shows a fetch of entities, so if you want related data to these entities, you have to specify prefetch paths as you're fetching an entity graph (e.g. Customers which in each customer instance have their Order instances in the 'Orders' collection). The engine takes care of related entities not being there, you don't have to worry about that.

If you want to have a flat list, so all joined tables together in 1 resultset, you should either look into creating a typed list or if you want to write the resultset class yourself, fetching the resultset in instances of that class.

To answer your question better, please confirm what you want to have as resultset: a flat list or an entity graph

Frans,

Thanks for elaborating a bit more.

I like to have an entity graph that is linked like the query is.

Sorry, sorry, sorry!!

It was some issue in my code that was filtering out the left joined records.

One question still exists. When I add Relations and PredicateExpressions to a RelationPredicateBucket to empty entities, they won't show in my result set.

Is that right? And why? They are in my resultset when I don't add Relations to those empty entities.

cadsjo wrote:

Sorry, sorry, sorry!!

It was some issue in my code that was filtering out the left joined records.

Good that you figured it out!

cadsjo wrote:

One question still exists. When I add Relations and PredicateExpressions to a RelationPredicateBucket to empty entities, they won't show in my result set.

Is that right? And why? They are in my resultset when I don't add Relations to those empty entities.

I guess is the same that happens when you do that in any SQL Query: if you add inner relations, the resultset exclude those on the FK side that doesn't match the PK side. So empty fields on FK side won't match on PK (with can't be null, by definition).

In such cases, you should specify a left join. Is that what you are talking about or is something else?