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

本是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