Hello,

I have to say, I am new to LLBLGen, so I might be making a mistake myself. But from what I can see the AddWithOr is not working.

What do I work with? LLBLGen 5.1 Pro, SelfServicing with the LLBLGen Pro Runtime Framework.

The SQL query I'm working with:

SELECT *
FROM [User] as u1
LEFT OUTER JOIN Employee AS e ON e.UserId = u1.Id
LEFT OUTER JOIN [User] AS u2 ON u2.Id = e.UserId
WHERE u2.Id IS NULL OR u2.Id = X;

Where X is some value.

And here's my first method, which works perfectly, this one is without the OR statement.

public static UserCollection GetAllUnlinkedUsers()
        {
            var users = new UserCollection();
            var bucket = new RelationPredicateBucket() as IRelationPredicateBucket;
            
            // Relations
            bucket.Relations.Add(UserEntity.Relations.EmployeeEntityUsingUserId, JoinHint.Left)
                .CustomFilter = new PredicateExpression { EmployeeFields.UserId == UserFields.Id };

            // Predicate
            bucket.PredicateExpression.Add(EmployeeFields.UserId == DBNull.Value);

            // Sorting
            var sorter = new SortExpression
            {
                UserFields.Username | SortOperator.Ascending
            };

            users.GetMulti(bucket.PredicateExpression, -1, sorter, bucket.Relations);

            return users;
        }

And here the same code, but with AddWithOr (as in the query shown above):

public static UserCollection GetAllUnlinkedUsers(int includeUserId)
        {
            var users = new UserCollection();
            var bucket = new RelationPredicateBucket() as IRelationPredicateBucket;

            // Relations
            bucket.Relations.Add(UserEntity.Relations.EmployeeEntityUsingUserId, JoinHint.Left)
                .CustomFilter = new PredicateExpression { EmployeeFields.UserId == UserFields.Id };

            // Predicate
            bucket.PredicateExpression.Add(EmployeeFields.UserId == DBNull.Value);
            bucket.PredicateExpression.AddWithOr(EmployeeFields.UserId == includeUserId);

            // Sorting
            var sorter = new SortExpression
            {
                UserFields.Username | SortOperator.Ascending
            };

            users.GetMulti(bucket.PredicateExpression, -1, sorter, bucket.Relations);

            return users;
        }

And with the second method I just receive the exact same output as with the first method (without the parameter).

Where are these methods called?

private void PreFillUnlinkedUsers()
        {
            dynamic users = null;

            // Add
            if (!this.HasEmployeeId)
            {
                users = UserReader.GetAllUnlinkedUsers();

                for (int i = 0; i < users.Count; i++)
                {
                    this._ddlUser.Items.Add(new ListItem(users[i].Username, users[i].Id.ToString()));
                }

                this._ddlUser.Items.Insert(0, new ListItem("Selecteer gebruiker", string.Empty));
            }
            // Edit
            else
            {
                users = UserReader.GetAllUnlinkedUsers(this.Employee.Id);

                for (int i = 0; i < users.Count; i++)
                {
                    this._ddlUser.Items.Add(new ListItem(users[i].Username, users[i].Id.ToString()));
                }

                this._ddlUser.Items.Insert(0, new ListItem("Selecteer gebruiker", string.Empty));
                this._ddlUser.SelectedValue = this.Employee.UserId.ToString();
            }
        }

I am not sure if I'm doing something wrong, or if I misunderstood something from the documentation or what. But I can't seem to get this working.

You are fetching Users, and you are filtering on Employees which you have outer-Joined. So whatever filtering you are doing there, all Users will be returned.

I believe you need to Inner Join the Employees, for the filtering to take effect.

On another note: you don't need the CustomFilter used in the Join relation, the relation filter is automatically considered. Only use CustomFilter if you want to add extra filtering on the relation or if you want to substitute the default PK-FK filter.

P.S. It's always helpful in such cases to examine the output queries. Please check DQE Tracing

Walaa wrote:

You are fetching Users, and you are filtering on Employees which you have outer-Joined them. So whatever filtering you are doing there, all Users will be returned.

I believe you need to Inner Join the Employees, for the filtering to take effect.

On another note: you don't need the CustomFilter used in the Join relation, the relation filter is automatically considered. Only use CustomFilter if you want to add extra filtering on the relation or if you want to substitute the default PK-FK filter.

P.S. It's always helpful in such cases to examine the output queries. Please check DQE Tracing

Thank you very much for the help, and the debug feature was extremely useful. Shame I didn't know about that sooner haha.

Still got a lot to learn about LLBLGen, but I'm getting there! Love working with it so far

Rlaan wrote:

Still got a lot to learn about LLBLGen, but I'm getting there! Love working with it so far