I'm attempting to upgrade a project to the latest (as of today) v2.5 LLBLGen. After some minor changes to get the compiling done, it turns out my TypedLists are now producing invalid queries. I have a hierarchy of:
Contact
-> Individual
-> Business
-> Committee
I have typed lists for each subtype that gather data that includes fields from the super type Contact.
From the look of the generated SQL, it seems that it now produces table aliases, but does not use those aliases in the WHERE clause.
Here is the generated SQL, followed by some of the c# code used to produce this.
SELECT [dbo].[Address].[Street] AS [Address], [dbo].[Address].[City], [dbo].[Address].[State], [dbo].[Address].[ZipCode], SUM([dbo].[Contribution].[Amount]) AS [Amount], [dbo].[Email].[EmailAddress], [LPA_L2].[Title], [LPA_L2].[FirstName], [LPA_L2].[LastName], [LPA_L2].[Init], [LPA_L2].[Suffix], [LPA_L2].[Occupation], [dbo].[Phone].[PhoneNum] AS [Phone], [LPA_L2].[Employer], [LPA_L2].[ContactID], [LPA_L1].[ContactType], [LPA_L1].[Notes], [LPA_L1].[UserAdded], [LPA_L1].[Salutation], [LPA_L1].[Envelope], [LPA_L1].[DoNotMail], [LPA_L1].[DefaultAddressID], [LPA_L1].[DefaultPhoneID], [LPA_L1].[DefaultEmailID], [LPA_L1].[CreatedOn] FROM (((((( [dbo].[Contact] [LPA_L1] INNER JOIN [dbo].[Individual] [LPA_L2] ON [LPA_L1].[ContactID]=[LPA_L2].[ContactID]) LEFT JOIN [dbo].[Address] ON [LPA_L2].[ContactID]=[dbo].[Address].[ContactID] AND ( ( [dbo].[Address].[AddressTypeID] = [LPA_L1].[DefaultAddressID]))) LEFT JOIN [dbo].[Phone] ON [LPA_L2].[ContactID]=[dbo].[Phone].[ContactID] AND ( ( [dbo].[Phone].[PhoneTypeID] = [LPA_L1].[DefaultPhoneID]))) LEFT JOIN [dbo].[Email] ON [LPA_L2].[ContactID]=[dbo].[Email].[ContactID] AND ( ( [dbo].[Email].[EmailTypeID] = [LPA_L1].[DefaultEmailID]))) LEFT JOIN [dbo].[Contribution] ON [LPA_L2].[ContactID]=[dbo].[Contribution].[ContactID]) LEFT JOIN [dbo].[ClientCommittee] ON [dbo].[ClientCommittee].[ClientCommitteeID]=[dbo].[Contribution].[CommitteeID]) WHERE ( ( ( [dbo].[Contribution].[CommitteeID] = @CommitteeID1) AND [dbo].[Contact].[DoNotMail] = @DoNotMail2 OR ( [dbo].[Contact].[DoNotMail] = @DoNotMail3 AND [dbo].[Contact].[UserAdded] = @UserAdded4)) AND ( ( [LPA_L2].[ContactID] IS NOT NULL))) GROUP BY [dbo].[Address].[Street], [dbo].[Address].[City], [dbo].[Address].[State], [dbo].[Address].[ZipCode], [dbo].[Email].[EmailAddress], [LPA_L2].[Title], [LPA_L2].[FirstName], [LPA_L2].[LastName], [LPA_L2].[Init], [LPA_L2].[Suffix], [LPA_L2].[Occupation], [dbo].[Phone].[PhoneNum], [LPA_L2].[Employer], [LPA_L2].[ContactID], [LPA_L1].[ContactType], [LPA_L1].[Notes], [LPA_L1].[UserAdded], [LPA_L1].[Salutation], [LPA_L1].[Envelope], [LPA_L1].[DoNotMail], [LPA_L1].[DefaultAddressID], [LPA_L1].[DefaultPhoneID], [LPA_L1].[DefaultEmailID], [LPA_L1].[CreatedOn] ORDER BY [LPA_L2].[LastName] ASC
c#
IEntityFields fields = GetResultSet();
IGroupByCollection groupByClause = DALHelper.GetGroupByClause(fields);
CamelotAccess.DaoClasses.TypedListDAO dao = new CamelotAccess.DaoClasses.TypedListDAO();
// Create Relations
IRelationCollection rc = GetRelations();
// This filter added to only include current committees in Aggregate
IPredicateExpression filter = new PredicateExpression();
DALHelper.ApplyCommitteeFilter(ref filter, ContributionFields.CommitteeID, CurrentCommittees);
if (HideDNS)
filter.AddWithAnd(ContactFields.DoNotMail == false);
if (ShowUserAdded)
{
if (HideDNS)
filter.AddWithOr(ContactFields.DoNotMail == false & ContactFields.UserAdded == true);
else
filter.AddWithOr(ContactFields.UserAdded == true);
}
// Data Retreival
DataTable itl = this.Clone();
if (this.Count == 0)
{
itl = this;
// 'this' is base class that sits inbetween my IndividualTypedList generated class,
// and the TypedListBase from LLBLGen.
// like so: IndividualTypedList : ContribuTracTypedList : TypedListBase
if(pageCount != 0)
dbRowCount = GetDbCount(filter, rc);
}
SortExpression sort = GetSortExpression();
dao.GetMultiAsDataTable(fields, itl, 0, sort, filter, rc, true, groupByClause, null, pageCount > 0 ? 1 : 0, this.Count == 0 ? pageCount : dbRowCount - PageCount);
Again, this was production code that worked great before the upgrade/DAL regeneration.
Any help on this would be appreciated.
Thanks!
