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;
            }
        }

Hi Tore,

Could you please post the AddFreeTextFilter method?

Also could you please post the relevant generated code for your collectoin and prefetchpaths?

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.

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.

That worked.

Thanks very much

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?

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