Linq编译查询和性能问题

Question

我遇到 linq 编译查询的一些性能问题。

using (var txn = new TransactionScope(TransactionScopeOption.Required, new TransactionOptions { IsolationLevel = System.Transactions.IsolationLevel.ReadUncommitted }))
        {
            DateTime dealcheck = new DateTime(1753, 2, 2);

            Func<DealDataClassesDataContext, string, IQueryable<DealsDetails>> DD =
            CompiledQuery.Compile<DealDataClassesDataContext, string, IQueryable<DealsDetails>>

    ((DealDataClassesDataContext nw, string sCity) =>

        from D in nw.Deals

        where D.Address == City && (D.DealTime >= DateTime.Now || D.DealTime == dealcheck) && PriceMax >= D.DealPrice && D.DealPrice >= PriceMin && DisCountMax >= D.SavingsRate && D.SavingsRate >= DiscountMin && (D.DealTime >= DateTime.Now.AddDays(TimeMin) && D.DealTime <= DateTime.Now.AddDays(TimeMax) || D.DealTime == dealcheck)

        select new DealsDetails(
                          lst,
                          D.DealId,
                          D.DealHeadline,
                          D.DealCategory,
                          D.BuyPrice,
                          D.DealPrice,
                          D.SavingsRate,
                          D.SavingAmount,
                          D.RelatedWebsite,
                          D.Address,
                          string.Empty,
                          D.DealImage,
                          string.Empty,
                          string.Empty,
                          D.Time, D.CurrentTime, D.DealTime,
                         D.Location, string.Empty, string.Empty, D.Latitude, D.Longitude, D.Islocal, D.VendorMail, D.MerchantInfo, D.Review, D.HowItWork, D.DealUrl
                          ));

            string jString = "";

            //int a = q(DealDbContext1, "London").Count();

            using (DealDataClassesDataContext db = new DealDataClassesDataContext())
            {
                IQueryable<DealsDetails> DDD = DD.Invoke(DealDbContext, "London");

                if (lstSite.Count > 0 && lstSite[0] != "AllDeals")
                {
                    DDD = DDD.Where(D => D.RelatedWebsite.Split(',').Where(x => lstSite.Contains(x)).Any()); //.Where(row => row.Category.ToList().Where(x => lst.Contains(x)).Any()).ToList();
                }
                if (lst.Count > 0)
                {
                    DDD = DDD.Where(D => D.Categories.Split(',').Where(x => lst.Contains(x)).Any()); //.Where(row => row.Category.ToList().Where(x => lst.Contains(x)).Any()).ToList();
                }
                if (sortby == "Time" && orderby == "Asc")
                {
                    DDD = (from d in DDD orderby d.Time ascending select d).Skip((Page - 1) * PageSize).Take(PageSize);
                }
                else if (sortby == "Time" && orderby == "Desc")
                {
                    DDD = (from d in DDD orderby d.Time descending select d).Skip((Page - 1) * PageSize).Take(PageSize);
                }
                else if (sortby == "Price" && orderby == "Asc")
                {
                    DDD = (from d in DDD orderby d.DealPrice ascending select d).Skip((Page - 1) * PageSize).Take(PageSize);
                }
                else if (sortby == "Price" && orderby == "Desc")
                {
                    DDD = (from d in DDD orderby d.DealPrice descending select d).Skip((Page - 1) * PageSize).Take(PageSize);
                }
                else if (sortby == "Percent" && orderby == "Asc")
                {
                    DDD = (from d in DDD orderby d.SavingsRate ascending select d).Skip((Page - 1) * PageSize).Take(PageSize);
                }
                else if (sortby == "Percent" && orderby == "Desc")
                {
                    DDD = (from d in DDD orderby d.SavingsRate descending select d).Skip((Page - 1) * PageSize).Take(PageSize);
                }
                else
                {
                    DDD = DDD.Skip((Page - 1) * PageSize).Take(PageSize);
                }

                string Currency = "$";
                foreach (DealsDetails item in DDD)
                {
                    //Creating Html String Here
                }

                return jString;

我已在此处附加了我的整个代码，请检查这有什么问题，它需要很长时间才能响应大约 20 秒。

主要是 foreach 循环花费了超过 17 秒。

请让我知道如何编译此查询。

提前致谢。

Answer 1

除了预编译查询之外，您还应该预生成本地查询视图< /a>