title: Optimize LINQ query
body: <p>I have a report that shows orders made to a determined merchant, and it was working fine until I needed to add a filter for payment status.</p>
  
  <p>This is how I build the query, filter by filter:</p>
  
  <pre><code>var queryOrder = context.Orders.Select(m=&gt;m);
  
  if (viewModel.InitialDate.HasValue)
      queryOrder = queryOrder.Where(m =&gt; m.CreatedDate.Date &gt;= viewModel.InitialDate.Value);
  
  (...) /* continues building the query, filter by filter */
  
  
  if (viewModel.SelectedPaymentStatus != null)
      queryOrder = queryOrder.Where(m =&gt; viewModel.SelectedPaymentStatus.Contains(m.Payments.Select(p =&gt; p.PaymentStatusId).Single().ToString()));
  
  queryOrder = queryOrder.Where(m =&gt; m.MerchantId == merchantId);
  </code></pre>
  
  <p>When I run <code>queryOrder</code>, even if it's only a <code>queryOrder.Count()</code>, it takes over 1 minute to execute. Using SQL Server's profiling tool, I extracted the generated query as this:</p>
  
  <pre><code>SELECT [t0].[Id], [t0].[CustomerId], [t0].[MerchantId], [t0].[OrderNumber], [t0].[Amount], [t0].[SoftDescriptor], [t0].[ShippingMethod], [t0].[ShippingPrice], [t0].[IpAddress], [t0].[SellerComment], [t0].[CreatedDate]
  FROM [dbo].[Order] AS [t0]
  WHERE ([t0].[MerchantId] = @p0) 
  AND ((CONVERT(NVarChar,(
         SELECT [t1].[PaymentStatusId]
         FROM [dbo].[Payment] AS [t1]
         WHERE [t1].[OrderId] = [t0].[Id]
      ))) IN (@p1, @p2, @p3, @p4, @p5, @p6, @p7, @p8))
  </code></pre>
  
  <p>the @p0 parameter is a Guid for merchantId, and the @p1 thru @p8 are numeral strings <code>"1"</code> thru <code>"8"</code>, representing the paymentStatusId's.</p>
  
  <p>If I skip the line:</p>
  
  <pre><code>if (viewModel.SelectedPaymentStatus != null)
      queryOrder = queryOrder.Where(m =&gt; viewModel.SelectedPaymentStatus.Contains(m.Payments.Select(p =&gt; p.PaymentStatusId).Single().ToString()));
  </code></pre>
  
  <p>The query runs in under 1 second. But when I use it, the performance hits the floor. Any tips on how to solve this?</p>