Calling a method inside a Linq query

Question

I want to insert into my table a column named 'S' that will get some string value based on a value it gets from a table column.

For example: for each ID (a.z) I want to gets it's string value stored in another table. The string value is returned from another method that gets it through a Linq query.

• Is it possible to call a method from Linq?
• Should I do everything in the same query?

This is the structure of the information I need to get:

a.z is the ID in the first square in table #1, from this ID I get another id in table #2, and from that I can get my string value that I need to display under column 'S'.

var q = (from a in v.A join b in v.B     on a.i equals b.j     where a.k == "aaa" && a.h == 0     select new {T = a.i, S = someMethod(a.z).ToString()})     return q; 

The line S = someMethod(a.z).ToString() causing the following error:

Unable to cast object of type 'System.Data.Linq.SqlClient.SqlColumn' to type 'System.Data.Linq.SqlClient.SqlMethodCall'.

Answer 1

You have to execute your method call in Linq-to-Objects context, because on the database side that method call will not make sense - you can do this using AsEnumerable() - basically the rest of the query will then be evaluated as an in memory collection using Linq-to-Objects and you can use method calls as expected:

var q = (from a in v.A join b in v.B         on a.i equals b.j         where a.k == "aaa" && a.h == 0         select new {T = a.i, Z = a.z })         .AsEnumerable()         .Select(x => new { T = x.T, S = someMethod(x.Z).ToString() }) 

Answer 2

You'll want to split it up into two statements. Return the results from the query (which is what will hit the database), and then enumerate the results a second time in a separate step to transform the translation into the new object list. This second "query" won't hit the database, so you'll be able to use the someMethod() inside it.

Linq-to-Entities is a bit of a strange thing, because it makes the transition to querying the database from C# extremely seamless: but you always have to remind yourself, "This C# is going to get translated into some SQL." And as a result, you have to ask yourself, "Can all this C# actually get executed as SQL?" If it can't - if you're calling someMethod() inside it - your query is going to have problems. And the usual solution is to split it up.

(The other answer from @BrokenGlass, using .AsEnumerable(), is basically another way to do just that.)