tnblog
首页

用Linq解决商品和其下sku的筛选排序查询

68人阅读 2018/12/27 15:53 评论:0 收藏
分类: Linq

本是sql to linq的东西,但是用的框架是.core 2.1,所以就归到core里吧


最近遇到一个稍微复杂点的小问题

需求:商城的商品列表排序,按商品排序编号,同商品下sku的最低价来升降排序

开始做成拆分为几步,代码比较复杂,效率不高,后面找到了简单的解决方案:


                List<ProListModel> ProList = new List<ProListModel>();

                var query = (from pro in context.ShopProductInfo.Where(@where)
                             join sku in context.ShopSkuProduct.Where(skuwhere)
                             on pro.ProductNo equals sku.ProductNo
                             group sku by sku.ProductNo into g
                             select new ProListModel
                             {
                                 Id = context.ShopProductInfo.SingleOrDefault(c => c.ProductNo == g.Key).Id,
                                 ProductName = context.ShopProductInfo.SingleOrDefault(c => c.ProductNo == g.Key).ProductName,
                                 ProductNo = g.Key,
                                 Type = context.ShopProductInfo.SingleOrDefault(c => c.ProductNo == g.Key).Type,
                                 ShortDesc = context.ShopProductInfo.SingleOrDefault(c => c.ProductNo == g.Key).ShortDesc,
                                 ThumbImage = context.ShopProductInfo.SingleOrDefault(c => c.ProductNo == g.Key).ThumbImage,
                                 CategoryId = context.ShopProductInfo.SingleOrDefault(c => c.ProductNo == g.Key).CategoryId,
                                 Price = g.OrderBy(c => c.SalePrice).FirstOrDefault().SalePrice,
                                 ShowOrder = context.ShopProductInfo.SingleOrDefault(c => c.ProductNo == g.Key).ShowOrder,
                             }).AsQueryable();

                if (parameter.Sort == "desc")
                {
                    query = query.OrderByDescending(p => p.Price);
                }
                else if (parameter.Sort == "asc")
                {
                    query = query.OrderBy(p => p.Price);
                }
                else
                {
                    query = query.OrderByDescending(p => p.CreateTime).OrderBy(p => p.ShowOrder);
                }

                result.Total = query.ToList().Count();

                result.Data = new GetProductListResult
                {
                    ProList = query.Skip(parameter.PageSize * (parameter.PageIndex - 1))
                    .Take(parameter.PageSize)
                    .ToList(),
                };

这里主要是归纳一下两表联查的时候根据不同顺序,不同字段排序的优解方案以及Linq分组后进行表内排序的语法


/*-----------------------------------------                    分                鸽                线                    -------------------------------------------*/

上面的方法虽然解决了问题,但是放到实际场景时。接口处理的速度很慢,不传筛选的参数大概3s,传了有时候会到4~5s之久

所以又找了很多方法,各种尝试


一个半小时奋战,终于得到跟简单的方法


var query = (from sku in context.ShopSkuProduct.Where(skuwhere)
                             .GroupBy(o => o.ProductNo)
                                 .Select(o => new
                                 {
                                     ProductNo = o.Key,
                                     Saleprice = o.Min(x => x.SalePrice)
                                 })
                             join pro in context.ShopProductInfo.AsNoTracking().Where(@where)
                             on sku.ProductNo equals pro.ProductNo 
                             select new ProListModel
                             {
                                 ProductName = pro.ProductName,
                                 ProductNo = pro.ProductNo,
                                 ThumbImage = pro.ThumbImage,
                                 Price = sku.Saleprice,
                                 CreateTime = pro.CreateTime,
                                 ShowOrder = pro.ShowOrder,
                             }).AsQueryable();

把原来的query部分换成新的,速度飞快


hiahiahia~~perfect

评价

{{item.title}}

{{item.content}}
酸甜苦辣才是生活 起起浮浮才是人生
博主搜索
文章类别
最新文章
最新评价
{{item.ArticleTitle}}
{{item.BlogName}} : {{item.Content}}