Thanks for the response.
The code I wrote to build a dynamic list is spread across several functions. I'm just going to post the parts that I think are pertinent.
public static WebSearchForApplicationsResultDataCollection Search(WebSearchForApplicationsCriteria criteria)
{
<here is some code to make field selections>
// Select the tables we will join (create the bucket's relations).
// We sometimes do different joins depending on the search criteria.
IRelationPredicateBucket bucket = new RelationPredicateBucket();
ICollection relations = CreateRelationsUsingCriteria(_criteria);
bucket.Relations.AddRange(relations);
<here is some code to make the predicate>
// Now get the data
DataAccessAdapter adapter = new DataAccessAdapter();
adapter.FetchTypedList(fields, list, bucket, maxItems, sorter, allowDuplicates);
}
private static ICollection CreateRelationsUsingCriteria(WebSearchForApplicationsCriteria criteria)
{
ArrayList relations = new ArrayList();
IEntityRelation relation;
<here is code that creates other relations and adds them to the list>
// PT.Person
relation = PersonForApplicationEntity.Relations.Person_EntityUsingPersonId;
relation.HintForJoins = JoinHint.Left;
//CLB- relation.SetAliases("Yadda", "Blah"); // "Bad alias?"
//CLB- relation.SetAliases("", ""); // does nothing
relation.SetAliases("", "PersonAlias"); // make [LPA_P1] the alias for PT.Person.
relations.Add(relation);
return relations;
}
I don't know if I'm using the SetAliases() method correctly.
When I use: relation.SetAliases("Yadda", "Blah"), I get an exception:
GCS.WS.BL.UnitTests.UnitTests_ApplicationSearch.ReferenceNameSearch_FirstName : GCS.WS.BL.Exception.WSBusinessLogicException : The 'search' process failed
----> SD.LLBLGen.Pro.ORMSupportClasses.ORMRelationException : Relation at index 7 doesn't contain an entity already added to the FROM clause. Bad alias?
When I use: relation.SetAliases("", ""), the query doesn't make any aliases, which is what I expect.
When I use: relation.SetAliases("", "PersonAlias"), it makes an alias but calls it LPA_P1 instead of PersonAlias.
It also gives an error because the WHERE clause isn't using an alias while the FROM clause is.
GCS.WS.BL.UnitTests.UnitTests_ApplicationSearch.ReferenceNameSearch_FirstName : GCS.WS.BL.Exception.WSBusinessLogicException : The 'search' process failed
----> System.Data.SqlClient.SqlException : The multi-part identifier "GCSLRMS_WS_UnitTest.PT.Person.FirstName" could not be bound.
Below is the generated SQL.
See the last LEFT JOIN. It uses an alias LPA_P1. How did it come up with that?
The WHERE clause is not using the alias. So I need to also learn how to do that.
Note that I'm not actually selecting any field from the PT.Person table, I'm only using it for filter purposes.
[code]SELECT DISTINCT [GCSLRMS_WS_UnitTest].[PT].[ApplicationType].
, [GCSLRMS_WS_UnitTest].[PT].[Application].[Prefix], [GCSLRMS_WS_UnitTest].[PT].[Application].[Number], [GCSLRMS_WS_UnitTest].[PT].[Application].[IssuePrefix], [GCSLRMS_WS_UnitTest].[PT].[Application].[IssueNumber], [GCSLRMS_WS_UnitTest].[PT].[Application].[Year], [GCSLRMS_WS_UnitTest].[dbo].[Parcel].[UserDefinedID] AS [UserDefinedId], [GCSLRMS_WS_UnitTest].[dbo].[Parcel].[UserDefinedID2] AS [UserDefinedId2], [GCSLRMS_WS_UnitTest].[dbo].[Person].[NamePrefix], [GCSLRMS_WS_UnitTest].[dbo].[Person].[FirstName], [GCSLRMS_WS_UnitTest].[dbo].[Person].[MiddleName], [GCSLRMS_WS_UnitTest].[dbo].[Person].[LastName], [GCSLRMS_WS_UnitTest].[dbo].[Person].[NameExtension], [GCSLRMS_WS_UnitTest].[dbo].[PropertyAddress].[HouseNumber], [GCSLRMS_WS_UnitTest].[dbo].[PropertyAddress].[HouseNumberSuffix], [GCSLRMS_WS_UnitTest].[dbo].[PropertyAddress].[PrefixDirection], [GCSLRMS_WS_UnitTest].[dbo].[PropertyAddress].[StreetName], [GCSLRMS_WS_UnitTest].[dbo].[PropertyAddress].[StreetType], [GCSLRMS_WS_UnitTest].[dbo].[PropertyAddress].[SuffixDirection], [GCSLRMS_WS_UnitTest].[dbo].[PropertyAddress].[UnitType], [GCSLRMS_WS_UnitTest].[dbo].[PropertyAddress].[UnitNumber], [GCSLRMS_WS_UnitTest].[PT].[TemporaryParcel].[Name], [GCSLRMS_WS_UnitTest].[PT].[TemporaryParcel].[PropertyAddress]
FROM
(((((((( [GCSLRMS_WS_UnitTest].[PT].[ApplicationType]
INNER JOIN [GCSLRMS_WS_UnitTest].[PT].[Application] ON [GCSLRMS_WS_UnitTest].[PT].[ApplicationType].[ID]=[GCSLRMS_WS_UnitTest].[PT].[Application].[ApplicationTypeID])
LEFT JOIN [GCSLRMS_WS_UnitTest].[PT].[TemporaryParcel] ON [GCSLRMS_WS_UnitTest].[PT].[TemporaryParcel].[ID]=[GCSLRMS_WS_UnitTest].[PT].[Application].[TemporaryParcelID])
LEFT JOIN [GCSLRMS_WS_UnitTest].[dbo].[Parcel] ON [GCSLRMS_WS_UnitTest].[dbo].[Parcel].[ParcelID]=[GCSLRMS_WS_UnitTest].[PT].[Application].[ParcelID] AND ( [GCSLRMS_WS_UnitTest].[dbo].[Parcel].[IsRE] = @IsRe1))
LEFT JOIN [GCSLRMS_WS_UnitTest].[dbo].[Ownership] ON [GCSLRMS_WS_UnitTest].[dbo].[Parcel].[ParcelID]=[GCSLRMS_WS_UnitTest].[dbo].[Ownership].[ParcelID] AND ( [GCSLRMS_WS_UnitTest].[dbo].[Ownership].[OwnerStatus] = @OwnerStatus2))
LEFT JOIN [GCSLRMS_WS_UnitTest].[dbo].[Person] ON [GCSLRMS_WS_UnitTest].[dbo].[Person].[PersonID]=[GCSLRMS_WS_UnitTest].[dbo].[Ownership].[PersonID])
LEFT JOIN [GCSLRMS_WS_UnitTest].[dbo].[PropertyAddress] ON [GCSLRMS_WS_UnitTest].[dbo].[Parcel].[ParcelID]=[GCSLRMS_WS_UnitTest].[dbo].[PropertyAddress].[ParcelID] AND ( [GCSLRMS_WS_UnitTest].[dbo].[PropertyAddress].[PrimaryAddress] = @PrimaryAddress3))
LEFT JOIN [GCSLRMS_WS_UnitTest].[PT].[PersonForApplication] ON [GCSLRMS_WS_UnitTest].[PT].[Application].[ID]=[GCSLRMS_WS_UnitTest].[PT].[PersonForApplication].[ApplicationID])
LEFT JOIN [GCSLRMS_WS_UnitTest].[PT].[Person] [LPA_P1] ON [LPA_P1].[ID]=[GCSLRMS_WS_UnitTest].[PT].[PersonForApplication].[PersonID])
WHERE ( ( ( [GCSLRMS_WS_UnitTest].[PT].[Person].[FirstName] LIKE @FirstName4)))
). 