postgresql 包含非 EF 方法调用的 Select 子句

Select clause containing non-EF method calls

提问by Justin Grant

I'm having trouble building an Entity Framework LINQ query whose select clause contains method calls to non-EF objects.

我在构建实体框架 LINQ 查询时遇到问题，该查询的 select 子句包含对非 EF 对象的方法调用。

The code below is part of an app used to transform data from one DBMS into a different schema on another DBMS. In the code below, Role is my custom class unrelated to the DBMS, and the other classes are all generated by Entity Framework from my DB schema:

下面的代码是用于将数据从一个 DBMS 转换为另一个 DBMS 上的不同模式的应用程序的一部分。在下面的代码中，Role 是我的自定义类，与 DBMS 无关，其他类都是由 Entity Framework 从我的 DB schema 生成的：

// set up ObjectContext's for Old and new DB schemas
var New = new NewModel.NewEntities();
var Old = new OldModel.OldEntities();

// cache all Role names and IDs in the new-schema roles table into a dictionary
var newRoles = New.roles.ToDictionary(row => row.rolename, row => row.roleid);

// create a list or Role objects where Name is name in the old DB, while
// ID is the ID corresponding to that name in the new DB
var roles = from rl in Old.userrolelinks
            join r in Old.roles on rl.RoleID equals r.RoleID
            where rl.UserID == userId
            select new Role { Name = r.RoleName, ID = newRoles[r.RoleName] };
var list = roles.ToList();

But calling ToList gives me this NotSupportedException:

但是调用 ToList 给了我这个 NotSupportedException：

LINQ to Entities does not recognize the method 'Int32 get_Item(System.String)' method, and this method cannot be translated into a store expression

LINQ to Entities 无法识别方法 'Int32 get_Item(System.String)' 方法，并且该方法无法转换为存储表达式

Sounds like LINQ-to-Entities is barfing on my call to pull the value out of the dictionary given the name as a key. I admittedly don't understand enough about EF to know why this is a problem.

听起来像 LINQ-to-Entities 正在呼唤我将名称从字典中提取出来作为键的值。诚然，我对 EF 的了解不够，不知道为什么这是一个问题。

I'm using devart's dotConnect for PostgreSQLentity framework provider, although I assume at this point that this is not a DBMS-specific issue.

我正在使用 devart 的dotConnect for PostgreSQL实体框架提供程序，尽管此时我认为这不是特定于 DBMS 的问题。

I know I can make it work by splitting up my query into two queries, like this:

我知道我可以通过将我的查询分成两个查询来使其工作，如下所示：

var roles = from rl in Old.userrolelinks
            join r in Old.roles on rl.RoleID equals r.RoleID
            where rl.UserID == userId
            select r;
var roles2 = from r in roles.AsEnumerable()
            select new Role { Name = r.RoleName, ID = newRoles[r.RoleName] };
var list = roles2.ToList();

But I was wondering if there was a more elegant and/or more efficient way to solve this problem, ideally without splitting it in two queries.

但我想知道是否有更优雅和/或更有效的方法来解决这个问题，最好不要将其拆分为两个查询。

Anyway, my question is two parts:

无论如何，我的问题是两部分：

First, can I transform this LINQ query into something that Entity Framework will accept, ideally without splitting into two pieces?

首先，我可以将此 LINQ 查询转换为实体框架将接受的内容，最好不要分成两部分吗？

Second, I'd also love to understand a little about EF so I can understand why EF can't layer my custom .NET code on top of the DB access. My DBMS has no idea how to call a method on a Dictionary class, but why can't EF simply make those Dictionary method calls after it's already pulled data from the DB? Sure, if I wanted to compose multiple EF queries together and put custom .NET code in the middle, I'd expect that to fail, but in this case the .NET code is only at the end, so why is this a problem for EF? I assume the answer is something like "that feature didn't make it into EF 1.0" but I am looking for a bit more explanation about why this is hard enough to justify leaving it out of EF 1.0.

