Been working on this one all week, and can't seem to get it to going. It is returning a list of any overlapping graphic objects in a GraphicObject table. The SQL Statement is this:

select g1.Page_ID, g2.Page_ID, g1.Parent_GraphicObject_ID, g2.Parent_GraphicObject_ID, g2.GraphicObject_ID, g1.PositionX + g1.Width, g2.PositionX, g1.PositionY + g1.Height, g2.PositionY from GraphicObject g1, GraphicObject g2

        where g1.PositionX + g1.Width >= g2.PositionX 
        and g2.PositionX >= g1.PositionX
        and g1.PositionY + g1.Height >= g2.PositionY
        and g2.PositionY >= g1.PositionY
        and g1.GraphicObject_ID != g2.GraphicObject_ID
        and g1.Page_ID = g2.Page_ID
        and g1.Parent_GraphicObject_ID = g2.Parent_GraphicObject_ID

I was using a dynamic typed list, defining the fields and aliasing them, and then using expressions on the various where clauses - but I can't seem to get expressions into the DefineFields function - I'd send you my current code, but I'd bastardized it so much that it frankly doesn't make sense to me anymore, and I'd just be embarrased.

Here's the SQL Table: CREATE TABLE [dbo].[GraphicObject] ( [GraphicObject_ID] [uniqueidentifier] NOT NULL , [GraphicObjectType_EnumValue] [tinyint] NOT NULL , [Page_ID] [int] NOT NULL , [PositionX] [smallint] NOT NULL , [PositionY] [smallint] NOT NULL , [Height] [smallint] NOT NULL , [Width] [smallint] NOT NULL , [IsShortcut] [booleanvalue] NULL , [Parent_GraphicObject_ID] [uniqueidentifier] NULL , [Linked_GraphicObject_ID] [uniqueidentifier] NULL , )

And yes I know about GUIDs and slowness, but this is working out since a lot of the work can be done offline - so I just use the ObjectID from LLBLGen's objects - which works out real nice (got several people working on these "pages" at one time, each updating / adding graphics).

Thanks for any help, Brian

Oh, I even gave an old version of the SQL that was working, the following replaces the Parent_GraphicObject_ID where clause:

and ((g1.Parent_GraphicObject_ID is null and g2.Parent_GraphicObject_ID is null) or (g1.Parent_GraphicObject_ID = g2.Parent_GraphicObject_ID))

since they can be null...

I would setup a view to simplify your results and then use the FieldCompareExpressionPredicate to filter against the view and return the results you are looking for.

I'm guessing make the view in the database and then use it. I would like to avoid that route - as it would be the only one - as so far we have no custom stored procs and no views. If it can't be done with a dynamic types then I'll just go back to doing in in memory with objects. Which'll prove pretty slow when it comes time to audit 50,000 pages with over a mill graphics, but oh well.

Thanks Brian

Brian wrote:

I'm guessing make the view in the database and then use it. I would like to avoid that route - as it would be the only one - as so far we have no custom stored procs and no views. If it can't be done with a dynamic types then I'll just go back to doing in in memory with objects. Which'll prove pretty slow when it comes time to audit 50,000 pages with over a mill graphics, but oh well.

It can be done with a dynamic list, but it will be a complex piece of code. The main issue is that you join 2 times the same table without a relation.

I can rewrite your query as:

SELECT  g1.Page_ID, 
        g2.Page_ID, 
        g1.Parent_GraphicObject_ID, 
        g2.Parent_GraphicObject_ID, 
        g2.GraphicObject_ID, 
        g1.PositionX + g1.Width, 
        g2.PositionX, 
        g1.PositionY + g1.Height, 
        g2.PositionY 
FROM    GraphicObject g1 INNER JOIN GraphicObject g2
        ON
        g1.GraphicObject_ID != g2.GraphicObject_ID
WHERE   
        g2.PositionX >= g1.PositionX
        /* unneccessary, as when g2.PositionX is >= g1.PositionX, 
         * g1.PositionX + g1.Width >= g2.PositionX will always be true
         * AND 
         * g1.PositionX + g1.Width >= g2.PositionX
         */
        AND 
        g2.PositionY >= g1.PositionY
        /* unnecessary, same with above
         * AND 
         * g1.PositionY + g1.Height >= g2.PositionY
         * AND 
         */
        g1.Page_ID = g2.Page_ID
        AND 
        (
            (
                g1.Parent_GraphicObject_ID is null 
                AND 
                g2.Parent_GraphicObject_ID is null
            ) 
            OR
            (
                g1.Parent_GraphicObject_ID = g2.Parent_GraphicObject_ID
            )
        )

which then results in the shorter query (and just 1 alias)

SELECT  GraphicObject.Page_ID AS 'PageID', 
        g2.Page_ID AS 'g2PageID', 
        GraphicObject.Parent_GraphicObject_ID AS 'ParentGraphicObject', 
        g2.Parent_GraphicObject_ID AS 'g2ParentGraphicObject', 
        g2.GraphicObject_ID, 
        GraphicObject.PositionX + GraphicObject.Width AS 'PositionXPlusWidth', 
        g2.PositionX, 
        GraphicObject.PositionY + GraphicObject.Height AS 'PositionYPlusHeight', 
        g2.PositionY 
FROM    GraphicObject INNER JOIN GraphicObject g2
        ON
        GraphicObject.GraphicObject_ID != g2.GraphicObject_ID
WHERE   
        g2.PositionX >= GraphicObject .PositionX
        AND 
        g2.PositionY >= GraphicObject .PositionY
        AND 
        g2.Page_ID = GraphicObject.Page_ID
        AND 
        (
            (
                GraphicObject.Parent_GraphicObject_ID is null 
                AND 
                g2.Parent_GraphicObject_ID is null
            ) 
            OR
            (
                g2.Parent_GraphicObject_ID = GraphicObject.Parent_GraphicObject_ID 
            )
        )

