Hello, I know this is possible, but can't seem to get it working properly.

Got a 1:n relation between Page and its Properties. Page Page_ID Page_Name etc...

PropertyValuesForPage Page_ID Property_Name Property_Value etc...

I've got two properties that I'm interested in with names of "VersionMostRecent" and "VersionLastApproved". Both properties will have a version number in the Property_Value that I'd like to compare. I'd like to only get back page_IDs that have different property_values for these two properties.

In SQL it'd be this:

{select p1.Page_ID from PropertyValuesForPage p1, PropertyValuesForPage p2 where p1.Property_Name = 'Page.Background.VersionMostRecent' and p2.Property_Name = 'Page.Background.VersionLastApproved' and p1.Page_ID = p2.Page_ID and p1.Property_Value != p2.Property_Value}

I've tried the relations with predicates on the PageEntity, but that doesn't seem to work - I hope I'm just missing something simple.

Thanks, Brian

I've done it a bit differently, with an extra join, as you specify a join between two tables without a relation (which is doable in SQL, but not in LLBLGen Pro)

It's selfservicing, but adapter is pretty the same. I've commented the code abit so you see teh connection between the sql and the predicates. I haven't tested it in full as I don't have a database setup like your situation, though it should give the same results.

ResultsetFields fields = new ResultsetFields(1);
fields.DefineField(PageFieldIndex.PageID, 0, "PageID");

// Page INNER JOIN PropertyValuesForPage p1 ON Page.PageID = p1.PageID
// INNER JOIN PropertyValuesForPage p2 ON Page.PageID = p2.PageID
RelationCollection relations = new RelationCollection();
relations.Add(PageEntity.Relations.PropertyValuesForPageEntityUsingPageID, "p1");
relations.Add(PageEntity.Relations.PropertyValuesForPageEntityUsingPageID, "p2");

PredicateExpression filter = new PredicateExpression();
// Add:
// p1.Property_Name = 'Page.Background.VersionMostRecent' 
// and
// p2.Property_Name = 'Page.Background.VersionLastApproved' 
filter.Add(
    new PredicateExpression(
        Predicatefactory.CompareValue(
            PropertyValuesForPageFieldIndex.PropertyName, ComparisonOperator.Equal,
            "Page.Background.VersionMostRecent", "p1"),
        Predicatefactory.CompareValue(
            PropertyValuesForPageFieldIndex.PropertyName, ComparisonOperator.Equal,
            "Page.Background.VersionMostRecent", "p2")));
// Add:
// p1.PropertyValue != p2.PropertyValue
IEntityField p2PropertyValue = EntityFieldFactory.Create(PropertyValuesForPageFieldIndex.PropertyValue);
p2PropertyValue.ObjectAlias = "p2";
filter.Add(PredicateFactory.CompareExpression(
    PropertyValuesForPageFieldIndex.PropertyValue, ComparisonOperator.NotEqual, 
        new Expression(p2PropertyValue)));

// fetch the dynamic list. 
DataTable dynamicList = new DataTable();
TypedListDAO dao = new TypedListDAO();
dao.GetMultiAsDataTable(fields, dynamicList, 0, null, filter, relations, false, null, null, 0, 0);

// you can also use the relation and predicate to fetch entities:
PageCollection pages = new PageCollection();
pages.GetMulti(filter, relations);

To speed up the joins, you can move some predicates to the relation's as a CustomFilter, which you can specify as a property like: relations.Add(...).CustomFilter = ...;

You rock!

Worked like a champ.

Thanks, Brian.

Cool!