First you have to analyze how the SQL would look like, then see what LLBLGen objects you can use to get this to work, finally write your code.
I can just write you an approximate code. Instead I will show you a similar scenario. Consider the following:
EmployeeId - ReportsTo
1 2
2 NULL
3 2
4 2
5 2
6 5
7 5
8 2
9 5
Now say you want get all the employees related to "Employee 5", so if he is the boss you get the subordinate, if he is a subordinate you get the boss. In this case you expect to get employees "2,6,7,9".
To get this in SQL you would write something like:
SELECT *
FROM Employees
WHERE EmployeeId IN
(SELECT CASE (EmployeeId) WHEN @id THEN ReportsTo ELSE EmployeeId END
FROM Employees
WHERE EmployeeId = @id OR ReportsTo = @id)
We are close. We just need to translate this to LLBLGen. the "WHERE EmployeeId IN" says us that we need to use a FieldCompareSetPredicat, and the "CASE ..." statement could be written used a DBFunctionCall.
So now it's time to write code. Here is the code for the above example:
[TestMethod]
public void GetAllRelatedEmployeesToAnEmployee()
{
int employeeIdToSearch = 5;
// expression to select the right field to pick up
IExpression employeeFieldToUseExpr = new DbFunctionCall("CASE {0} WHEN " + employeeIdToSearch.ToString() + " THEN {1} ELSE {2} END",
new object[] { EmployeeFields.EmployeeId, EmployeeFields.ReportsTo, EmployeeFields.EmployeeId });
// WHERE EmployeeId IN ... filter
IPredicateExpression relatedEmployeeSubFilter = new PredicateExpression (EmployeeFields.EmployeeId == employeeIdToSearch | EmployeeFields.ReportsTo == employeeIdToSearch);
FieldCompareSetPredicate relatedEmployeeFilter = new FieldCompareSetPredicate(EmployeeFields.EmployeeId,
null, EmployeeFields.EmployeeId.SetExpression(employeeFieldToUseExpr), null, SetOperator.In,
relatedEmployeeSubFilter);
// add it to the main filter
IRelationPredicateBucket filter = new RelationPredicateBucket();
filter.PredicateExpression.Add(relatedEmployeeFilter);
// fetch the collection
EntityCollection<EmployeeEntity> employees = new EntityCollection<EmployeeEntity>();
using (var adapter = new DataAccessAdapter())
{
adapter.FetchEntityCollection(employees, filter);
}
// test
var ids = string.Join(",",
(from e in employees
select e.EmployeeId.ToString()));
Assert.AreEqual("2,6,7,9", ids);
}
So, now you can write your code. This is an approximate code (not tested):
// expression to select the right field to pick up
IExpression blinkMatchFieldToUseExpr = new DbFunctionCall("CASE {0} WHEN " + myId.ToString() + " THEN {1} ELSE {2} END",
new object[] {BlinkMatchCalculateScoreFields.MemeberGuid, BlinkMatchCalculateScoreFields.FiendGuid, BlinkMatchCalculateScoreFields.MemeberGuid });
// WHERE Member.MemberGuid IN ... filter
IPredicateExpression relatedBlinkMatchSubFilter = new PredicateExpression (
BlinkMatchCalculateScoreFields.MemeberGuid == myId | BlinkMatchCalculateScoreFields.FriendGuid == myId);
FieldCompareSetPredicate relatedBlinkMatchFilter = new FieldCompareSetPredicate(MemberFields.MemeberGuid,
null, BlinkMatchCalculateScoreFields.MemeberGuid.SetExpression(blinkMatchFieldToUseExpr), null, SetOperator.In,
relatedBlinkMatchSubFilter );
// here is your code
EntityCollection<MemberEntity> members = new EntityCollection<MemberEntity>();
RelationPredicateBucket bucket = new RelationPredicateBucket();
bucket.PredicateExpression.Add(MemberFields.MemberStatus == 1);
bucket.Relations.Add(MemberEntity.Relations.BlinkMatchCalculateScoreEntityUsingMemberGuid);
bucket.Relations.Add(MemberEntity.Relations.BlinkMatchCalculateScoreEntityUsingFriendGuid);
bucket.PredicateExpression.AddWithAnd(BlinkMatchCalculateScoreFields.Percentage >= 75);
// need the next filter here
bucket.PredicateExpression.Add(relatedBlinkMatchFilter);