其次，我也很想了解一些关于 EF 的信息，这样我就可以理解为什么 EF 不能将我的自定义 .NET 代码放在数据库访问之上。我的 DBMS 不知道如何在 Dictionary 类上调用方法，但是为什么 EF 在已经从 DB 中提取数据后不能简单地调用那些 Dictionary 方法？当然，如果我想将多个 EF 查询组合在一起并将自定义 .NET 代码放在中间，我希望这会失败，但在这种情况下，.NET 代码仅在最后，那么为什么这是一个问题英孚？我认为答案类似于“该功能并未包含在 EF 1.0 中”，但我正在寻找更多关于为什么这足以证明将其排除在 EF 1.0 之外的解释。

回答by Jacob Proffitt

The problem is that in using Linq's delayed execution, you really have to decide where you want the processing and what data you want to traverse the pipe to your client application. In the first instance, Linq resolves the expression and pulls all of the role data as a precursor to

问题在于，在使用 Linq 的延迟执行时，您确实必须决定要处理的位置以及要遍历管道到客户端应用程序的数据。在第一个实例中，Linq 解析表达式并提取所有角色数据作为前驱

New.roles.ToDictionary(row => row.rolename, row => row.roleid);

At that point, the data moves from the DB into the client and is transformed into your dictionary. So far, so good.

此时，数据从数据库移动到客户端并转换为您的字典。到现在为止还挺好。

The problem is that your second Linq expression is asking Linq to do the transform on the second DB usingthe dictionary on the DBto do so. In other words, it is trying to figure out a way to pass the entire dictionary structure to the DB so that it can select the correct ID value as part of the delayed execution of the query. I suspect that it would resolve just fine if you altered the second half to

问题是您的第二个 Linq 表达式要求 Linq使用DB 上的字典对第二个 DB进行转换。换句话说，它试图找出一种将整个字典结构传递给 DB 的方法，以便它可以选择正确的 ID 值作为延迟执行查询的一部分。我怀疑如果您将后半部分更改为

var roles = from rl in Old.userrolelinks
            join r in Old.roles on rl.RoleID equals r.RoleID
            where rl.UserID == userId
            select r.RoleName;
var list = roles.ToDictionary(roleName => roleName, newRoles[roleName]);

That way, it resolves your select on the DB (selecting just the rolename) as a precursor to processing the ToDictionary call (which it should do on the client as you'd expect). This is essentially exactly what you are doing in your second example because AsEnumerable is pulling the data to the client before using it in the ToList call. You could as easily change it to something like

这样，它会将您在 DB 上的选择（仅选择角色名称）解析为处理 ToDictionary 调用的前兆（它应该按照您的预期在客户端上执行）。这基本上正是您在第二个示例中所做的，因为 AsEnumerable 在 ToList 调用中使用数据之前将数据拉到客户端。你可以很容易地把它改成类似的东西

var roles = from rl in Old.userrolelinks
            join r in Old.roles on rl.RoleID equals r.RoleID
            where rl.UserID == userId
            select r;
var list = roles.AsEnumerable().Select(r => new Role { Name = r.RoleName, ID = newRoles[r.RoleName] });

and it'd work out the same. The call to AsEnumerable() resolves the query, pulling the data to the client for use in the Select that follows it.

它的结果是一样的。对 AsEnumerable() 的调用将解析查询，将数据拉到客户端以供其后的 Select 使用。

Note that I haven't tested this, but as far as I understand Entity Framework, that's my best explanation for what's going on under the hood.

请注意，我尚未对此进行测试，但据我了解 Entity Framework，这是我对幕后情况的最佳解释。

回答by Devart

Jacob is totally right. You can not transform the desired query without splitting it in two parts, because Entity Framework is unable to translate the get_Item call into the SQL query.
The only way is to write the LINQ to Entities query and then write a LINQ to Objects query to its result, just as Jacob advised.
The problem is Entity-Framework-specific one, it does not arise from our implementation of the Entity Framework support.

雅各布是完全正确的。如果不将所需的查询分成两部分，就无法转换它，因为实体框架无法将 get_Item 调用转换为 SQL 查询。
唯一的方法是编写 LINQ to Entities 查询，然后将 LINQ to Objects 查询写入其结果，正如 Jacob 建议的那样。
问题是特定于实体框架的问题，它不是由我们对实体框架支持的实现引起的。