Adding Inner Join to DbScanExpression in Entity Framework Interceptor

c# entity-framework entity-framework-6 entity-framework-6.1

Question

I'm attempting to utilize a DbContext and an Entity Framework CommandTree interceptor to add a filter to each query.

I have two tables, one called "User" and having two columns (UserId and EmailAddress), and the other being called "TenantUser" and having two columns ("UserId" and "TenantId").

I want to perform an inner join against the TenantUser table whenever a DbScan of the User database is performed, and I want to filter using the TenantId column.

While EntityFramework.Filters is a project that accomplishes something similar, it does not enable "complicated joins," which appears to be what I'm attempting to do.

I developed an interceptor that employs a visitor and the following technique to swap out DbScanExpressions for a DbJoinExpression in the wake of the 2014 TechEd demo. I want to use a DbFilterExpression to compare the TenantId column with a known ID once I have that functioning.

    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);
    }

I've added the interceptor to the dbContext and executed the following code to test the code above:

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

But this leads to the following mistake:

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 incorrectly constructing the DbJoinExpression? Or am I overlooking something?

1
14
2/12/2015 6:44:54 AM

Accepted Answer

Due to the fact that InnerJoin results in a result that combines columns from both tables but the query is intended to return the matching properties of class User, you also need to utilize projection at the conclusion of the query. Here is the code that I found to be effective:

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 can see, using the lambda expression alias from the expression defining the join condition, you can refer to a specific joined table following a join operation. Therefore, in my case, you refer to TennantUser as r and User table as l. In the final SQL query that is delivered to the database, the letters l and r will be used as well as aliases. You can include any necessary additional logic, such as a filter, between the inner join and the select operations.

3
4/2/2015 7:34:20 PM






Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow