由于单个查询中有多个查询，c# linq语法较慢
我想知道是否有更好、更有效的方法来重新编码下面的linq语法，以使查询运行得更快，即通过对数据库的一次调用。我的数据库位于远程位置，这导致速度非常慢：
var query = (from ticket in dataClassesDataContext.Tickets.Where(TicketsToShow.And(SearchVals))
select new
{
Priority = ticket.TicketPriority.TicketPriorityName,
Ticket = string.Format(TicketFormat, ticket.TicketID),
AssetId = ticket.Asset.Serial,
OpenDate = ticket.CheckedInDate,
OpenFor = CalculateOpenDaysAndHours(ticket.CheckedInDate, ticket.ClosedDate),
Account = ticket.Account.Customer.Name,
Description = ticket.Description.Replace("\n", ", "),
Status = ticket.TicketStatus.TicketStatusName,
Closed = ticket.ClosedDate,
THIS IS THE CAUSE ====>>> Amount = GetOutstandingBalanceForTicket(ticket.TicketID),
Paid = ticket.Paid,
Warranty = ticket.WarrantyRepair,
AssetLocation = GetAssetLocationNameFromID(ticket.Asset.LocationID, AssLocNames)
}).Skip(totalToDisplay * page).Take(totalToDisplay);
if (SortOrder.ToLower().Contains("Asc".ToLower()))
{
query = query.OrderBy(p => p.OpenDate);
}
else
{
query = query.OrderByDescending(p => p.OpenDate);
}//ENDIF性能不佳的主要原因是下面的函数GetOutstandingBalanceForTicket中的代码，该代码计算发票中所有项目的总和，并将其作为字符串中的总数返回：
public static string GetOutstandingBalanceForTicket(int TicketID)
{
string result = string.Empty;
decimal total = 0;
try
{
using (DataClassesDataContext dataClassesDataContext = new DataClassesDataContext(cDbConnection.GetConnectionString()))
{
var queryCustomerTickets = from ticket in dataClassesDataContext.Tickets
where
(ticket.TicketID == TicketID)
select ticket;
if (queryCustomerTickets != null)
{
foreach (var ticket in queryCustomerTickets)
{
var queryTicketChargeItems = from chargeItem in dataClassesDataContext.ProductChargeItems
where chargeItem.ChargeID == ticket.ChargeID &&
chargeItem.Deleted == null
select chargeItem;
foreach (var chargeItem in queryTicketChargeItems)
{
total += (chargeItem.Qty * chargeItem.Price);
}
}
}
}
}
catch (Exception ex)
{
}
return total.ToString("0.##");
}提前谢谢你。
回答 3
Stack Overflow用户
发布于 2014-01-17 07:33:53
正如您所指出的，这段代码相当慢，因为每个票证都需要一个查询。
要消除多个查询的需要，您应该考虑在ticketsToShow和tickets实体(在ticketid上)之间应用内连接，使用groupby提供每个票证的费用总和。
这在对LINQ: Using INNER JOIN, Group and SUM的回答中得到了很好的说明
Stack Overflow用户
发布于 2014-01-17 07:40:25
理想情况下，您可能会更多地将其视为一次加载所有类型的设置。然而，我不认为linq2sql支持这一点(我知道EF支持)。不过，您可以做的一件事是避免嵌套查询。由于您已经有权访问ticket表，因此您可能应该从select语句中对其发出Sum()。我很难验证这是否是一种改进，所以如果你愿意的话，这段代码是一种即时的。
//(from ticket in dataClassesDataContext.Tickets.Where(TicketsToShow.And(SearchVals))
(from ticket in dataClassesDataContext.Tickets//如果可能，您可以在此处进行紧急加载(并非完全必需)//。包含仅用作示例的EF方法/*.Include(t => t.TicketPriority)//紧急加载必需的实体.Include(t => t.Asset)//紧急加载必需的实体.Include(t => t.Account.Customer)//紧急加载必需的实体.Include(t => t.TicketStatus)//紧急加载必需的实体.Include(t => t.ProductChargeItems)//紧急加载必需的实体*/
.Where(TicketsToShow.And(SearchVals))
select new
{
Priority = ticket.TicketPriority.TicketPriorityName,
Ticket = string.Format(TicketFormat, ticket.TicketID),
AssetId = ticket.Asset.Serial,
OpenDate = ticket.CheckedInDate,
OpenFor = CalculateOpenDaysAndHours(ticket.CheckedInDate, ticket.ClosedDate),
Account = ticket.Account.Customer.Name,
Description = ticket.Description.Replace("\n", ", "),
Status = ticket.TicketStatus.TicketStatusName,
Closed = ticket.ClosedDate,
//Use Sum and the foreign relation instead of a nested query
Amount = ticket.ProductChargeItems.Where(pci => pci.Deleted == null).Sum(pci => pci.Qty * pci.Price),
Paid = ticket.Paid,
Warranty = ticket.WarrantyRepair,
AssetLocation = GetAssetLocationNameFromID(ticket.Asset.LocationID, AssLocNames)
}).Skip(totalToDisplay * page).Take(totalToDisplay);
if (SortOrder.ToLower().Contains("Asc".ToLower()))
{
query = query.OrderBy(p => p.OpenDate);
}
else
{
query = query.OrderByDescending(p => p.OpenDate);
}Stack Overflow用户
发布于 2014-01-17 07:43:17
我认为，您可以使这个查询更简单。有人是这样想的：
public static string GetOutstandingBalanceForTicket(DataClassesDataContext context, int TicketID)
{
decimal total = 0;
var total = (from ticket in context.Tickets
join chargeItem from context.ProductChargeItems on chargeItem.ChargeID == ticket.ChargeID
where (ticket.TicketID == TicketID && chargeItem.Deleted == null)
select chargeItem).Sum(chargeItem => chargeItem.Qty * chargeItem.Price);
return total.ToString("0.##");
}
/*...*/
Amount = GetOutstandingBalanceForTicket(dataClassesDataContext, ticket.TicketID),现在，您可以在查询中内联此方法。
它可能包含语法错误，因为我是用记事本写的。
https://stackoverflow.com/questions/21174898
复制相似问题
腾讯云开发者
