EF优化第一弹

AllWrapperedProducts.cs

internal override IQueryable<Entity.ProductWrapper> AllWrapperedProducts()
{
    DateTime today = DateTime.Now.Date;

    var query = (from p in ProductDbContext.Product
                 where p.ResourceType == (int)ProductResourceType.产品
                 && p.PkgId.HasValue && p.PkgId.Value != 0
                 && p.ExpireDate.HasValue
                 && p.EffectDate.HasValue
                 && p.IsValid.HasValue && p.IsValid.Value
                 select p);

    if (GlobalConfig.IsProductEnvironment)
    {
        // 过滤掉未上架的产品
        query = from p in query
                where p.IsUp.HasValue && p.IsUp.Value
                select p;
    }

    if (!GlobalConfig.ContainsProductsIsSingleProduct)
    {
        // 过滤掉TravelType3的产品
        query = from p in query
                where p.TripType != (int)TravelType.TravelType3
                select p;
    }

    var query2 = from p in query
                 join prc in this.QueryProductsPriceCache()
                 on p.ProductId equals prc.ProductId into gPriceDate
                 where gPriceDate.Count() > 0
                 let minDate = gPriceDate.Min(dd => dd.PriceDate)
                 let maxDate = gPriceDate.Max(dd => dd.PriceDate)
                 let tempFirstDepartureDate = EntityFunctions.AddDays(today, p.AdvanceDays)
                 let firstDepartureDate = (minDate < tempFirstDepartureDate) ? tempFirstDepartureDate : minDate
                 let lastDepartureDate = maxDate
                 select new Entity.ProductWrapper
                 {
                     FirstDepartureDate = firstDepartureDate,
                     LastDepartureDate = lastDepartureDate,
                     Product = p,
                     PricesCache = gPriceDate,
                     MinPrice = gPriceDate.Min(pc => pc.MinPrice)
                 };
    return query2;
}

Map.cs

        protected virtual IEnumerable<Product> Map(IEnumerable<Entity.ProductWrapper> productEntities)
        {
            IEnumerable<Product> products;

            // 产品各图
            var picProvider = new PicturesProvider(ProductDbContext);
            Func<Entity.Picture, ImageInfo> getImageInfo = (pic) =>
            {
                if (pic == null || string.IsNullOrEmpty(pic.URL)) return null;
                return new ImageInfo { Title = pic.Title, Url = pic.URL };
            };

            IQueryable<Entity.ProductWrapper> query = productEntities as IQueryable<Entity.ProductWrapper>;
            if (query != null)
            {
                var q = from p in query
                        join pic in picProvider.FindBy(PictureObjectType.产品)
                        on p.Product.ProductId equals pic.ObjectId into gPics
                        join prop in ProductDbContext.Product_Property
                        on p.Product.ProductId equals prop.ProductId into gProps
                        select new
                        {
                            ProductWrapper = p,
                            Pictures = gPics.DefaultIfEmpty(),
                            Properties = gProps.DefaultIfEmpty(),
                        };
                var list = q.ToList();

                products = list.Select(p =>
                {
                    int productId = p.ProductWrapper.Product.ProductId;
                    var props = p.Properties;
                    var pics = p.Pictures;

                    var product = Map(p.ProductWrapper.Product);

                    if (!props.Any() || props.First() == null)
                    {
                        ExceptionSwitcher<DataInvalidException>.TryThrow(GlobalConfig.IsProductEnvironment,
                            new DataInvalidException(string.Format("product: {0} have not properties", productId)));
                    }
                    else
                    {
                        product.InterestList = GetInterests(props).ToList();

                        //product.DestinationGroupList = GetDestinationGroups(props).ToList();
                    }

                    if (pics.Any())
                    {
                        product.MainImage = getImageInfo(pics.FirstOrDefault(i => i != null && i.Location == PictureLocation.主图));

                        //product.RouteMap = getImageInfo(p.Pictures.FirstOrDefault(i => i.Location == PictureLocation.路线图));
                        //product.MainPhoto = getImageInfo(p.Pictures.FirstOrDefault(i => i.Location == PictureLocation.行程图 && i.Title == "1"));
                        //product.PhotoList = (from pic in p.Pictures.Where(i => i.Location == PictureLocation.行程图 && i.Title != "1")
                        //                     select getImageInfo(pic)).ToList();
                    }

                    // 可出发日期集合
                    var departureDateList = this.GetPriceDateList(p.ProductWrapper);
                    product.DepartureDateList = departureDateList;

                    SetProductMinPrice(product, p.ProductWrapper, departureDateList);

                    return product;
                });
            }
            else
            {
                var list = (from p in productEntities
                            join pic in picProvider.FindBy(PictureObjectType.产品)
                            on p.Product.ProductId equals pic.ObjectId into gPics
                            join prop in ProductDbContext.Product_Property
                            on p.Product.ProductId equals prop.ProductId into gProps
                            select new
                            {
                                ProductWrapper = p,
                                Pictures = gPics.DefaultIfEmpty(),
                                Properties = gProps.DefaultIfEmpty(),
                            }).ToList();

                products = list.Select(p =>
                {
                    int productId = p.ProductWrapper.Product.ProductId;
                    var props = p.Properties;
                    var pics = p.Pictures;

                    var product = Map(p.ProductWrapper.Product);

                    if (!props.Any() || props.First() == null)
                    {
                        ExceptionSwitcher<DataInvalidException>.TryThrow(GlobalConfig.IsProductEnvironment,
                            new DataInvalidException(string.Format("product: {0} have not properties", productId)));
                    }
                    else
                    {
                        product.InterestList = GetInterests(props).ToList();

                        //product.DestinationGroupList = GetDestinationGroups(props).ToList();
                    }

                    if (pics.Any())
                    {
                        product.MainImage = getImageInfo(pics.FirstOrDefault(i => i != null && i.Location == PictureLocation.主图));

                        //product.RouteMap = getImageInfo(p.Pictures.FirstOrDefault(i => i.Location == PictureLocation.路线图));
                        //product.MainPhoto = getImageInfo(p.Pictures.FirstOrDefault(i => i.Location == PictureLocation.行程图 && i.Title == "1"));
                        //product.PhotoList = (from pic in p.Pictures.Where(i => i.Location == PictureLocation.行程图 && i.Title != "1")
                        //                     select getImageInfo(pic)).ToList();
                    }

                    // 可出发日期集合
                    var departureDateList = this.GetPriceDateList(p.ProductWrapper);
                    product.DepartureDateList = departureDateList;

                    SetProductMinPrice(product, p.ProductWrapper, departureDateList);

                    return product;
                });
            }
            return products;
        }

