Search code examples
c#sqlinner-joinllblgen

How do I perform a join or place a where clause on a relation or prefetch? (LLBLGen)

I have the following code that I've been able to cobble together by looking at a bunch of different sources online, however the below actually throws a null reference exception on the bottom line.

GroupAgentEntity agent = new GroupAgentEntity();

RelationPredicateBucket pred = new RelationPredicateBucket();
pred.PredicateExpression.Add(PredicateFactory.CompareValue(GroupAgentFieldIndex.GroupId, ComparisonOperator.Equal, groupId));
pred.PredicateExpression.Add(PredicateFactory.CompareValue(GroupAgentFieldIndex.IsPrimary, ComparisonOperator.Equal, true));
pred.PredicateExpression.Add(PredicateFactory.CompareValue(GroupAgentFieldIndex.BeginDate, ComparisonOperator.LessEqual, DateTime.Now));
pred.PredicateExpression.Add(PredicateFactory.CompareNull(GroupAgentFieldIndex.EndDate));

pred.Relations.Add(GroupAgentEntity.Relations.AgentSplitGroupEntityUsingAgentSplitGroupId);

IPredicateExpression preFilter = new PredicateExpression(AgentSplitGroupFields.Name == "Broker");

PrefetchPath2 pre = new PrefetchPath2((int)EntityType.AgentSplitGroupEntity);
pre.Add(AgentSplitGroupEntity.PrefetchPathAgentSplitGroup, 0, preFilter);

if (deep)
{
    pre.Add(GroupAgentEntity.PrefetchPathBroker);
    pre.Add(GroupAgentEntity.PrefetchPathCarrierBroker);
}

this.DataAdapter.FetchEntityUsingUniqueConstraint(agent, pred.PredicateExpression, pre);

What I'm attempting to do is join from the GroupAgent table to the AgentSplitGroup table as well as prefetch an entity of type AgentSplitGroupEntity. Unfortunately my experience with LLBLGen is rather limited (especially when it comes to predicate expressions).

Could anyone please provide any ideas as to why the above code is throwing a null reference exception on the bottom line? That, or if someone could help me to pull off the query I need in another way (but still using predicate expressions), I'd greatly appreciate it.

Note that I did the obvious thing and checked the parameters going to FetchEntityUsingUniqueConstraint(...) during runtime, and none of them are null.

Solution

  • I'm not LLBLGen expert but you can change your PredicateExpression from:

    pred.PredicateExpression.Add(PredicateFactory.CompareValue(GroupAgentFieldIndex.GroupId, ComparisonOperator.Equal, groupId));

    with:

    pred.PredicateExpression.Add(GroupAgentFields.GroupId == groupId);

    Other operators work also. Null is DBNull.Value

    But I think your problem is actually in Prefetch, it should look like this:

    RelationPredicateBucket preFilter = new RelationPredicateBucket();
preFilter.Relations.Add(GroupAgentEntity.Relations.AgentSplitGroupEntityUsingAgentSplitGroupId);
preFilter.PredicateExpression.Add(AgentSplitGroupFields.Name == "Broker");
PrefetchPath2 pre = new PrefetchPath2((int)EntityType.AgentGroupEntity);
pre.Add(AgentGroupEntity.PrefetchPathAgentSplitGroup, 0, preFilter.PredicateExpression, preFilter.Relations);