How do I Filter one side of a DbJoinExpression

Question

In EF 6.1 have created a DefaultExpressionVisitor to use with a IDbCommandTreeInterceptor. I want to know how to correctly override the DbJoinExpression visitor to filter the right hand side of the join then perform the same join but on the filtered set.

Based on various approaches (such as using BindAs etc) I get errors such as:

• No property with the name 'Extent2' is declared by the type
• The referenced variable 'Extent2' is not defined in the current scope.

but I cannot get the mix of comparable types, variables and parameter. Their is little documentation and no example for usages of DbJoinExpressions in this context.

As an example say I have an ObjectContext with People and Animals. And a Person has an Association with Animals that they own, and a Pet has an OwnerId. So the explicit Key relationship is between Person.Id == Animal.OwnerId.

I have added an Association, also a Navigation Property and called it "Cats".

So for this to be accurate, I want to filter the Collection of Animals (the right hand expression) using the AnimalType column as a discriminator.

    public override DbExpression Visit(DbJoinExpression expression)
    {
       //TODO pull these values from attributes etc
        var discriminatorColumn = "AnimalType";
        var discriminatorType = "Cat";

        //People
        DbExpressionBinding left = this.VisitExpressionBinding(expression.Left);
        //Unfiltered Animals
        DbExpressionBinding right = this.VisitExpressionBinding(expression.Right);


        //TODO Filter the right side using the AnimalType dbcolumn and re-join
        // Get the right hand collection element
        var entitySetExpression = right.Expression as DbScanExpression;

        var variableReference = right.Variable;

        // Create the property based on the variable in order to apply the equality
        var discriminatorProperty = DbExpressionBuilder.Property(variableReference, discriminatorColumn);
        var predicateExpression = DbExpressionBuilder.Equal(discriminatorProperty, DbExpression.FromString(discriminatorType));

        //Filtered Animals being Cats
        var filterExpression = DbExpressionBuilder.Filter(entitySetExpression.Bind(),predicateExpression);


        var joinCondition = this.VisitExpression(expression.JoinCondition) as DbComparisonExpression;
        DbExpressionBinding filteredRight = filterExpression.Bind();

        DbExpression newExpression = expression;
        if (!ReferenceEquals(expression.Left, left)
            || !ReferenceEquals(expression.Right, filteredRight)
            || !ReferenceEquals(expression.JoinCondition, joinCondition))
        {
            if (DbExpressionKind.InnerJoin == expression.ExpressionKind)
            {
                newExpression = DbExpressionBuilder.InnerJoin(left, filteredRight, joinCondition);
            }
            else if (DbExpressionKind.LeftOuterJoin == expression.ExpressionKind)
            {
                newExpression = DbExpressionBuilder.LeftOuterJoin(left, filteredRight, joinCondition);
            }
            else
            {
                Debug.Assert(
                    expression.ExpressionKind == DbExpressionKind.FullOuterJoin,
                    "DbJoinExpression had ExpressionKind other than InnerJoin, LeftOuterJoin or FullOuterJoin?");
                newExpression = DbExpressionBuilder.FullOuterJoin(left, filteredRight, joinCondition);
            }
        }

        return newExpression;
    }

Essentially I am looking to create a SQL join with extra filter something like:

SELECT ....
FROM People p LEFT JOIN
     Animals a ON p.Id = a.OwnerId (here ***AND a.AnimalType = 'Cat'***)
WHERE ( or here ***a.AnimalType = 'Cat'***)

Reading the source code on codeplex for the DefaultExpressionVisitor it is pushing scope variables but this method is private. This might explain the parameter scope issues I am seeing.

Any help would be appreciated.

Answer 1

Turns out to be simpler that I thought. I avoided trying to Filter the DbScanExpression and simply added another condition to the join with an AndExpression

    public override DbExpression Visit(DbJoinExpression expression)
    {
        //TODO pull these values from attributes etc
        var discriminatorColumn = "AnimalType";
        var discriminatorType = "Cat";
        //if (Attribute.GetCustomAttributes())

        //People
        DbExpressionBinding left = this.VisitExpressionBinding(expression.Left);
        //Unfiltered Animals
        DbExpressionBinding right = this.VisitExpressionBinding(expression.Right);



        // Create the property based on the variable in order to apply the equality
        var discriminatorProperty = DbExpressionBuilder.Property(right.Variable, discriminatorColumn);

        //TODO create type from discriminatorType to match property type eg string, guid, int etc
        var predicateExpression = DbExpressionBuilder.Equal(discriminatorProperty, DbExpression.FromString(discriminatorType));

        //Use existing condition and combine with new condition using And
        var joinCondition = DbExpressionBuilder.And(expression.JoinCondition, predicateExpression);



        DbExpression newExpression = expression;

        //only re-create the join if something changed
        if (!ReferenceEquals(expression.Left, left)
            || !ReferenceEquals(expression.Right, right)
            || !ReferenceEquals(expression.JoinCondition, joinCondition))
        {
            switch (expression.ExpressionKind)
            {
                case DbExpressionKind.InnerJoin:
                    newExpression = DbExpressionBuilder.InnerJoin(left, right, joinCondition);
                    break;
                case DbExpressionKind.LeftOuterJoin:
                    newExpression = DbExpressionBuilder.LeftOuterJoin(left, right, joinCondition);
                    break;
                default:
                    Debug.Assert(
                        expression.ExpressionKind == DbExpressionKind.FullOuterJoin,
                        "DbJoinExpression had ExpressionKind other than InnerJoin, LeftOuterJoin or FullOuterJoin?");
                    newExpression = DbExpressionBuilder.FullOuterJoin(left, right, joinCondition);
                    break;
            }
        }

        return newExpression;
    }