title: Using ternary conditional operator or expression in Entity Framework
body: <p>I have an entity framework query that I inherited which includes several Sums, cutdown example:-</p>
  
  <pre><code>from c in db.Clients
  where {where clauses}
  select new
  {
  ClientID = c.ClientID,
  ClientName = c.ClientName,
  CurrentBalance = c.ClientTransactions.Select(ct =&gt; ct.Amount)
                  .DefaultIfEmpty(0m).Sum(),
  }).ToList();
  </code></pre>
  
  <p>As the number of clients and the number of transactions has grown, this query has obviously become slower and slower.</p>
  
  <p>Ideally I'd want to store balances rather than calculate them every time, but currently the system doesn't do that, and it would be a very large change to implement, so for now I'm just attempting a band-aid fix.</p>
  
  <p>The fix I'm attempting to implement is to simply not do the Sum calculations (there are several, example above just has one) for people that aren't interested in them.</p>
  
  <p>My first attempt was simply to use ternary conditional operators to determine whether or not to do the calculation:-</p>
  
  <pre><code>from c in db.Clients
  where {where clauses}
  select new
  {
  ClientID = c.ClientID,
  ClientName = c.ClientName,
  CurrentBalance = ClientSearchExcludeCurrentBalance ? 0m : 
                   c.ClientTransactions.Select(ct =&gt; ct.fAmount).DefaultIfEmpty(0m).Sum(),
  }).ToList();
  </code></pre>
  
  <p>The problem with this, it turns out, is that regardless of the value of the condition (ClientSearchExcludeCurrentBalance) both sides are still calculated, and then the ternary decides which one to use. So even setting the condition to false, the Sum still gets processed and the query takes too long.</p>
  
  <p>Commenting out the sum, as below...</p>
  
  <pre><code>from c in db.Clients
  where {where clauses}
  select new
  {
  ClientID = c.ClientID,
  ClientName = c.ClientName,
  CurrentBalance = ClientSearchExcludeCurrentBalance ? 0m : 0m,
                   //c.ClientTransactions.Select(ct =&gt; ct.fAmount).DefaultIfEmpty(0m).Sum(),
  }).ToList();
  </code></pre>
  
  <p>... is now nice and fast, so the ternary is definitely running it even when it's not used.</p>
  
  <p>So, with that idea out the window, I tried using an expression instead:-</p>
  
  <pre><code>Expression&lt;Func&lt;Client, Decimal&gt;&gt; currentBalance = c =&gt; 0m;
  if (!ClientSearchExcludeCurrentBalance)
  {
      currentBalance = c =&gt; c.ClientTransactions
                            .Select(ct =&gt; ct.Amount).DefaultIfEmpty(0m).Sum();
  }
  
  from c in db.Clients
  where {where clauses}
  select new
  {
  ClientID = c.ClientID,
  ClientName = c.ClientName,
  CurrentBalance = currentBalance.Invoke(c),
  }).ToList();
  </code></pre>
  
  <p>This fell over with an unknown expression error:-</p>
  
  <pre><code>LINQ to Entities does not recognize the method 'System.Decimal Invoke[Client,Decimal](System.Linq.Expressions.Expression`1[System.Func`2[RPM.DAO.UI.Client,System.Decimal]], RPM.DAO.UI.Client)' method, and this method cannot be translated into a store expression
  </code></pre>
  
  <p>I also tried using Expand()</p>
  
  <pre><code>CurrentBalance = currentBalance.Expand().Invoke(c)
  </code></pre>
  
  <p>but still got the unknown expression error.</p>
  
  <p>Just to see, I tried it with defaulting the Sum values to 0m, and then in the loop that assigns the results to the DTO Collection doing the sum there if needed</p>
  
  <pre><code>foreach (var client in Clients) 
  {
      if (!ClientSearchExcludeCurrentBalance) {
          var c = db.Clients.FirstOrDefault(cl =&gt; cl.ClientID == client.ClientID);
          client.CurrentBalance = c.ClientTransactions.Select(ct =&gt; ct.fAmount)
                                  .DefaultIfEmpty(0m).Sum();
      }
  }
  </code></pre>
  
  <p>This works, in that it only does the sum if told to, but doing it outside the main select means the entire query now takes twice as long as it used to, so is clearly not viable.</p>
  
  <p>So, my questions are:-</p>
  
  <p>Does anyone know if it's possible to make Entity Framework only run the parts of a ternary conditional operator that will be used?</p>
  
  <p>Does anyone know if it's possible to use an Expression to return a value in Entity Framework?</p>
  
  <p>Or, alternatively, how to add an IF statement into an Entity Framework query?</p>
  
  <p>For (non-working) example:-</p>
  
  <pre><code>from c in db.Clients
  where {where clauses}
  select new
  {
  ClientID = c.ClientID,
  ClientName = c.ClientName,
  CurrentBalance = if (ClientSearchExcludeCurrentBalance)
                       return 0m;
                   else 
                       return c.ClientTransactions.Select(tf =&gt; tf.fAmount)
                             .DefaultIfEmpty(0m).Sum(),
  }).ToList();
  </code></pre>
  
  <p>Thanks!</p>
  
  <p>Edit:</p>
  
  <p>I tried Barr J's solution:-</p>
  
  <pre><code>from c in db.Clients
  let currentBalance = ClientSearchExcludeCurrentBalance ? 0m : 
                       c.ClientTransactions.Select(ct =&gt; ct.Amount).DefaultIfEmpty(0m).Sum()
  where {where clauses}
  select new
  {
  ClientID = c.ClientID,
  ClientName = c.ClientName,
  CurrentBalance = currentBalance
  }).ToList();
  </code></pre>
  
  <p>I get a null reference exception:</p>
  
  <pre><code>System.NullReferenceException: 'Object reference not set to an instance of an object.'
  </code></pre>
  
  <p>Edit #2: The cut-down version above doesn't give the null exception error, but the full version (with identical code) does... weird!</p>
  
  <p>Anyway, with the working cut-down version above I tried it with the setting set to true and fall, and both took the same time, so it still does the Sum evaluation either way</p>