title: Long duration of the building list
body: <p>few days ago I start my adventure with ef.</p>
  
  <p>I want to get most used project list from database and build this code:</p>
  
  <pre><code>DateTime dtLastMonth = DateTime.Now.AddMonths(-1);
  
  int[] KontrahentAdres_GIDNumer = new int[]{ 13350, 15494 };
  int[] KontrahentAdres_GIDTyp = new int[]{ 864 };
  
  var query = (from dp in DokumentyPoczties
                               join so in SprawyObiekties on new { dp.ID_DokumentuPoczty, dp.ID_TYP_DokumentuPoczty } equals new { so.ID_DokumentuPoczty, so.ID_TYP_DokumentuPoczty }
                               into soTemp
                               from sot in soTemp.DefaultIfEmpty()
                               join hz in HistoriaZdarzens on new { dp.ID_DokumentuPoczty, dp.ID_TYP_DokumentuPoczty } equals new { ID_DokumentuPoczty = hz.ID_Obiektu, ID_TYP_DokumentuPoczty = hz.ID_TYP_Obiektu }
                               into hzTemp
                               from hzt in hzTemp.DefaultIfEmpty()
                               join wp in WysylkiPoczties on new { dp.ID_DokumentuPoczty, dp.ID_TYP_DokumentuPoczty } equals new { wp.ID_DokumentuPoczty, wp.ID_TYP_DokumentuPoczty }
                               into wpTemp
                               from wpt in wpTemp.DefaultIfEmpty()
                               where dp.PRJ_Kod.Length &gt; 0 &amp;&amp; hzt.TypZdarzenia == 1 &amp;&amp; hzt.DataCzas &gt;= dtLastMonth
                               orderby dp.PRJ_Kod
                               select new { dp.PRJ_Kod, dp.UmW_GIDNumer, dp.ZamNagGIDNumer, wpt.KontrahentAdres_GIDNumer, wpt.KontrahentAdres_GIDTyp, hzt.DataCzas, sot.ID_Sprawy, sot.ID_TYP_Sprawy });
  
  //query = query.Where(x =&gt; KontrahentAdres_GIDNumer.Contains((int)x.KontrahentAdres_GIDNumer) &amp;&amp; KontrahentAdres_GIDTyp.Contains((int)x.KontrahentAdres_GIDTyp));
  var query2 = from qr in query
                           select new { qr.PRJ_Kod };
  
  int count;
  
  var query3 = query2.Where(x =&gt; x.PRJ_Kod != null).GroupBy(x =&gt; x.PRJ_Kod).Select(gr =&gt; new { gr.Key, count = gr.Count() }).OrderByDescending(x =&gt; x.count).Take(20);
  
  Console.Write(query3);
  </code></pre>
  
  <p>Sql query:</p>
  
  <pre><code>-- Region Parameters
  DECLARE @p0 Int = 0
  DECLARE @p1 Int = 1
  DECLARE @p2 DateTime = '2014-02-28 16:49:18.585'
  -- EndRegion
  SELECT TOP (20) [t5].[PRJ_Kod] AS [Key], [t5].[value] AS [count]
  FROM (
      SELECT COUNT(*) AS [value], [t4].[PRJ_Kod]
      FROM (
          SELECT [t0].[PRJ_Kod], [t2].[DataCzas] AS [value], [t2].[TypZdarzenia]
          FROM [DokumentyPoczty] AS [t0]
          LEFT OUTER JOIN [SprawyObiekty] AS [t1] ON ([t0].[ID_DokumentuPoczty] = [t1].[ID_DokumentuPoczty]) AND ([t0].[ID_TYP_DokumentuPoczty] = [t1].[ID_TYP_DokumentuPoczty])
          LEFT OUTER JOIN [HistoriaZdarzen] AS [t2] ON ([t0].[ID_DokumentuPoczty] = [t2].[ID_Obiektu]) AND ([t0].[ID_TYP_DokumentuPoczty] = [t2].[ID_TYP_Obiektu])
          LEFT OUTER JOIN [WysylkiPoczty] AS [t3] ON ([t0].[ID_DokumentuPoczty] = [t3].[ID_DokumentuPoczty]) AND ([t0].[ID_TYP_DokumentuPoczty] = [t3].[ID_TYP_DokumentuPoczty])
          ) AS [t4]
      WHERE ([t4].[PRJ_Kod] IS NOT NULL) AND (LEN([t4].[PRJ_Kod]) &gt; @p0) AND ([t4].[TypZdarzenia] = @p1) AND ([t4].[value] &gt;= @p2)
      GROUP BY [t4].[PRJ_Kod]
      ) AS [t5]
  ORDER BY [t5].[value] DESC
  </code></pre>
  
  <p>run fast 00:00.026 but when I try to get results in my application then i have to wait 8-9 sec.</p>
  
  <p>Can you please help me how to optimize this?</p>