How to move parts of a LINQ query to an reusable L

Question

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.

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

Answer 2

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.