Ok, I'm officially stumped, although I refuse to give up.

Here's what I want to do:

Select 
  g.GroupID,
  g.GroupName,
  ugdp.Value,
  ugdp.Date,
  up.FullName,
  ... < more fields > ...
From [Group] g
inner join UserProfile up ON up.UserID = @UserID -- for argument's sake
inner join user_group_dp ugdp ON ugdp.UserID = up.UserID AND ugdp.GroupID = g.Groupid
  AND ugdp.MiscPK1 = @SomePK1 AND ugdp.MiscFK2 = @SomeFK2

The part I'm really having trouble with is:

AND ugdp.GroupID = g.Groupid

The dynamic list I have built up in code looks primarily like this:

        fields.DefineField(GroupFieldIndex.GroupNm, fields.Count - 1, "RegionNm", "Region"); 
        fields.Expand(1);
        fields.DefineField(GroupFieldIndex.GroupNm, fields.Count - 1, "MarketNm", "Market");
    fields.Expand(1);
    fields.DefineField(UserProfileFieldIndex.UniqueID, fields.Count - 1, "ID");
    fields.Expand(1);
    fields.DefineField(GroupFieldIndex.UniqueID, fields.Count - 1, "RetailerID"); 
        fields.Expand(1);
        fields.DefineField(GroupFieldIndex.GroupID, fields.Count -1, "GroupID");
    fields.Expand(1);
    fields.DefineField(GroupFieldIndex.GroupNm, fields.Count - 1, "RetailerNm"); 
    fields.Expand(1);
    fields.DefineField(UserProfileFieldIndex.FirstName, fields.Count - 1, "FirstName");
    fields.Expand(1);
    fields.DefineField(UserProfileFieldIndex.LastName, fields.Count - 1, "LastName");
    
        bucket.Relations.Add(GroupEntity.Relations.GroupEntityUsingGroupIDParentGroupID, string.Empty, "Market", JoinHint.None);
    bucket.Relations.Add(GroupEntity.Relations.GroupEntityUsingGroupIDParentGroupID, "Market", "Region", JoinHint.None);
    bucket.Relations.Add(GroupEntity.Relations.GroupEntityUsingGroupIDParentGroupID, "Region", "Country", JoinHint.None);

... < Add UserProfile Relation > ...

... < This next part is done in a loop as there could be multiple > ...

        fields.Expand(1);
        string suffix = index.ToString();
        IPredicateExpression exp = new PredicateExpression();
exp.AddWithAnd(PredicateFactory.CompareValue(User_Group_DataPointFieldIndex.GroupID, ComparisonOperator.Equal, null, "GroupID"));
exp.AddWithAnd(PredicateFactory.CompareValue(User_Group_DataPointFieldIndex.DataPointID, ComparisonOperator.Equal, dataPoint.DataPointID,"CurrentValue" + suffix));
exp.AddWithAnd(PredicateFactory.CompareValue(User_Group_DataPointFieldIndex.CalendarYearID, ComparisonOperator.Equal, Convert.ToInt32(years[j].ToString()),"CurrentValue" + suffix));
exp.AddWithAnd(PredicateFactory.CompareValue(User_Group_DataPointFieldIndex.TimeFrameID, ComparisonOperator.Equal, timeFrameID,"CurrentValue" + suffix));
bucket.Relations.Add(UserProfileEntity.Relations.User_Group_DataPointEntityUsingUserID, string.Empty, "CurrentValue" + suffix, JoinHint.Left).CustomFilter = exp;

One thing to note is this is meant to be polymorphic. Multiple things use this particular method in different ways.

I've written this about 20 different ways and with one exception in the example above, if I try to alias my starting table [Group] with "Group", I consistantly get this error: "Relation at index 3 doesn't contain an entity already added to the FROM clause. Bad alias?".

So in actuality, if I take out:

exp.AddWithAnd(PredicateFactory.CompareValue(User_Group_DataPointFieldIndex.GroupID, ComparisonOperator.Equal, null, "GroupID"));

