CODEWITHSUNDEEP
c#linqlinq-to-sql
user990635
user990635

Reputation: 4249

Calling a method inside a Linq query

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.

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'.
enter image description here

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'.

Upvotes: 43

Views: 80722

Answers (3)

Giedrius
Giedrius

Reputation: 8540

That is an old question, but I see nobody mention one "hack", that allows to call methods during select without reiterating. Idea is to use constructor and in constructor you can call whatever you wish (at least it works fine in LINQ with NHibernate, not sure about LINQ2SQL or EF, but I guess it should be the same). Below I have source code for benchmark program, it looks like reiterating approach in my case is about twice slower than constructor approach and I guess there's no wonder - my business logic was minimal, so things like iteration and memory allocation matters.

Also I wished there was better way to say, that this or that should not be tried to execute on database,

// Here are the results of selecting sum of 1 million ints on my machine:
// Name    Iterations      Percent    
// reiterate       294     53.3575317604356%      
// constructor     551     100%

public class A
{
    public A()
    {            
    }

    public A(int b, int c)
    {
        Result = Sum(b, c);
    }

    public int Result { get; set; }

    public static int Sum(int source1, int source2)
    {
        return source1 + source2;
    }
}

class Program
{
    static void Main(string[] args)
    {
        var range = Enumerable.Range(1, 1000000).ToList();

        BenchmarkIt.Benchmark.This("reiterate", () =>
            {
                var tst = range
                    .Select(x => new { b = x, c = x })
                    .AsEnumerable()
                    .Select(x => new A
                    {
                        Result = A.Sum(x.b, x.c)
                    })
                    .ToList();
            })
            .Against.This("constructor", () =>
            {
                var tst = range
                    .Select(x => new A(x, x))
                    .ToList();
            })
            .For(60)
            .Seconds()
            .PrintComparison();

        Console.ReadKey();
    }
}

Upvotes: 0

BrokenGlass
BrokenGlass

Reputation: 160852

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

Upvotes: 73

Ken Smith
Ken Smith

Reputation: 20445

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

Upvotes: 13

Related Questions