Non-EF method calls in the select clause

c# entity-framework linq linq-to-entities postgresql

Question

The select clause of an Entity Framework LINQ query that involves method calls to non-EF objects is giving me difficulties.

A program that converts data from one DBMS into a different schema on another DBMS is included in the code below. The classes in the code below were all created by Entity Framework using my database schema, with the exception of Role, which is a custom class that has no connection to the DBMS:

// 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 when I use ToList, I get the following 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

It seems that my request to get the value from the dictionary using the name as a key is being rejected by LINQ-to-Entities. To be honest, I don't know enough about EF to comprehend why this is an issue.

Although I'm using Devart's dcPostgres for PostgreSQL entity framework provider, I believe that this isn't a DBMS-specific problem at this moment.

I'm certain that by dividing my question into two queries, like in the following:

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

However, I was wondering whether there was a more beautiful and/or effective approach to address this issue, preferably without requiring the use of two queries.

Anyway, I have two questions:

Can I first convert this LINQ query—ideally without dividing into two parts—into something that Entity Framework can accept?

In order to understand why EF can't overlay my customized.NET code on top of the DB access, I'd also want to learn a little bit more about EF. Why can't EF just run those Dictionary method calls after it has already retrieved data from the DB, even if my DBMS has no concept how to do so? In this situation, the.NET code is just at the end, so why is this an issue for EF? Sure, I'd expect that to fail if I tried to combine numerous EF queries and add custom.NET code in the middle. I'm assuming the response is something along the lines of "that functionality didn't make it into EF 1.0," but I'm hoping for a little more justification as to why this is challenging enough to warrant keeping it out of EF 1.0.

1
5
6/19/2013 7:31:03 PM

Accepted Answer

The issue with delayed execution for Linq is that you have to carefully consider where you want the processing to take place and what data you want to provide over the pipe to your client application. First, Linq resolves the expression and fetches all of the role information before

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

At that moment, the data is translated into your dictionary and sent from the DB into the client. So far, so good.

Your second Linq expression's request for Linq to do the transform on the second DB and the dictionary is problematic. In order for the DB to choose the appropriate ID value as part of the delayed execution of the query, it must be possible to send the whole dictionary structure to it. If you changed the second half to, I think it would resolve perfectly fine.

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

In this manner, it resolves your DB select (selecting just the rolename) before performing the ToDictionary function, which it should accomplish on the client as you would anticipate. Since AsEnumerable pulls the data to the client before utilizing it in the ToList method, this is effectively what you are doing in your second example. You might just as easily alter it to say

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 the result would be the same. The query is answered by the AsEnumerable() method, which also returns the data to the client for use in the Select that comes next.

Although I haven't tried it, this is my best interpretation for what's happening on the inside based on my understanding of Entity Framework.

10
11/17/2009 9:10:12 AM

Popular Answer

Jacob is entirely correct. Because Entity Framework is unable to convert the get Item call into a SQL query, you cannot modify the appropriate query without separating it into two pieces.
The only option is to create the LINQ to Entities query, and then, as Jacob suggested, create a LINQ to Objects query to access the query's output.
The issue is unique to Entity Framework; it is not a result of how we implemented Entity Framework support.



Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow