including "offline" code in compiled querys

Question

What happens behind the curtains when I include a function into my compiled query, like I do with DataConvert.ToThema() here to convert a table object into my custom business object:

public static class Queries
{
    public static Func<MyDataContext, string, Thema> GetThemaByTitle
    {
        get
        {
            var func = CompiledQuery.Compile(
                (MyDataContext db, string title) =>
                    (from th in elan.tbl_Thema
                     where th.Titel == title
                     select DataConvert.ToThema(th)).Single()
                     );
            return func;
        }
    }
}

public static class DataConvert
{
    public static Thema ToThema(tbl_Thema tblThema)
    {
        Thema thema = new Thema();

        thema.ID = tblThema.ThemaID;
        thema.Titel = tblThema.Titel;
        // and some other stuff

        return thema;
    }
}

and call it like this

Thema th = Queries.GetThemaByTitle.Invoke(db, "someTitle");

Apparently the function is not translated in to SQL or something (how could it), but it also does not hold when I set a breakpoint there in VS2010.

It works without problems, but I don't understand how or why. What exactly happens there?

score 2 · Accepted Answer · answered Apr 10 '11 at 14:39

Your DataConvert.ToThema() static method is simply creating an instance of a type which has a default constructor, and setting various properties, is that correct? If so, it's not terribly different from:

(from th in elan.tbl_Thema
where th.Titel == title
select new Thema{ID=th.ThemaID, Titel=th.Titel, etc...}
).Single());

When you call Queries.GetThemaByTitle, a query is being compiled. (The way you are calling this, by the way, may or may not actually be giving you any benefits from pre-compiling). That 'Query' is actually a code expression tree, only part of which is intended to generate SQL code that is sent to the database.

Other parts of it will generate IL code which is grabbing what is returned from the database and putting it into some form for your consumption. LINQ (EF or L2S) is smart enough to be able to take your static method call and generate the IL from it to do what you want - and maybe it's doing so with an internal delegate or some such. But ultimately, it doesn't need to be (much) different from what would be generated from I substituted above.

But note that this happens regardless what the type is that you get back; somewhere, IL code is being generated that puts DB values into a CLR object. That is the other part of those expression trees.

If you want a more detailed look at those expression trees and what they involved, I'd have to dig for ya, but I'm not sure from your question if that's what you are looking for.

score 1 · Answer 2 · answered Apr 15 '11 at 13:38

Let me start by pointing out, that whether you compile your query or not does not matter. You would observe the very same results even if you did not pre-compile.

Technically, as Andrew has pointed out, making this work is not that complicated. When your LINQ expression is evaluated an expression tree is constructed internally. Your function appears as a node in this expression tree. No magic here. You'll be able to write this expression both in L2S and L2E and it will compile and run fine. That is until you try to actually execute the actual SQL query against the database. This is where difference begins. L2S seems to happily execute this task, whereas L2E fails with NotSupportedException, and reporting that it does not know how to convert ToThema into store query.

So what's happening inside? In L2S, as Andrew has explained, the query compiler understands that your function can be run separately from the store query has been executed. So it emits calls to your function into the object reading pipeline (where data read from SQL is transformed to the objects that are returned as the result of your call).

Once thing Andrew was not quite right, is that it matters what's inside your static method. I don't think it does.

If you put a break point in the debugger to your function, you will see that it's called once per returned row. In the stack trace you will see "Lightweight Function", which, in reality, means that the method was emitted at run time. So this is how it works for Linq to Sql.

Linq to Entity team seemed to go different route. I do not know, what was the reasoning, why they decided to ban all InvocationExpressions from L2E queries. Perhaps these were performance reason, or may be the fact that they need to support all kind of providers, not SQL Server only, so that data readers might behave differently. Or they simply thought that most people wouldn't realize that some of those are executed per returned row and preferred to keep this option closed.

Just my thoughts. If anyone has any more insight, please chime in!

including "offline" code in compiled querys

2 Answers2