I tried delete a statement that use contain() and use skip() take() but it's still take a long time on execution
public JsonNetResult GetList(C.Lib.ListParam param, string type, DateTime? WorkDate, string MNo, string ANo, string AName, string Specifi, string PNo, string SName, string PType, string OrNo)
{
var idObj = (mis.Models.Security.CIdentity)Csla.ApplicationContext.User.Identity;
var c = new mis.Models.CEntities();
var targetList = (from obj in c.WorkOrders
join obj2 in c.Partners on obj.PartnerId equals obj2.PartnerId
join obj3 in c.Assets on obj.AssetsId equals obj3.AssetsId
join obj4 in c.WorkOrderItems on obj.WorkOrderId equals obj4.WorkOrderId
join obj5 in c.WorkItems on obj4.WorkId equals obj5.WorkId
join obj6 in c.Products on obj3.AssetsId equals obj6.AssetsId
where true && obj.TenantId == idObj.TenantId && obj2.Customer == true && obj.WorkOrderStatus <= 1
select new
{
WorkProgressId = obj.WorkOrderId,
OrderNo = obj.InOrderItem.OrderItem.Order.OrderNo,
ProductType = obj5.ProductType,
WorkOrderId = obj.WorkOrderId,
MakeQty = obj.MakeQty,//delete some column
PrepaidDate = obj.PrepaidDate,
CompleteDate = (from subobj in c.WorkOrderItems
where subobj.WorkOrderId == obj.WorkOrderId && subobj.CompleteDate != null
orderby subobj.CompleteDate descending, subobj.WorkItem.WorkNo descending
select subobj.CompleteDate).FirstOrDefault(),
WorkNoName = (from subobj in c.WorkOrderItems
where subobj.WorkOrderId == obj.WorkOrderId && subobj.CompleteDate != null
orderby subobj.WorkItem.WorkNo descending
select new { WorkNoName = subobj.WorkItem.WorkNo + " " + subobj.WorkItem.WorkName }).FirstOrDefault().WorkNoName,
});
targetList = targetList.GroupBy(x => x.MakeNo).Select(x => new
{
WorkProgressId = x.FirstOrDefault().WorkOrderId,
OrderNo = x.FirstOrDefault().OrderNo,
ProductType = x.FirstOrDefault().ProductType,
WorkOrderId = x.FirstOrDefault().WorkOrderId,
WorkOrderDate = x.FirstOrDefault().WorkOrderDate,
OrderQty = x.FirstOrDefault().OrderQty,
MakeQty = x.FirstOrDefault().MakeQty,
PrepaidDate = x.FirstOrDefault().PrepaidDate,//delete some column
CompleteDate = x.FirstOrDefault().CompleteDate,
WorkNoName = x.FirstOrDefault().WorkNoName
}).OrderByDescending(x => x.WorkOrderDate).ThenByDescending(x => x.WorkNoName);
param.SetCount(targetList);
targetList.OrderByDescending(x => x.WorkOrderDate).ThenByDescending(x=> x.WorkNoName);
var tk= targetList.Skip((param.Page - 1) * param.Rows).Take(param.Rows);
return JsonNetHelper.ReturnData(param,tk);
}
I'm wondering what is the best way to optimize it? Or would the query profiler do that for me?
