- Home
- LLBLGen Pro
- Bugs & Issues
Issue using Relations.ObeyWeakRelations with PrefetchPath.Add
Joined: 22-Jan-2007
Dear all,
I see a possible issue with the Relations.ObeyWeakRelations when used in PrefetchPath.Add. I have a method to fetch contact invitations. Invitations are for a workspace and may also be for a project. If I remove the following line (and also remove the full text search criteria on project title/description), I get the result I want:
relations.Add(ContactInvitationEntity.Relations.ProjectEntityUsingInviterProjectId);
Removing this, makes it no longer possible to enter the "My really cool project" search criterion to fetch all pending invitations for the project entitled 'My really cool project'.
If I keep the relation to Project, I miss the invitations that have no Project connected.
Ideas anyone?
Thanks,
Tore.
Using the spectacular LLBLGen v2.5 Final
public IEnumerable<WorkspaceEntity> FetchPendingContactsByFilter(string searchCriteria, Searcher.ContactSearchStatusFilter statusFilter, long userId) {
using (DataAccessAdapter adapter = new DataAccessAdapter()) {
EntityCollection<WorkspaceEntity> workspaces = new EntityCollection<WorkspaceEntity>();
IRelationPredicateBucket filter = new RelationPredicateBucket();
filter.Relations.ObeyWeakRelations = true;
filter.Relations.Add(WorkspaceEntity.Relations.CustomerEntityUsingCustomerId);
filter.Relations.Add(WorkspaceEntity.Relations.UserRoleWorkspaceEntityUsingWorkspaceId);
filter.Relations.Add(CustomerEntity.Relations.CustomerAdminEntityUsingCustomerId);
filter.Relations.Add(CustomerEntity.Relations.SalesPersonEntityUsingSalesPersonId);
filter.Relations.Add(SalesPersonEntity.Relations.ResellerEntityUsingResellerId);
filter.Relations.Add(ResellerEntity.Relations.ResellerAdminEntityUsingResellerId);
filter.Relations.Add(WorkspaceEntity.Relations.ContactInvitationEntityUsingInviteeWorkspaceId);
filter.Relations.Add(WorkspaceEntity.Relations.ProjectEntityUsingWorkspaceId);
if (!SecurityRepository.IsUserSystemAdmin(userId)) { // no permission filter for system administrators
filter.PredicateExpression.Add(CustomerAdminFields.UserId == userId | UserRoleWorkspaceFields.UserId == userId | WorkspaceFields.OwnerId == userId | CustomerFields.OwnerId == userId | SalesPersonFields.UserId == userId | ResellerFields.OwnerId == userId | ResellerAdminFields.UserId == userId);
}
EntityField2[] invitationFreeTextFilter = new EntityField2[] { CustomerFields.Name, WorkspaceFields.Name, ContactInvitationFields.InviteeEmailAddress, ProjectFields.Title, ProjectFields.Description };
AddFreeTextFilter(searchCriteria, filter.PredicateExpression, invitationFreeTextFilter);
IPrefetchPath2 prefetchPath = new PrefetchPath2(EntityType.WorkspaceEntity);
prefetchPath.Add(WorkspaceEntity.PrefetchPathOwner);
PredicateExpression invitationFilter = new PredicateExpression(new FieldCompareNullPredicate(ContactInvitationFields.InviteeUserId, null, false));
RelationCollection relations = new RelationCollection();
relations.ObeyWeakRelations = true;
if (!string.IsNullOrEmpty(searchCriteria)) {
relations.Add(ContactInvitationEntity.Relations.WorkspaceEntityUsingInviteeWorkspaceId);
relations.Add(ContactInvitationEntity.Relations.ProjectEntityUsingInviterProjectId);
relations.Add(WorkspaceEntity.Relations.CustomerEntityUsingCustomerId);
AddFreeTextFilter(searchCriteria, invitationFilter, invitationFreeTextFilter);
}
IPrefetchPathElement2 invitationPrefetchElement = prefetchPath.Add(WorkspaceEntity.PrefetchPathContactInvitations, -1, invitationFilter, relations, new SortExpression((ContactInvitationFields.InviteeEmailAddress | SortOperator.Ascending)));
invitationPrefetchElement.SubPath.Add(ContactInvitationEntity.PrefetchPathRole);
adapter.FetchEntityCollection(workspaces, filter, -1, new SortExpression(WorkspaceFields.Name | SortOperator.Ascending), prefetchPath);
return workspaces;
}
}
Joined: 22-Jan-2007
Thanks for replying David
Could you please post the AddFreeTextFilter method?
private static void AddFreeTextFilter(string searchCriteria, IPredicateExpression rootExpression, EntityField2[] searchFields) {
if (string.IsNullOrEmpty(searchCriteria)) {
return;
}
List<KeyValuePair<string, string>> terms = PrepareSearchTerms(searchCriteria);
IPredicateExpression outerFreeTextFilter = new PredicateExpression();
foreach (KeyValuePair<string, string> term in terms) {
IPredicateExpression innerFreeTextFilter = new PredicateExpression();
for (int i = 0; i < searchFields.Length; ) {
innerFreeTextFilter.AddWithOr(new FieldFullTextSearchPredicate(searchFields[i++], null, FullTextSearchOperator.Contains, term.Value));
}
switch (term.Key) {
case "AND":
outerFreeTextFilter.AddWithAnd(innerFreeTextFilter);
break;
case "OR":
outerFreeTextFilter.AddWithOr(innerFreeTextFilter);
break;
}
}
rootExpression.AddWithAnd(outerFreeTextFilter);
}
Also could you please post the relevant generated code for your collectoin and prefetchpaths?
The relevant PrefetchPath sql (it has inner join for Project even though [ContactInvitation].[InviterProjectId] is nullable):
Executed Sql Query:
Query: SELECT DISTINCT [DbName].[dbo].[ContactInvitation].[ContactInvitationId], [DbName].[dbo].[ContactInvitation].[InviteeWorkspaceId], [DbName].[dbo].[ContactInvitation].[InviterUserId], [DbName].[dbo].[ContactInvitation].[InvitedDate], [DbName].[dbo].[ContactInvitation].[InviteeUserId], [DbName].[dbo].[ContactInvitation].[InviterProjectId], [DbName].[dbo].[ContactInvitation].[InviteeEmailAddress], [DbName].[dbo].[ContactInvitation].[InviteeRoleId], [DbName].[dbo].[ContactInvitation].[SendInvitation], [DbName].[dbo].[ContactInvitation].[InviteeResellerId], [DbName].[dbo].[ContactInvitation].[ProjectRoleId] FROM ((( [DbName].[dbo].[Workspace] INNER JOIN [DbName].[dbo].[ContactInvitation] ON [DbName].[dbo].[Workspace].[WorkspaceId]=[DbName].[dbo].[ContactInvitation].[InviteeWorkspaceId]) INNER JOIN [DbName].[dbo].[Project] ON [DbName].[dbo].[Project].[ProjectId]=[DbName].[dbo].[ContactInvitation].[InviterProjectId]) INNER JOIN [DbName].[dbo].[Customer] ON [DbName].[dbo].[Customer].[CustomerId]=[DbName].[dbo].[Workspace].[CustomerId]) WHERE ( ( ( [DbName].[dbo].[ContactInvitation].[InviteeWorkspaceId] = @InviteeWorkspaceId1)) AND ( ( [DbName].[dbo].[ContactInvitation].[InviteeUserId] IS NULL AND ( ( Contains([DbName].[dbo].[Customer].[Name], @Name2) OR Contains([DbName].[dbo].[Workspace].[Name], @Name3) OR Contains([DbName].[dbo].[ContactInvitation].[InviteeEmailAddress], @InviteeEmailAddress4) OR Contains([DbName].[dbo].[Project].[Title], @Title5) OR Contains([DbName].[dbo].[Project].[Description], @Description6)))))) ORDER BY [DbName].[dbo].[ContactInvitation].[InviteeEmailAddress] ASC
Parameter: @InviteeWorkspaceId1 : Int64. Length: 0. Precision: 19. Scale: 0. Direction: Input. Value: 14.
Parameter: @Name2 : String. Length: 13. Precision: 0. Scale: 0. Direction: Input. Value: "toretester14%".
Parameter: @Name3 : String. Length: 13. Precision: 0. Scale: 0. Direction: Input. Value: "toretester14%".
Parameter: @InviteeEmailAddress4 : String. Length: 13. Precision: 0. Scale: 0. Direction: Input. Value: "toretester14%".
Parameter: @Title5 : String. Length: 13. Precision: 0. Scale: 0. Direction: Input. Value: "toretester14%".
Parameter: @Description6 : String. Length: 13. Precision: 0. Scale: 0. Direction: Input. Value: "toretester14%".
It does seem like the
relations.ObeyWeakRelations = true;
statement is ignored.
Joined: 21-Aug-2005
Instead of using ObeyWeakRelations, please use the following, overload of RelationsCollection.Add() method.
public IEntityRelation Add(
IEntityRelation relationToAdd,
JoinHint hint
)
And specify OuterJoin for the JoinHint.
Joined: 10-Nov-2006
Observed with 2.5 Final ( March 28th, 2008 ) using SQL Server and SelfServicing:
I ran across a very similar issue today, and I see from the answers here that removing the ObeyWeakRelations flag and manually specifying all the join directions is a workaround. I have many RelationCollections already defined using ObeyWeakRelations = true, and changing them to use explicit JoinHints would be quite an effort.
Will this bug be fixed in a future release?
Also, on a related note, I see that the TOP clause is missing on the prefetch query, as described here: http://www.llblgen.com/TinyForum/Messages.aspx?ThreadID=8904 Was that bug supposed to be fixed yet?
Joined: 17-Aug-2003
PLEASE don't append to old threads.
wesleysmith wrote:
Observed with 2.5 Final ( March 28th, 2008 ) using SQL Server and SelfServicing:
I ran across a very similar issue today, and I see from the answers here that removing the ObeyWeakRelations flag and manually specifying all the join directions is a workaround. I have many RelationCollections already defined using ObeyWeakRelations = true, and changing them to use explicit JoinHints would be quite an effort.
Will this bug be fixed in a future release?
No, because it's not a bug that's fixable, it's a 'flaw' in the system of how to treat other relations in the join chain: there's no real solution possible. So there are some situations where you have to specify it yourself.
Also, on a related note, I see that the TOP clause is missing on the prefetch query, as described here: http://www.llblgen.com/TinyForum/Messages.aspx?ThreadID=8904 Was that bug supposed to be fixed yet?
Yes in v2.5