I have the following dynamic list which work great. However I also want to remove any primary and foreign keys from the dt table as well. Not sure how to add a filter to do this. Can someone provide some help here? I have another option.

1. Build the resultset with only the fields I need, the problem is that I need to query on fields that are not in the resultset. Not sure how to do this.

What's the best way to do this?

public static DataTable GetMilestoneData(MilestoneEntity milestone, OrganizationPageEntity targetOrganizationPage)
    {
        DataTable dt = new DataTable();
        DataAccessAdapter adapter = new DataAccessAdapter();

        ResultsetFields fields = new ResultsetFields(9);
        fields.DefineField(MilestoneFieldIndex.MilestoneId, 0, "MilestoneId");
        fields.DefineField(MilestoneFieldIndex.Name, 1,"Name");
        fields.DefineField(MilestoneDataFieldIndex.PlannedDate, 2,"PlannedDate");
        fields.DefineField(StatusTypeFieldIndex.Name, 3, "Status");
        fields.DefineField(MilestoneDataFieldIndex.ActualDate, 4,"ActualDate");
        fields.DefineField(MilestoneDataFieldIndex.OrganizationPageId, 5, "OrganizationPageId");
        fields.DefineField(MilestoneDataFieldIndex.TargetOrganizationPageId, 6, "TargetPageId");
        fields.DefineField(MilestoneDataFieldIndex.CompanyId, 7, "CompanyId");
        fields.DefineField(MilestoneFieldIndex.Description, 8, "Description");

        RelationPredicateBucket bucket = new RelationPredicateBucket();
        bucket.Relations.Add(MilestoneDataEntity.Relations.StatusTypeEntityUsingStatusTypeId);
        bucket.Relations.Add(MilestoneDataEntity.Relations.MilestoneEntityUsingMilestoneId);

        IPredicateExpression filter = new PredicateExpression();
        filter.Add(PredicateFactory.CompareValue(MilestoneDataFieldIndex.MilestoneId, ComparisonOperator.Equal, milestone.MilestoneId));
        filter.Add(PredicateFactory.CompareValue(MilestoneDataFieldIndex.TargetOrganizationPageId, ComparisonOperator.Equal, targetOrganizationPage.OrganizationPageId));
        bucket.PredicateExpression.Add(filter);
        adapter.FetchTypedList(fields, dt, bucket, false);

        return dt;
    }

Eric,

If I'm reading your question correctly, you need to add a CustomFilter to your entity relationship. Take a look The "Custom Filters for EntityRelations" section of this LLBLGen User Manual Page: Generated code - Filtering and sorting, Adapter.

Paul

Paul, thanks for the info but I'm still a little confused. Basically I don't understand how to create a predicate expression that tests whether a given field is primary key or foreign key.

Or maybe there's a different approach.

You want to filter on fields in an entity related to one or more entities in the select list of your dynamic list? If not I don't understand the question, so a simple example (of what you want, not code) would probably help

I probably did not word this correctly so I will try again. The dynamic list code presented above works fine with one exeception. I only want the resulting datatable to display columns which are not primary or foreign keys. Since the resulting datatable will be bound to a datagrid, there is no need for a user to see the primary or foreign keys.

I think I may be going about this the wrong way. Maybe I should have the resultset contain just the columns I need(no primary or foreign key columns). But I couldn't figure out how to filter on values which are not in the resultset. So in the code above I have:

ResultsetFields fields = new ResultsetFields(9); fields.DefineField(MilestoneFieldIndex.MilestoneId, 0, "MilestoneId"); fields.DefineField(MilestoneFieldIndex.Name, 1,"Name"); fields.DefineField(MilestoneDataFieldIndex.PlannedDate, 2,"PlannedDate"); fields.DefineField(StatusTypeFieldIndex.Name, 3, "Status"); fields.DefineField(MilestoneDataFieldIndex.ActualDate, 4,"ActualDate"); fields.DefineField(MilestoneDataFieldIndex.OrganizationPageId, 5, "OrganizationPageId"); fields.DefineField(MilestoneDataFieldIndex.TargetOrganizationPageId, 6, "TargetPageId"); fields.DefineField(MilestoneDataFieldIndex.CompanyId, 7, "CompanyId"); fields.DefineField(MilestoneFieldIndex.Description, 8, "Description");

So maybe my ResultsetFields should look something like this instead:

ResultsetFields fields = new ResultsetFields(5); fields.DefineField(MilestoneFieldIndex.Name, 0,"Name"); fields.DefineField(MilestoneDataFieldIndex.PlannedDate, 1,"PlannedDate"); fields.DefineField(StatusTypeFieldIndex.Name, 2, "Status"); fields.DefineField(MilestoneDataFieldIndex.ActualDate, 3,"ActualDate"); fields.DefineField(MilestoneFieldIndex.Description, 4, "Description");

But how does one filter on columns which are not in the resultfields?

[Edit] Ok, I don't know what I was doing wrong before, but I modified the resultsetFields to use the second example and everything worked. So for now, I don't have any issues.

Yes, that second collection should work fine.

You have to disctinct two things: resultset fieldslist and selectlist fieldslist.

The selectlist fieldslist is the list of ALL the fields of ALL the tables/views in the FROM clause. The resultset fieldslist is the list of fields you want to return in a resultset.

The WHERE and other clauses of a SELECT statement work on the selectlist fieldslist (there are exceptions but that's not important now), so you can filter on fields not in the selectlist fieldslist and you don't have to specify every field of the selectlist fieldslist in your resultset fieldslist.

ResultsetFields object defines teh fields of the Resultset fieldslist. LLBLGen pro will use the entity the fields are from IF no relations are specified, for the FROM clause. If there is a relation collection with relations specified, that's used for the FROM clause.

So to avoid pk/fk's, just don't mention them in the ResultsetFields