Performance issue in linq query-CodePudding

I have created a linq query to join tables and to do aggregate function and it takes more than a minute and it is affecting performance when executing the query in database it takes 18 seconds Kindly help me to improve the performance of Linq query.

Code:

List<int> oStatus = new List<int> { (int)STATUS.PAID, (int)STATUS.PARTIALY_PAID, (int)STATUS.OPEN, (int)STATUS.COMPLETED };

List<int> oLocationids = (
    from loc in oTransactionContext.Location 
    join lum in oTransactionContext.LocationUserMap on loc.LocationId equals lum.LocationId
    where lum.UserId == iUserID && loc.LocationName != "Local Purchase" && loc.LocationId !=0 &&loc.IsActive==true
    select lum.LocationId
    ).ToList();

    var oPT_Det = (
        from inv in oTransactionContext.Invoice
        join loc in oTransactionContext.Location on inv.LocationId equals loc.LocationId
        where (inv.ClientId == (iClientID) && oLocationids.Contains(inv.LocationId.GetValueOrDefault()) &&
        (inv.EndTime > DateTime.UtcNow.AddMonths(-6))
        && inv.IsActive == (true) && oStatus.Contains(inv.Status.GetValueOrDefault()))
        group new { inv, loc } by new { inv.LocationId, loc.LocationName } into groupResult
        select new OverAllSales
        {
            Location = Helper.CommonHelper.ParseString(groupResult.Key.LocationName),
            InvoiceAmount = Helper.CommonHelper.ParseDecimal(groupResult.Sum(f => f.inv.TotalInvoiceAmount)),
        }
        ).ToList();

Postgresql:

SELECT
    COALESCE(SUM(inv.total_invoice_amount), 0) AS value1,
    loc.location_name                          AS metric
FROM location loc
     LEFT JOIN location_user_map LUM ON LUM.location_id = loc.location_id
     LEFT OUTER JOIN invoice inv ON inv.client_id =2 AND inv.location_id = loc.location_id
        AND inv.status IN (SELECT status_id FROM status WHERE status IN ('Paid', 'Partialy Paid', 'Open', 'Completed'))
WHERE loc.location_name NOT IN ('Local Purchase')
  AND loc.location_id != 0
  AND LUM.user_id IN ($user_ids)
  AND inv.is_active = TRUE
  AND CAST(inv.end_time AS date) > CURRENT_DATE - INTERVAL '2' MONTH
GROUP BY loc.location_name
ORDER BY value1 DESC

CodePudding user response：

This is direct translation of the SQL. Should generate similar SQL query.

var startDate = DateTime.Date.AddMonths(-2);

int clientId = ...
int userId = ...

var oStatus = new List<int> { (int)STATUS.PAID, (int)STATUS.PARTIALY_PAID, (int)STATUS.OPEN, (int)STATUS.COMPLETED };

var query = 
    from loc in oTransactionContext.Location 
    join lum in oTransactionContext.LocationUserMap on loc.LocationId equals lum.LocationId
    from inv in oTransactionContext.Invoice
        .Where(inv => inv.ClientId == clientId && inv.LocationId = loc.LocationId 
            && oStatus.Contains(inv.Status))
    where 
        loc.LocationName != "Local Purchase"
        && loc.LocationId !=0
        && lum.UserId == userId
        && inv.IsActive == true
        && inv.end_time >= startDate
    group inv by loc.LocationName into g
    select new 
    {
        Location = g.Key,
        InvoiceAmount = g.Sum(x => x.total_invoice_amount) ?? 0
    };

var oPT_Det = query.ToList();