Select clause containing non-EF method calls

Question

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

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

But calling ToList gives me this 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

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.

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:

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

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.

Answer 1

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

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

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

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.

Answer 2

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.