QueryProductsPriceCache.cs

private IQueryable<Entity.Product_Price_Cache> QueryProductsPriceCache()
{
    Func<ProductDbEntities, IQueryable<Entity.Product_Price_Cache>> getQuery = (dbContext) =>
    {
        return from ppc in dbContext.Product_Price_Cache
               where (ppc.MinBasePrice.Value > 0 && ppc.MinAttachPrice == 0) || (ppc.IsComplete ?? false)
               select ppc;
    };

    // 10分钟为一个段 [13分的minSecId = 1] [59分的minSecId = 5] [03分的minSecId = 0]
    int minuteSecId = DateTime.Now.Minute / 10;
    var query = getQuery(this.ProductDbContext);
    if (query.Count(prc => prc.CacheHour == minuteSecId) == 0)   // 当前时间没有数据，则取最小时间
    {
        SysLog.WriteTrace("PriceCache Miss1", string.Format("minuteSecId: {0}, Server Time: {1}", minuteSecId, DateTime.Now));

        var queryNoCached = getQuery(this.ProductDbContext);
        minuteSecId = queryNoCached.Min(prc => prc.CacheHour);

        SysLog.WriteTrace("PriceCache get minMinuteSecId", string.Format("minuteSecId: {0}, Server Time: {1}", minuteSecId, DateTime.Now));
    }

    query = from ppc in query
            where ppc.CacheHour == minuteSecId
            select ppc;

    return query;
}

Search.cs

public virtual IEnumerable<Product> Search(SearchCondition searchCondition,
            Pager pager)
{
    IEnumerable<Product> products;

    var query = this.AllWrapperedProducts();

    query = (searchCondition.DepartureCity != null) ? this.FindByDepartureCity(query, searchCondition.DepartureCity.Value.CityCode) : query;
    query = searchCondition.DestinationGroup.HasValue ? this.FindByProperty(query, searchCondition.DestinationGroup.Value) : query;
    query = searchCondition.DestinationRegion.HasValue ? this.FindByProperty(query, searchCondition.DestinationRegion.Value) : query;
    query = searchCondition.TravelType.HasValue ? this.FindByTravelType(query, searchCondition.TravelType.Value) : query;
    query = searchCondition.Interest.HasValue ? this.FindByProperty(query, searchCondition.Interest.Value) : query;

    query = searchCondition.MinDays.HasValue ? query.Where(pw => pw.Product.TripDays >= searchCondition.MinDays.Value) : query;
    query = searchCondition.MaxDays.HasValue ? query.Where(pw => pw.Product.TripDays <= searchCondition.MaxDays.Value) : query;

    query = !string.IsNullOrEmpty(searchCondition.ProductName) ? this.FindByProductName(query, searchCondition.ProductName) : query;

    // 出发日期相关的搜索处理
    if (searchCondition.BeginDate.HasValue || searchCondition.EndDate.HasValue)
    {
        var pws = SearchByDepartureDate(query, searchCondition.BeginDate, searchCondition.EndDate);
        var b = this.Page(pws, pager);
        products = Map(b);
    }
    else
    {
        if (pager.SortRule == SortRule.ProductMinDepartureDate)
        {
            var pws = SortByMinDepartureDate(query);
            var b = this.Page(pws, pager);
            products = Map(b);
        }
        else
        {
            var b = this.LazyPage(query, pager);
            products = Map(b);
        }
    }

    return products;
}

SearchByDepartureDate.cs

protected override IEnumerable<Entity.ProductWrapper> SearchByDepartureDate(IQueryable<Entity.ProductWrapper> query, DateTime? beginDate, DateTime? endDate)
{
    var a = from pw in query
            join pr in this.QueryProductsPriceCache()
            on pw.Product.ProductId equals pr.ProductId into gDepartureDate // 按出发日期分组
            where gDepartureDate.Any(dd => dd.PriceDate >= beginDate && dd.PriceDate <= endDate)
            select pw;
    return a;
}

哇，好长好长。。。
用缓存吧。。。

WTF, It's so fvvking long. . .
Let's cache it...

表捶我!
Don't kick my ass!