将内部连接添加到实体框架拦截器中的DbScanExpression [英] Adding Inner Join to DbScanExpression in Entity Framework Interceptor

问题描述

我试图使用Entity Framework CommandTree拦截器通过DbContext向每个查询添加一个过滤器。

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

为了简单起见，我有两个表（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").

每次有用户表的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的演示，我创建了一个拦截器，它使用以下方法的访问者用DbJoinExpression替换DbScanExpressions。一旦我得到了这个工作，我打算把它包装在一个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:

（Nullable = True，DefaultValue =））]'。

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生成了两个表中的列的结果另一方面，查询应该返回类User的匹配属性，因此您还需要在查询结束时使用投影。这是为我工作的代码：

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屋！