在实体框架拦截器中向 DbScanExpression 添加内部联接 [英] Adding Inner Join to DbScanExpression in Entity Framework Interceptor

问题描述

我正在尝试使用实体框架 CommandTree 拦截器通过 DbContext 向每个查询添加过滤器.

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

为了简单起见，我有两个表，一个名为User"，有两列(UserId"和EmailAddress")，另一个名为TenantUser"，有两列(UserId"和TenantId").

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").

每次对 User 表进行 DbScan 时，我都想对 TenantUser 表进行内部联接并根据 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.

有一个名为 EntityFramework.Filters 的项目可以执行这些操作，但不支持复杂连接""，这似乎是我想要做的.

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.

在 TechEd 2014 的演示之后，我创建了一个使用访问者的拦截器使用以下方法将 DbScanExpressions 替换为 DbJoinExpression.一旦我开始工作，我计划将它包装在 DbFilterExpression 中，以将 TenantId 列与已知 ID 进行比较.

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

为了测试上面的代码，我将拦截器添加到 dbContext 并运行以下代码:

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:

Transient.rowtype[(l,CodeFirstDatabaseSchema.User(Nullable=True,DefaultValue=)),(r,CodeFirstDatabaseSchema.User(Nullable=True,DefaultValue=) 类型没有声明名为EmailAddress"的属性))]'.

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=))]'.

我是否错误地构建了 DbJoinExpression?还是我错过了什么?

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

推荐答案

您获得该异常的原因是因为 InnerJoin 产生了两个表中的列组合的结果，另一方面，查询应该返回那些匹配的属性类用户，因此您还需要在查询结束时使用投影.这是对我有用的代码:

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

正如您在连接操作后看到的，您通过使用指定连接条件的表达式中的 lambda 表达式别名来引用特定的连接表.因此，在我的例子中，您将 User 表称为 l，将 TennantUser 称为 r.将使用字母 l 和 r 以及将生成的 SQL 查询发送到数据库的别名.在 InnerJoin 和 Select 操作之间，您可以放置​​您需要的其他逻辑，例如 Filter 等.

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.

这篇关于在实体框架拦截器中向 DbScanExpression 添加内部联接的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！