Using: Adapter template. LLBLGen Pro Version 2.6 Final (October 9th, 2009) .Net 3.5

Tables: PropertyDescription BlockLot LotType.

I've got two paths from PropertyDescription to LotType.

Path 1: PropertyDescription joins to BlockLot on PropertyDescription.BlockLotId = BlockLot.BlockLotId and then BlockLot joins to LotType on BlockLot.LotTypeId = LotType.LotTypeId

Path 2: PropertyDescription joins to LotType on PropertyDescription.LotTypeId = LotType.LotTypeId

Query:

Below is the query I'm trying to create with a TypedList.

I'm trying to get the LotType.LotType name twice via the two paths mentioned earlier.

select 
PropertyDescription.PropertyDescriptionID,
TemporaryLotType.LotTypeName as TemporaryLotTypeName,
LotType.LotTypeName
from PropertyDescription 
left join LotType TemporaryLotType on PropertyDescription.TemporaryLotTypeID = TemporaryLotType.LotTypeId
left join BlockLot on PropertyDescription.BlockLotID = BlockLot.BlockLotID
left join LotType LotType on BlockLot.LotTypeID = LotType.LotTypeID

Here is the code I wrote to try to create it:

// fields
ResultsetFields fields = new ResultsetFields(3);
fields.DefineField(PropertyDescriptionFieldIndex.PropertyDescriptionId, 0, "PropertyDescriptionId");
fields.DefineField(LotTypeFieldIndex.LotTypeName, 1, "TemporaryLotType");
fields.DefineField(LotTypeFieldIndex.LotTypeName, 2, "LotType");

// tables
IRelationPredicateBucket bucket = new RelationPredicateBucket();
bucket.Relations.Add(PropertyDescriptionEntity.Relations.BlockLotEntityUsingBlockLotId, JoinHint.Left);
bucket.Relations.Add(BlockLotEntity.Relations.LotTypeEntityUsingLotTypeId, JoinHint.Left);
bucket.Relations.Add(PropertyDescriptionEntity.Relations.LotTypeEntityUsingTemporaryLotTypeId, "TemporaryLotType", JoinHint.Left);

// predicate    
PredicateExpression pred = new PredicateExpression();
pred.Add(PropertyDescriptionFields.PropertyDescriptionId > 350578);
bucket.PredicateExpression.Add(pred);

// run
DataAccessAdapter adapter = new DataAccessAdapter();
DataTable list = new DataTable();
int maxItems = -1;
ISortExpression sorter = null;
bool allowDuplicates = false;
adapter.FetchTypedList(fields, list, bucket, maxItems, sorter, allowDuplicates);

Generated Query:

SELECT DISTINCT 
[GCSLRMS_Buffalo_DB53].[dbo].[PropertyDescription].[PropertyDescriptionID] AS [PropertyDescriptionId], 
[GCSLRMS_Buffalo_DB53].[dbo].[LotType].[LotTypeName] AS [TemporaryLotType], 
[GCSLRMS_Buffalo_DB53].[dbo].[LotType].[LotTypeName] AS [LotType] 
FROM ((( 
[GCSLRMS_Buffalo_DB53].[dbo].[BlockLot]  RIGHT JOIN [GCSLRMS_Buffalo_DB53].[dbo].[PropertyDescription]  ON  [GCSLRMS_Buffalo_DB53].[dbo].[BlockLot].[BlockLotID]=[GCSLRMS_Buffalo_DB53].[dbo].[PropertyDescription].[BlockLotID]) 
LEFT JOIN [GCSLRMS_Buffalo_DB53].[dbo].[LotType]  ON  [GCSLRMS_Buffalo_DB53].[dbo].[LotType].[LotTypeID]=[GCSLRMS_Buffalo_DB53].[dbo].[BlockLot].[LotTypeID]) 
LEFT JOIN [GCSLRMS_Buffalo_DB53].[dbo].[LotType] [LPA_T1]  ON  [LPA_T1].[LotTypeID]=[GCSLRMS_Buffalo_DB53].[dbo].[PropertyDescription].[TemporaryLotTypeID])
WHERE ( ( ( [GCSLRMS_Buffalo_DB53].[dbo].[PropertyDescription].[PropertyDescriptionID] > 350578)))

Comments / Questions : It looks like it generated a table alias in the join like I wanted (LPA_T1). But how come it doesn't use the name I gave it ("TemporaryLotType")?

How can I get the field select to use that "TemporaryLotType" table alias? Instead of generating this:

[GCSLRMS_Buffalo_DB53].[dbo].[LotType].[LotTypeName] AS [TemporaryLotType]

How can I get it to generate this instead?:

[LPA_T1].[LotTypeName] AS [TemporaryLotType]

Thanks.

You are using the DefineField overload that accepts an alias for the field. You need to use another overload that accepts an entity alias, and use the same alias you use in the join.

Oh, I didn't see that one. That did the trick. Thanks!