Everything works, but my relation that I'm adding is too broad and needs the GroupID (which is one of the PK's).

I apologize if I don't make much sense. I'll try to clean the post up in a few minutes or so.

Please look into 'CustomFilter' in "Using the generated code / selfservicing (adapter) / Filtering and sorting / Advanced filter usage -> Custom filters for EntityRelations"

That way you can add your AND ... to the join.

Maybe I'm missing something, I thought that was exactly what I was doing in the example above. I've used that exact technique in quite a few places. Without it, I would be in trouble.

The problem I am referring to is this line of code within that example:

exp.AddWithAnd(PredicateFactory.CompareValue(User_Group_DataPointFieldIndex.GroupID, ComparisonOperator.Equal, null, "GroupID"));

There's two problems I have with this. The Entity Group is in my from clause. I'm not sure how to alias it without being told I have a bad alias (specifying the alias as the starting entity in the first relation, for example). The second problem is that even if I did know what the alias was for the Group Entity, I'm not sure how to add it to a predicate expression to use with a future join.

The generic sql example would be something like:

FROM [Group] g
INNER JOIN User_Group_DataPoint ugdp ON ugdp.UserID = up.UserID AND ugdp.GroupID = g.GroupID

buckets seem to always require an actual, hard value which can be from an aliased entity. What I need to know how to do is replace that hard value with a reference to a field in an aliased entity. The reason being is the Entity being joined on has 5 PKs and it's important that a single entity is returned. If a collection is returned, then the query is wrong.

First let's discuss your query (what yuou want to do in the first place)

Select g.fields..... ugdp.fields.... up.fields..... From [Group] g inner join UserProfile up ON up.UserID = @UserID -- for argument's sake inner join user_group_dp ugdp ON ugdp.UserID = up.UserID AND ugdp.GroupID = g.Groupid AND ugdp.MiscPK1 = @SomePK1 AND ugdp.MiscFK2 = @SomeFK2

Would the following query do the job?

SELECT  g.fields..... ugdp.fields.... up.fields.....
FROM user_group_dp ugdp
INNER JOIN UserProfile up ON ugdp.UserID = up.UserID
INNER JOIN [Group] g ON ugdp.GroupID = g.Groupid
WHERE up.UserID  = @UserID
AND ugdp.MiscPK1 = @SomePK1 
AND ugdp.MiscFK2 = @SomeFK2

if so, please tell me if you have any problems formulating it with LLBLGen Pro classes

SELECT g.fields..... ugdp.fields.... up.fields.....
FROM user_group_dp ugdp
INNER JOIN UserProfile up ON ugdp.UserID = up.UserID
INNER JOIN [Group] g ON ugdp.GroupID = g.Groupid
WHERE up.UserID = @UserID
AND ugdp.MiscPK1 = @SomePK1
AND ugdp.MiscFK2 = @SomeFK2

This query would produce unexpected results. The relation HAS to be on user_group_dp as I am dynamically adding them and the items you put in the where clause are on a per case basis which means they are required to be in the ON statement.

The adding of the relations to user_group_dp dynamically is what makes this process polymorphic and so valuable.

So you want a Left Join on the "user_group_dp" as pecifien on you join hint in the code snippet or an Inner Join as specified on the SQL snippet?

Sorry about that. I get my wires crossed when I'm doing too many things at once and I don't put enough thought in how to explain the situation to get you up to speed properly.

The JoinHint will be a Left for the User_Group_DataPoint Entity. I have a default value in my display for records that return null for that field. I think I typed in INNER out of habit.

for the:

exp.AddWithAnd(PredicateFactory.CompareValue(User_Group_DataPointFieldIndex.GroupID, ComparisonOperator.Equal, null, "GroupID"));

You have used "GroupID" as an ObjectAlias

I think you need a CompareExpression instead of the CompareValue

You are absolutely correct. I think I read over that a few times in the document and it just didn't click.

Thanks for your time.