title: Linq Query taking so long to execute
body: <p>This is my linq query and this is taking so long to execute. can any one suggest me how to make it faster . i am using some function in this query for calculating the data on the fly . when i removed those function this query run fast but i have to use those function for getting data in that right way . please some one suggest me how  i can improve this query </p>
  
  <pre><code>  var l = from a in GetItemList(con)
                          join product in GetProductStock(con) on
                              a.Id
                              equals product.Id
                              into prodList
                          from prod in prodList.DefaultIfEmpty()
                          join brand in GetBrandStock(con) on
                              a.PbPairId equals brand.Id
                              into brandList
                          from bran in brandList.DefaultIfEmpty()
                          join pack in GetPackStock(con) on
                              a.PbpPairId equals pack.Id
                              into packList
                          from pac in packList.DefaultIfEmpty()
                          join np in RtbVoucherVoPurSalItem.GetTotalBuySellByItemForNormal(con) on a.Id equals np.ItemId
                              into npl
                          from npv in npl.DefaultIfEmpty()
                          join pp in RtbVoucherVoPurSalItem.GetTotalBuySellByItemForNormal(con) on a.Id equals pp.ItemId
                              into ppl
                          from ppv in ppl.DefaultIfEmpty()
                          select
                              new RsXtraProductListWithClosing
                                  {
                                      Id = a.Id,
                                      PbPairId = a.PbPairId,
                                      PbpPairId = a.PbpPairId,
                                      ProductId = a.ProductId,
                                      BrandId = a.BrandId,
                                      PackId = a.PackId,
                                      DesignNoId = a.DesignNoId,
                                      Product = a.Product.Trim(),
                                      Brand = a.Brand.Trim(),
                                      Pack = a.Pack.Trim(),
                                      DesignNo = a.DesignNo.Trim(),
                                      QuantityUnitId = a.QuantityUnitId,
                                      RateUnitId = a.RateUnitId,
                                      AskDesignNo = a.AskDesignNo,
                                      AskCustomSize = a.AskCustomSize,
                                      AskDesignNoRate = a.AskDesignNoRate,
                                      UseBachNumber = a.UseBachNumber,
                                      UseExpDate = a.UseExpDate,
                                      UseManfacDate = a.UseManfacDate,
                                      AskD = a.AskD,
                                      AskH = a.AskH,
                                      AskW = a.AskW,
                                      SaD = a.SaD,
                                      SaH = a.SaH,
                                      SaW = a.SaW,
                                      SaleByPump = a.SaleByPump,
                                      ScalGroup = a.ScalGroup,
                                      CalculationUnitId = a.CalculationUnitId,
                                      UnitSize = a.UnitSize,
                                      LowStockQuantity = a.LowStockQuantity,
                                      OverStockQuantity = a.OverStockQuantity,
                                      SizeAs = a.SizeAs,
                                      UseDirectDiscount = a.UseDirectDiscount,
                                      Discount = a.Discount,
                                      PurchaseRate1 = a.PurchaseRate1,
                                      PurchaseRate2 = a.PurchaseRate2,
                                      PurchaseRate3 = a.PurchaseRate3,
                                      PurchaseOtherRate = a.PurchaseOtherRate,
                                      SaleRate1 = a.SaleRate1,
                                      SaleRate2 = a.SaleRate2,
                                      SaleRate3 = a.SaleRate3,
                                      SaleOtherRate = a.SaleOtherRate,
                                      LockOtherPurchaseRate = a.LockOtherPurchaseRate,
                                      LockOtherSaleRate = a.LockOtherSaleRate,
                                      LockSaleRate = a.LockSaleRate,
                                      LockPurchaseRate = a.LockPurchaseRate,
                                      ProductClosingStock = ((a.Pobu ?? prod.OpeningStock ?? 0) + (npv.Quantaty ?? 0)),
                                      ProductCClosingStock = ((a.CPobu ?? prod.COpeningStock ?? 0) + (ppv.Quantaty ?? 0)),
                                      BrandClosingStock = ((a.Bobu ?? bran.OpeningStock ?? 0) + (npv.Quantaty ?? 0)),
                                      BrandCClosingStock = ((a.CBobu ?? bran.COpeningStock ?? 0) + (ppv.Quantaty ?? 0)),
                                      PackClosingStock = ((a.Paobu ?? pac.OpeningStock ?? 0) + (npv.Quantaty ?? 0)),
                                      PackCClosingStock = ((a.CPaobu ?? pac.COpeningStock ?? 0) + (ppv.Quantaty ?? 0)),
                                      DesignNoClosingStock = ((a.Dobu ?? 0) + (npv.Quantaty ?? 0)),
                                      DesignNoCClosingStock = ((a.CDobu ?? 0) + (ppv.Quantaty ?? 0))
                                  };
                  return l;
  
  
  and this are the other functions helping this query 
  
    public static IQueryable&lt;RsXtraOpeningStock&gt; GetPackStock(RdbCompany con)
          {
              return con.RtbInventoryProductBrandPackDesignNoPairs
                  .GroupBy(p =&gt; p.BrandPackPairId).Select(
                  g =&gt; new RsXtraOpeningStock
                           {
                               Id = g.Key,
                               OpeningStock = (g.Sum(m =&gt; m.OpeningStock) ?? 0),
                               COpeningStock = (g.Sum(m =&gt; m.COpenigStock) ?? 0),
                           });
          }
  
  
          public static IQueryable&lt;RsXtraOpeningStock&gt; GetBrandStock(RdbCompany con)
          {
              return from d in con.RtbInventoryProductBrandPackPairs
                     join pk in GetPackStock(con) on d.Id equals pk.Id
                         into pkl
                     from pkd in pkl.DefaultIfEmpty()
                     group new{d,pkd} by d.BrandPairId
                         into g
                         select new RsXtraOpeningStock
                         {
                             Id = g.Key,
                             OpeningStock = ((g.Sum(a =&gt; (decimal?)(a.d.OpeningStock ?? a.pkd.OpeningStock))??0)),
                             COpeningStock = ((g.Sum(a =&gt; (decimal?)(a.d.COpenigStock ?? a.pkd.COpeningStock))??0))
                         };
          }
  
  
          public static IQueryable&lt;RsXtraOpeningStock&gt; GetProductStock(RdbCompany con)
          {
              return from d in con.RtbInventoryProductBrandPairs
                     join br in GetBrandStock(con) on d.Id equals br.Id
                         into brl
                     from brd in brl.DefaultIfEmpty()
                     group new { d, brd } by d.ProductId
                         into g
                         select new RsXtraOpeningStock
                         {
                             Id = g.Key,
                             OpeningStock = ((g.Sum(a =&gt; (decimal?)(a.d.OpeningStock ?? a.brd.OpeningStock)) ?? 0)),
                             COpeningStock = ((g.Sum(a =&gt; (decimal?)(a.d.COpenigStock ?? a.brd.COpeningStock)) ?? 0))
                         };
  
          }
  
  
  
  
  
  
   public static IQueryable&lt;RsXtraItemFinalBuySellByMode&gt; GetTotalBuySellByItemForPure(RdbCompany con)
          {
              var stockActiveArray = RsXtraVoucherHelperArray.GetActiveStockVoucherType();
              return GetList(con).Where(
                  a =&gt; stockActiveArray.Contains(a.FkVoucherVoMain.FkVoucher.FkVoucherGroup.VoucherType)
                       &amp;&amp; a.FkVoucherVoMain.CompanyId == RsXtraMainInnerObj.CurrentCompany.Id
                       &amp;&amp; a.FkVoucherVoMain.BranchId == RsXtraMainInnerObj.CurrentHierarchy.Id
                       &amp;&amp; a.FkVoucherVoMain.Mode == RenumMode.P
                  )
                  .GroupBy(a =&gt; new {a.ItemId})
                  .Select(a =&gt; new RsXtraItemFinalBuySellByMode
                                   {
                                       ItemId = a.Key.ItemId,
                                       Quantaty = a.Sum(c =&gt; (decimal?)c.Quantaty)
                                   });
          }
  
  
           public static IQueryable&lt;RsXtraItemFinalBuySellByMode&gt; GetTotalBuySellByItemForNormal(RdbCompany con)
          {
              var stockActiveArray = RsXtraVoucherHelperArray.GetActiveStockVoucherType();
              return GetList(con).Where(
                  a =&gt; stockActiveArray.Contains(a.FkVoucherVoMain.FkVoucher.FkVoucherGroup.VoucherType)
                       &amp;&amp; a.FkVoucherVoMain.CompanyId == RsXtraMainInnerObj.CurrentCompany.Id
                       &amp;&amp; a.FkVoucherVoMain.BranchId == RsXtraMainInnerObj.CurrentHierarchy.Id
                       &amp;&amp; a.FkVoucherVoMain.Mode != RenumMode.P
                  )
                  .GroupBy(a =&gt; new { a.ItemId })
                  .Select(a =&gt; new RsXtraItemFinalBuySellByMode
                  {
                      ItemId = a.Key.ItemId,
                      Quantaty = a.Sum(c =&gt; (decimal?)c.Quantaty)
                  });
          }
  </code></pre>