包含非EF方法调用SELECT子句 [英] Select clause containing non-EF method calls

问题描述

我无法建立一个实体框架LINQ查询的SELECT子句中包含的方法调用非EF对象。

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

下面的代码是使用的应用程序的一部分转变从一个DBMS数据转换成另一个DBMS不同的模式。在下面的代码中，角色是我的自定义类无关的DBMS，而其他类都是由实体框架从我的DB模式生成的：

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:

// 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();

但调用了ToList使我这个NotSupportedException异常：

But calling ToList gives me this NotSupportedException:

LINQ实体无法识别
中的方法的Int32
get_Item（System.String）'方法，和
。这种方法不能被翻译成$ b $巴店表达

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到实体的barfing在我的电话拉数值超出给定的名字作为字典关键。诚然，我不明白有足够的了解EF知道为什么这是个问题。

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.

我使用devart的 dotConnect PostgreSQL的实体框架供应商，但我认为在这一点上，这不是一个具体的DBMS的问题。

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

我知道我可以把它通过拆分了我的查询工作成两个查询，像这样：

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:

首先，我可以改变这个LINQ查询到的东西，实体框架将接受，最理想的情况分裂成两部分？

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

二，我也很想了解一点关于EF所以我能理解为什么EF无法层我对DB访问顶级定制.NET代码。我DBMS不知道如何调用一个字典类中的方法，但为什么不能简单地EF使这些字典方法调用它已经被拉从DB数据后？当然，如果我想组合成多种EF查询在一起，并把自定义的.NET代码在中间，我想到的是失败，但在这种情况下，.NET代码只在年底，那么，为什么是这是一个问题EF？我认为答案是什么这个功能并没有使它成为EF 1.0，但我正在寻找关于为什么这是够硬的理由离开它EF 1.0进一步的解释。

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.

推荐答案

的问题是，在使用LINQ的延迟执行，你必须要决定你想要的处理，你想管传送到你的客户什么样的数据应用。在一审中，LINQ的解析表达式，并拉所有角色数据为先导，以

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

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

在该点，从DB数据移动到在客户端和被变换成的词典。到目前为止，一切都很好。

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

的问题是，你的第二个LINQ表达式是要求LINQ到做在第二DB 是词典变换的DB 这样做。换句话说，它试图找出一个方法来对整个词典结构传递到数据库，以便它可以选择正确的ID值作为查询的延迟执行的一部分。我怀疑，如果从RL在Old.userrolelinks $ B改变下半年

The problem is that your second Linq expression is asking Linq to do the transform on the second DB using the dictionary on the DB to 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

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

这种方式，它可以解决您选择的DB（只选择角色名）为先导，以加工在ToDictionary调用（它应该在客户端上做如你所期望）。这基本上是你正在做在第二个例子正是因为AsEnumerable在了ToList呼叫使用之前拉动数据到客户端。你可以很容易地为它VAR角色=更改为类似

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

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

和它会制定出相同。到AsEnumerable（）的调用解析查询，数据拉到客户端在它后面的选择使用。

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.

请注意，我没有测试过这一点，但据我了解实体框架，那是什么的引擎盖下将我最好的解释。

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.

这篇关于包含非EF方法调用SELECT子句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！