2

I want to make parts of a LINQ query reusable by using expression trees (i think that's how its called).

Here's a simplified version of my query:

var lQuery = 
  from m in ...
  join a in ... into ta
  from ra in ta.DefaultIfEmpty()
  select 
    new {
      ...
      Status = ra != null ? ... : ..., /* <- i want this to be reusable */
      ...
    };

As you can see, the value of Status is determined via ? : syntax which is translated by LINQ automatically (a expression tree i suppose, the SQL logs show a CASE WHEN query in the end).

How do i move that code to a separate function so that my application does not throw a not supported exception at runtime?

I tried adding a function

public static System.Linq.Expressions.Expression<System.Func<%type of ra%, %status type%>>
  QueryStatusExpression()
{
  return ra => ra != null ? ... : ...;
}

then use it like 

Status = QueryStatusExpression().Invoke(ra)

but it throws the not supported exception.

I'm out of ideas right now. Any help is appreciated.

ViRuSTriNiTy
  • 5,017
  • 2
  • 32
  • 58

2 Answers2

1

The issue can be solved via combined expressions of the LINQKit project.

Here's how to do it:

  1. Add the LinqKit reference to your project (e.g. via NuGet package manager).

  2. Add the following line on top of your .cs file to make the extension methods of LINQKit available

    using LinqKit; // for System.Linq.Expressions.Expression<>.Invoke()

  3. Define the expression as a static field

    public static System.Linq.Expressions.Expression<
  System.Func<%type of ra% ra, %status type%>>
    GetStatusExpression = ra != null ? ... : ...;

  4. Invoke the expression in the query (be sure to add .AsExpandable() to the first table as described in the LINQKit docs)

    var lQuery = 
  from m in %first table%.AsExpandable() ...
  join a in ... into ta
  from ra in ta.DefaultIfEmpty()
  select 
    new {
      ...
      Status = GetStatusExpression.Invoke(ra),
      ...
    };

If you want to use the expression in "normal" code then you need to compile it first like

 public static System.Func<%type of ra% ra, %status type%>
    GetStatusExpressionCompiled = GetStatusExpression.Compile();

 ...

 if (GetStatusExpressionCompiled(ra) == ...)
 {
   ...

Big thanks goes to svick for pointing me in the right direction with it's comment.

ViRuSTriNiTy
  • 5,017
  • 2
  • 32
  • 58
  • This works if we pre-define the entire right side of the assignment statement, but as soon as I try something like `Status = lStatusExpression.Invoke(ra) + "some words"` (assuming Status is a string) I get errors related to translating the rest of the line. Thoughts? – DCShannon May 01 '15 at 02:37
  • @DCShannon it's most likely because Invoke() compiles the expression hence you cannot extend the expression with + afterwards. Try add the + expression to lStatusExpression. – ViRuSTriNiTy May 02 '15 at 13:20
  • I edited my answer as using a method inside an expression does not work. – ViRuSTriNiTy May 05 '15 at 12:24
0

Editing my answer since I didn't catch the EF translation issue.

Using vanilla code, when you try to abstract this, you're going to always have a function delegate to deal with (due to the fact your expression will become an Expression<Func<T>>) which means the query provider is going to choke when it finds itself unable to interpret the function delegate. In order to work around this, you're going to have to rely on a library like LINQKit to allow you to merge expressions together, or basically write the same yourself. (See this discussion).

It appears that the motive for this code is to essentially perform a left join and substitute a default value instead of null for the rows that don't have a match. I think in that case it's necessary to understand what you're trying to accomplish. There's a good chance you would be better off moving the logic either up or down a layer. You could move the default value logic into a view or stored procedure in the database to consolidate it. You could also use the approach you are going for by pulling your result set first and then doing a query on the objects in memory, so then you don't have to worry about making it translatable to an SQL query.

Community
  • 1
  • 1
moarboilerplate
  • 1,633
  • 9
  • 23
  • 1
    The query provider won't be able to translate this at all, just as the OP states in his question, since you're creating a delegate, rather than an `Expression`. – Servy Mar 30 '15 at 16:31
  • @Servy Ah yes. Always the EF constraint. – moarboilerplate Mar 30 '15 at 16:44
  • @moarboilerplate Yep Servy is right, this won't work as it will throw a `not supported` exception. – ViRuSTriNiTy Mar 31 '15 at 06:58
  • @moarboilerplate thx for your update. The motive isn't a substitution as you are mentioned. `Status` is a complicated calculation based on multiple variables, not just `ra`. I striped down the example a bit for easier understanding. If i follow your link to the other discussion i see a relevant post by @fero but this seems really really too complicated. If i want to go that route i have to rework my whole query which is quite big already. I'm surprised that there's no way to write something that is compiled to an appropriate expression tree... – ViRuSTriNiTy Apr 01 '15 at 10:07
  • 1
    Yeah...sometimes it's better just to get the data first and then do the calculation in memory, or to offload the calculation to the DB in a stored procedure, view, function, etc... – moarboilerplate Apr 02 '15 at 16:21