Ok, in for a ride? here we go!

// first define the join relation. We need to specify a filter as well. 
EntityRelation relation = new EntityRelation(RelationType.OneToMany);
relation.AddEntityFieldPair(
    EntityFieldFactory.Create(GraphicObjectFieldIndex.GraphicObjectID),
    EntityFieldFactory.Create(GraphicObjectFieldIndex.GraphicObjectID));
PredicateExpression onClauseFilter = new PredicateExpression();
onClauseFilter.Add(PredicateFactory.CompareExpression(
    GraphicObjectFieldIndex.GraphicObjectID, ComparisonOperator.NotEqual, 
        new Expression(EntityFieldFactory.Create(GraphicObjectFieldIndex.GraphicObjectID)), "g2"));
RelationCollection relations = new RelationCollection();
relations.Add(relation, "g2", JoinHint.Inner).CustomFilter = onClauseFilter;
relation.CustomFilterReplacesOnClause = true;

// now we define the resultset. 
ResultsetFields fields = new ResultsetFields(9);
fields.DefineField(GraphicObjectFieldIndex.PageID, 0, "g1PageID");
fields.DefineField(GraphicObjectFieldIndex.PageID, 1, "g2PageID", "g2");
fields.DefineField(GraphicObjectFieldIndex.ParentGraphicObjectID, 2, "g1ParentGraphicObject");
fields.DefineField(GraphicObjectFieldIndex.ParentGraphicObjectID, 3, "g2ParentGraphicObject", "g2");
fields.DefineField(GraphicObjectFieldIndex.GraphicObjectID, 4, "GraphicObjectID", "g2");
fields.DefineField(GraphicObjectFieldIndex.PositionX, 5, "g1PositionXPlusWidth");
fields.DefineField(GraphicObjectFieldIndex.PositionX, 6, "PositionX", "g2");
fields.DefineField(GraphicObjectFieldIndex.PositionY, 7, "g1PositionYPlusHeight");
fields.DefineField(GraphicObjectFieldIndex.PositionY, 8, "PositionY", "g2");

// now we'll define the expressions for the resultset fields and apply them to the fields.
// g1.PositionX + GraphicObject.Width
IExpression g1PositionXPlusWidthEx = new Expression(
    EntityFieldFactory.Create(GraphicObjectFieldIndex.PositionX), ExOp.Add,
    EntityFieldFactory.Create(GraphicObjectFieldIndex.Width));
// apply it to the field
fields[5].ExpressionToUse = g1PositionXPlusWidthEx;

// g1.PositionY + GraphicObject.Height
IExpression g1PositionYPlusHeightEx = new Expression(
    EntityFieldFactory.Create(GraphicObjectFieldIndex.PositionY), ExOp.Add,
    EntityFieldFactory.Create(GraphicObjectFieldIndex.Height));
// apply it to the field
fields[7].ExpressionToUse = g1PositionYPlusHeightEx;

// now we'll define the where clauses. 
IPredicateExpression whereFilter= new PredicateExpression();
// g2.PositionX >= GraphicObject.PositionX
whereFilter.Add(PredicateFactory.CompareExpression(
    GraphicObjectFieldIndex.PositionX, ComparisonOperator.GreaterEqual, 
        new Expression(EntityFieldFactory.Create(GraphicObjectFieldIndex.PositionX)), "g2"));
// g2.PositionY >= GraphicObject.PositionY
whereFilter.AddWithAnd(PredicateFactory.CompareExpression(
    GraphicObjectFieldIndex.PositionY, ComparisonOperator.GreaterEqual, 
        new Expression(EntityFieldFactory.Create(GraphicObjectFieldIndex.PositionY)), "g2"));
// g2.Page_ID = GraphicObject.Page_ID
whereFilter.AddWithAnd(PredicateFactory.CompareExpression(
    GraphicObjectFieldIndex.PageID, ComparisonOperator.Equal, 
        new Expression(EntityFieldFactory.Create(GraphicObjectFieldIndex.PageID)), "g2"));
// and with an or
PredicateExpression subAndExpression = new PredicateExpression();
PredicateExpression subAndLeftExpression = new PredicateExpression();
subAndLeftExpression.Add(PredicateFactory.CompareNull(GraphicObjectFieldIndex.ParentGraphicObjectID));
subAndLeftExpression.AddWithAnd(PredicateFactory.CompareNull(GraphicObjectFieldIndex.ParentGraphicObjectID, "g2"));
subAndExpression.Add(subAndLeftExpression);
subAndExpression.AddWithOr(PredicateExpression.CompareExpression(GraphicObjectFieldIndex.ParentGraphicObjectID, 
    ComparisonOperator.Equal, new Expression(EntityFieldFactory.Create(GraphicObjectFieldIndex.ParentGraphicObjectID)), "g2");
whereFilter.AddWithAnd(subAndExpression);

// and now you're ready to fetch using the dynamic fetch code.

As you can see, it's very verbose, mostly due to the lack of helper methods to easily set things while you're creating something.

In 1.0.2005.1, this will change, as a matter of fact, today I start with the implementation of these particular enhancements, so this was a good excercise what to improve

Holy Crap load of code batman,

Got it in there, and have been messing around with it - I was close in a couple places, but not even close in others... - um, what is this line: relation.CustomFilterReplacesOnClause = true;

I understand what its intent is, but my current version doesn't mention it. I'm running: LLBLGen RunTime version: 10212004 - (works great haven't had a reason to upgrade) so now I imagine you'll tell me I need the new one. So I've downloaded that and will proceed onward.

As always you impress me, Brian.