c# linq syntax slow due to multiple queries in single query

Question

I am wondering if there is a better, more efficient way to re-code the linq syntax below to make the query run faster i.e. with a single call to the database. My database is located remotely which causes this to be quite slow:

    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

The main cause for the poor performance is the code in the function GetOutstandingBalanceForTicket below which calculates the sum of all items in an invoice and returns this as a total in a string:

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.##");
}

Thank you in advance.

Answer 1

As you pointed out this code is quite slow as a query will be required for each ticket.

to eliminate the need for multiple queries you should look at applying an inner join between the ticketsToShow and the tickets entity (on the ticketid), using groupby to provide the sum of the charges for each ticket.

This is well illustrated in the answers to LINQ: Using INNER JOIN, Group and SUM

Answer 2

Ideally you would probably approach it more as an eager loading all at once type of setup. However, I do not think linq2sql supports that (I know EF does). One thing you can do is avoid the nested query though. Since you already have access to the ticket table, perhaps you should just issue a Sum() on it from your select statement. Hard for me to verify if any of this is an improvement so this code is kind of on the fly if you will.

 //(from ticket in dataClassesDataContext.Tickets.Where(TicketsToShow.And(SearchVals))
 (from ticket in dataClassesDataContext.Tickets

//this would be where you could eager load if possible (not entirely required)
//.Include is an EF method used only as example
/*.Include(t => t.TicketPriority)//eager load required entities
 .Include(t => t.Asset)//eager load required entities
 .Include(t => t.Account.Customer)//eager load required entities
 .Include(t => t.TicketStatus)//eager load required entities
 .Include(t => t.ProductChargeItems)//eager load required entities
*/

 .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);
   }

Answer 3

I think, you can make this query simplier. Somethink like this:

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),

Now, you can inline this methos in your query.

It can contains syntax errors, because I wrote it in notepad.