分类:
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
评价
