Hi All,

I hope you're well.

I'm struggling with replicating a join in LLBLGEN with the same table. I need the same table, but twice in the relations. (it's in bold) Is anyone able to help me please? (I'm using vb.net)

TOE is the table "TravelOrderEvent " and TOE2 is "TravelOrderEvent " under a different alias.

SELECT        TOE.RecordLocator, ATS.FlightServiceClass, ATS.FlightNbr, ATS.FlightDate, ATS.CouponBoardPoint, ATS.CouponOffPoint, TT.TicketNbr, P.LastName + '/' + P.FirstName AS Passenger, 
                         MAX(CASE WHEN TR.RemarkText LIKE '%X*-CU%' THEN TR.RemarkText ELSE '' END) AS CustomerNumber,  ATS.FlightTime, ATSFD.FlightDate 
FROM            TravelOrderEvent AS TOE INNER JOIN
                         TktTrans AS TT ON TOE.TravelOrderIdentifier = TT.TravelOrderIdentifier INNER JOIN
                         AirTktSeg AS ATS ON TOE.TravelOrderIdentifier = ATS.TravelOrderIdentifier INNER JOIN
                         AirTktSegFlightDate AS ATSFD ON ATS.TravelOrderIdentifier = ATSFD.TravelOrderIdentifier AND 
                         ATS.CouponSequenceNbr = ATSFD.CouponSequenceNbr INNER JOIN
                         Passenger AS P ON TOE.TravelOrderIdentifier = P.TravelOrderIdentifier **INNER JOIN
                        TravelOrderEvent AS TOE2 ON TOE.RecordLocator = TOE2.RecordLocator INNER JOIN**
                         TktRemarks AS TR ON TOE2.TravelOrderIdentifier = TR.TravelOrderIdentifier

Thanks, Simon

Hi Simon,

If you are using QuerySpec you could achieve that using the .As("alias") fluent method that let you write the entity alias. Then you use the .Source("alias") method to indicate some field belongs to the previous mentioned alias. This is a simple example using the Employee->Employee relationship that uses the Employee.ReportsTo FK. In this example we want to fetch all employees whose supervisor has a first name "Andrew".

Using QuerySpec :

var qf = new QueryFactory();
var q = qf.Employee
            .From(QueryTarget.InnerJoin(qf.Employee.As("m")).On(EmployeeFields.ReportsTo == EmployeeFields.EmployeeId.Source("m")))
            .Where(EmployeeFields.FirstName.Source("m") == "Andrew");

This is the same example, using Linq2LLBL:

using(DataAccessAdapter adapter = new DataAccessAdapter())
{
    var metaData = new LinqMetaData(adapter);
    var q = (ILLBLGenProQuery)(
            from e in metaData.Employee
            join m in metaData.Employee on e.ReportsTo equals m.EmployeeId
            where m.FirstName == "Andrew"
            select e);
    var employees = (EntityCollection<EmployeeEntity>)q.Execute();
}

Does that help? What do you have so far written in code? LLBLGen version? Using QuerySpec or Linq2Llbl?

Hi Daelmo,

Thank you for your reply.

I'm using something like this for the relations in the code.

        Dim relations As IRelationCollection = New RelationCollection
        relations.Add(GIDs.EntityClasses.TravelOrderEventEntity.Relations.TktTransEntityUsingTravelOrderIdentifier)
        Dim rel2 As IEntityRelation = New EntityRelation(GIDs.HelperClasses.TravelOrderEventFields.TravelOrderIdentifier, GIDs.HelperClasses.AirTktSegFields.TravelOrderIdentifier, RelationType.OneToOne)
        relations.Add(rel2)
        Dim rel As IEntityRelation = New EntityRelation(GIDs.HelperClasses.AirTktSegFields.TravelOrderIdentifier, GIDs.HelperClasses.AirTktSegFields.TravelOrderIdentifier, RelationType.OneToOne)
        relations.Add(rel)
        relations.Add(GIDs.EntityClasses.AirTktSegEntity.Relations.AirTktSegFlightDateEntityUsingCouponSequenceNbrTicketNbrTravelOrderIdentifier)
        relations.Add(GIDs.EntityClasses.PassengerEntity.Relations.TravelOrderEventEntityUsingTravelOrderIdentifier)
        rel.AddEntityFieldPair(GIDs.HelperClasses.TravelOrderEventFields.TravelOrderIdentifier, GIDs.HelperClasses.TravelOrderEventFields.TravelOrderIdentifier)

This is the relation I want to add - the second table would be "TOE2" as TravelOrderEventEntityUsingTravelOrderIdentifier Which i thought would be the below line of code? relations.Add(GIDs.EntityClasses.TravelOrderEventEntityUsingTravelOrderIdentifier.Relations.TravelOrderEventEntityUsingTravelOrderIdentifier)

The join looks like this in SQL: https://imgur.com/KhPJeK8

I believe we're on LLBLGEN 5.1

Thanks, Simon

Please don't create relationships for joins like that. Your way of doing things is unnecessarily complicated and therefore less maintainable. these are all generated for you, so no need to recreate them manually.

I've written your query in queryspec, it will fetch a flat list. I hope I've not made any typo's, but you'll get the gist of it.

Dim qf as New QueryFactory()
Dim q = qf.TravelOrderEvent
            .From(QueryTarget.InnerJoin(qf.TktTrans).On(TravelOrderEventFields.TravelOrderIdentifier.Equal(TktTransFields.TravelOrderIdentifier))
            .InnerJoin(qf.AirTktSeg).On(TravelOrderEventFields.TravelOrderIdentifier.Equal(AirTktSegFields.TravelOrderIdentifier))
            .InnerJoin(qf.AirTktSegFlightDate)
                .On(AirTktSegFields.TravelOrderIdentifier.Equal(AirTktSegFlightDateFields.TravelOrderIdentifier)
                        .And(AirTktSegFlightDate.CouponSequenceNbr.Equal(AirTktSegFlightDate.CouponSequenceNbr)))
            .InnerJoin(qf.Passenger).On(TravelOrderEventFields.TravelOrderIdentifier.Equal(PassengerFields.TravelOrderIdentifier))
            .InnerJoin(qf.TravelOrderEvent.As("TOE2"))
                    .On(TravelOrderEventFields.RecordLocator.Equal(TravelOrderEventFields.RecordLocator.Source("TOE2")))
            .InnerJoin(qf.TktRemarks).On(TravelOrderEventFields.TravelOrderIdentifier.Source("TOE2").Equal(TktRemarksFields.TravelOrderIdentifier))
        .Select(TravelOrderEventFields.RecordLocator, AirTktSegFields.FlightServiceClass, AirTktSegFields.FlightNbr, AirTktSegFields.FlightDate, 
                AirTktSegFields.CouponBoardPoint, AirTktSegFields.CouponOffPoint, TktTransFields.TicketNbr, 
                StringFunctions.Concat(StringFunctions.Concat(PassengerFields.LastName, "/"), PassengerFields.FirstName).As("Passenger"),
                Functions.IIF(TktRemarksFields.RemarkText.Like("%X*-CU%"), TktRemarksFields.RemarkText, "").Max().As("CustomerNumber"), AirTktSegFields.FlightTime, 
                AirTktSegFlightDateFields.FlightDate)

To fetch it, see: https://www.llblgen.com/Documentation/5.8/LLBLGen%20Pro%20RTF/Using%20the%20generated%20code/QuerySpec/gencode_queryspec_fetchingqueries.htm (q here is a DynamicQuery, not projected to any type, so if you want that, you need to add projections to it)

You can use the Equal() extension method in your code building predicates as well, but you don't need to I think, as the generated code already contains relationship objects like TravelOrderEventEntity.Relations.TktTransEntityUsingTravelOrderIdentifier, and the rest of the ones you need should be there too.

To join an entity twice, you need to alias it, as described here: https://www.llblgen.com/Documentation/5.8/LLBLGen%20Pro%20RTF/Using%20the%20generated%20code/Filtering%20and%20Sorting/gencode_filteringadvanced.htm#advanced-filtering

That page also describes how to specify a custom predicate for a join.

But really, do yourself a favor and get rid of that New EntityRelation() usage, as that's not recommended at all.

Hi Daelmo and Otis,

That's worked for me.

Thank you for your help !