foreach in linq result not working

Question

Don't know what's wrong here, when I run the application it says "Specified method is not supported" pointing at "var result in query" in foreach loop. Please help...

var query = from c in entities.Customer
            select c.CustomerName;

List<string> customerNames = new List<string>();

foreach (var result in query)
{
    customerNames.Add(result.ToString());  
}

EDIT: using ToList() also gives the same error.

Answer 1

Can you try using just the ToList() method instead of the foreach?

List<string> customerNames = query.ToList();

Answer 2

Try removing .ToString() and see if this will work:

foreach (var result in query)
{
    customerNames.Add(result);  
}

Seems like that the root of the problem lies deep inside LINQ-to-SQL query translation mechanism. I suppose the translation engine tries to translate .ToString() into SQL and fails there.

Answer 3

try this

var query = from c in entities.Customer
        select c.CustomerName;

List<string> customerNames = new List<string>();

query.ToList().ForEach(r=>customerNames.Add(r));

Answer 4

If the problem is not ToString() as Gart mentioned my second suspicious falls in c.CustomerName. Is this a custom property in your partial class?

Also, the stacktrace of the exception must surly show what is the unsupported method.

Answer 5

The reason for your error is scope, which is what the "method not supported" error is telling you.

This usually happens when using a Linq to [fill in the blank] ORM. So, I'm guessing your entities must be from an ORM tool, something like Entity Framework, and you are using something like Linq to Entities.

When using linq your query is not enumerated out until you access it, which for an ORM means hitting the database or other data repository. This delayed action can cause some strange behavior if you do not know it is there, such as this error.

But, you have local (non-linq) code and your query intertwined, so the linq to [] compiler does not know how to handle your local code when compiling the linq code. Thus the "method not supported" error - it is basically the same as referencing a private method from outside of the class, the method you called is unknown in the current scope.

In other words the compiler is trying to compile your query and hit the database when you do the result.ToString(), but does not know anything about the private variable of CustomerNames or the foreach method. The database logic and the local object logic have to be kept separate - completely resolve the database query results before using locally.

You should be able to write it like this:

var customerNames  = entities.Customer.Select(c => c.CustomerName).ToList();

If you have to keep the foreach (for more complicated logic, not for this simple of an example) you still need to resolve the Linq to [] portion (by forcing it to enumerate the query results) prior to involving any non-linq code:

var query = from c in entities.Customer
            select c.CustomerName;

var qryList = query.ToList();

List<string> customerNames = new List<string>();

foreach (var result in qryList)
{
    customerNames.Add(result.ToString());  
}