title: How to write fast subquery in where condition in linq
body: <p>I  try to write subquery in linq where condition. But when I look my sql syntax at sql server profiler my query looks complicated and long and it takes a timeout error in server.</p>
  
  <p>I want to write an query like below.</p>
  
  <pre><code>SELECT 
  T1.Id,
  T2.Id,
  DATEADD(HOUR, 
      ISNULL(
      (
          SELECT 
              MAX(T3.MaxHour) AS [MaxHour]
          FROM TABLE3 T3 WITH(NOLOCK)
          WHERE 
              T1.CategoryId=T3.CategoryId
      ),
      0),T1.EndDate
      ),
  T1.StartDate 
  FROM   
      TABLE1 T1 WITH(NOLOCK)    
  LEFT OUTER JOIN TABLE2 T2 WITH(NOLOCK)  ON T1.Id=T2.Id  
  WHERE
      DATEADD(HOUR, 
      ISNULL(
      (
          SELECT 
              MAX(T3.MaxHour) AS [MaxHour]
          FROM TABLE3 T3 WITH(NOLOCK)
          WHERE 
              T1.CategoryId=T3.CategoryId
      ),
      0),T1.EndDate
      ) 
      &gt; '2014-11-05 00:00:00'
  AND
      T1.StartDate &lt; '2014-11-05 06:00:00'
  </code></pre>
  
  <p>ı wrote my linq expression like this. </p>
  
  <pre><code>        var actualData = from T1 in _context.TABLE1
                           join T2 in _context.TABLE2 on T1.Id equals T2.Id into data
                           from x in data.DefaultIfEmpty()
                           where
                               EntityFunctions.AddHours(T1.EndDate,
                               (
                                  (int?)((from T3 in _context.TABLE3
                                          where
                                               T3.CategoryId == T1.CategoryId
                                          select
                                              T3).Max(row =&gt; row.MaxHour))
                               ) ?? 0
                               ) &gt;= '2014-11-05 00:00:00'
                               &amp;&amp; T1.StartDate &lt; '2014-11-05 06:00:00'
  
                           select
                               new 
                               {
                                   Id1 = T1.Id,
                                   Id2 = T2.Id,
                                   StartDate = T1.StartDate,
                                   EndDate =  EntityFunctions.AddHours(T1.EndDate,
                                      (
                                          (int?)((from T3 in _context.TABLE3
                                          where
                                               T3.CategoryId == T1.CategoryId
                                          select
                                              T3).Max(row =&gt; row.MaxHour))
                                      ) ?? 0
                                  )
  
                               };
  </code></pre>
  
  <p>But it don't seems in sql that i want to see. </p>
  
  <p>How can ı write in linq?</p>
  
  <p>Thanks.</p>