Adding Inner Join to DbScanExpression in Entity Fr

Question

I'm trying to use an Entity Framework CommandTree interceptor to add a filter to every query via a DbContext.

For the sake of simplicity, I have two tables, one called "User" with two columns ("UserId" and "EmailAddress") and another called "TenantUser" with two columns ("UserId" and "TenantId").

Each time there is a DbScan of the User table, I want to do an inner join against the TenantUser table and filter based on the TenantId column.

There is a project called EntityFramework.Filters that does something along these lines, but doesn't support "complex joins", which seems to be what I'm trying to do.

Following a demo from TechEd 2014, I created an interceptor that uses a visitor with the method below to replace DbScanExpressions with a DbJoinExpression. Once I get that working, I plan to wrap it in a DbFilterExpression to compare the TenantId column with a known ID.

    public override DbExpression Visit(DbScanExpression expression)
    {
        var table = expression.Target.ElementType as EntityType;
        if (table != null && table.Name == "User")
        {
            return DbExpressionBuilder.InnerJoin(expression, DbExpressionBuilder.Scan(expression.Target), (l, r) =>
                DbExpressionBuilder.Equal(DbExpressionBuilder.Variable(tenantUserIdProperty.TypeUsage, "UserId"),
                    DbExpressionBuilder.Variable(userIdProperty.TypeUsage, "UserId")));
        }

        return base.Visit(expression);
    }

To test the code above, I've added the interceptor to the dbContext and run the following code:

    dbContext.Users.Select(u => new { u.EmailAddress }).ToList();

However, this results in the following error:

No property with the name 'EmailAddress' is declared by the type 'Transient.rowtype[(l,CodeFirstDatabaseSchema.User(Nullable=True,DefaultValue=)),(r,CodeFirstDatabaseSchema.User(Nullable=True,DefaultValue=))]'.

Am I building the DbJoinExpression incorrectly? Or am I missing something else?

Answer 1

The reason you obtained that exception is because InnerJoin produces a result combined of columns from both tables and on the other hand the query is supposed to return those matching properties of class User, so you additionally need to use projection at the end of query. Here is the code which worked for me:

public override DbExpression Visit(DbScanExpression expression)
{
    var table = expression.Target.ElementType as EntityType;
    if (table != null && table.Name == "User")
    {
        return expression.InnerJoin(
            DbExpressionBuilder.Scan(expression.Target.EntityContainer.BaseEntitySets.Single(s => s.Name == "TennantUser")),
            (l, r) =>
                DbExpressionBuilder.Equal(
                    DbExpressionBuilder.Property(l, "UserId"),
                    DbExpressionBuilder.Property(r, "UserId")
                )
        )
        .Select(exp => 
            new { 
                UserId = exp.Property("l").Property("UserId"), 
                Email = exp.Property("l").Property("Email") 
            });
    }

    return base.Visit(expression);
}

As you see after join operation you refer to specific joined table by using its lambda expression alias from expression specifying join condition. So in my case you refer to User table as l and to TennantUser as r. Letters l and r will be used as well as aliases in resulting SQL query sent to database. In between InnerJoin and Select operations you may place additional logic you need like Filter etc.