How to improve this C# Linq Concat Example?-CodePudding

The following C# Linq example displays the correct records in a serverSide datatable. However, when I add the following line: data = data.Concat(unconfirmed);, it increases the page/datatable load time from ~500 ms to ~2 seconds when year equals the current year. Searching by the current year displays the most amount of records, but they are only ~90. Searching by a previous year displays ~10 records at most and it only takes ~500 ms for the page/datatable to load:

var data = _db.ResidencyConfirmations.AsQueryable();

var year = Request.Form.GetValues("columns[11][search][value]")[0];
var showConfirmed = Request.Form.GetValues("columns[12][search][value]")[0];

int.TryParse(year, out var resultYear);

data = data.Where(rc => rc.Application.ApplicationType.Label == "Home Rehab");

var allNotesReleased = data.Where(rc => rc.Application.ApplicationActivityPhas
        .Where(aap => aap.ActivityPhas.WorkFlowStep == 6)
        .Select(aap => aap.ActivityPhas.ActivityPhase)
        .FirstOrDefault() == "All Notes Released" &&
    rc.Application.ApplicationActivityPhas
        .Where(aap => aap.ActivityPhas.WorkFlowStep == 6)
        .Select(aap => aap.ActivityPhaseDate)
        .FirstOrDefault().Year >= resultYear);

var complete = data.Where(rc => rc.Application.ApplicationActivityPhas
        .Where(aap => aap.ActivityPhas.WorkFlowStep == rc.Application.ApplicationActivityPhas
        .Max(x => x.ActivityPhas.WorkFlowStep))
        .Select(aap => aap.ActivityPhas.ActivityPhase)
        .FirstOrDefault() == "Complete" &&
    rc.Application.ApplicationActivityPhas
        .Where(aap => aap.ActivityPhas.WorkFlowStep == 5)
        .Select(aap => aap.ActivityPhaseDate)
        .FirstOrDefault().Year < resultYear);

var unconfirmed = complete.Where(rc => !rc.Application.ResidencyConfirmations
    .Any(x => x.ResidencyConfirmationDate.Year == resultYear))
    .GroupBy(rc => rc.Application.AppNumber)
    .Select(rc => rc.OrderByDescending(x => x.ResidencyConfirmationDate)
    .FirstOrDefault());

data = allNotesReleased.Concat(complete)
    .Where(rc => rc.ResidencyConfirmationDate.Year == resultYear);

if (showConfirmed == "false")
    data = data.Where(rc => !rc.Deed || !rc.Utility || !rc.VitalRecords);

data = data.Concat(unconfirmed);

CodePudding user response：

Not sure how it compiles, written in text editor, but query should be faster. Note that instead of using .Year introduced range variables, it will speedup query if you have indexes on date.

var year = Request.Form.GetValues("columns[11][search][value]")[0];
var showConfirmed = Request.Form.GetValues("columns[12][search][value]")[0];

int.TryParse(year, out var resultYear);

var startOfTheYear = new DateTime(resultYear, 1, 1);
var nextYear = startOfTheYear.AddYears(1);

var data = _db.ResidencyConfirmations.AsQueryable();

data = data.Where(rc => rc.Application.ApplicationType.Label == "Home Rehab");


var allNotesReleased = 
    from rc in data
    from aap in rc.Application.ApplicationActivityPhas
        .Where(aap => aap.ActivityPhas.WorkFlowStep == 6)
        .Take(1)
    where 
        aap.ActivityPhas.ActivityPhase == "All Notes Released" &&
        app.ActivityPhaseDate >= startOfTheYear &&
        rc.ResidencyConfirmationDate >= startOfTheYear && rc.ResidencyConfirmationDate < nextYear
    select rc;

var complete = 
    from rc in data
    from aap in rc.Application.ApplicationActivityPhas
        .OrderByDescending(aap => aap.WorkFlowStep)
        .Take(1)
    from aap2 in rc.Application.ApplicationActivityPhas
        .Where(aap2 => aap2.ActivityPhas.WorkFlowStep == 5)
        .Take(1)
    where 
        aap.ActivityPhas.ActivityPhase == "Complete" &&
        app2.ActivityPhaseDate < startOfTheYear &&
        rc.ResidencyConfirmationDate >= startOfTheYear && rc.ResidencyConfirmationDate < nextYear
    select rc;

var unconfirmed = complete
    .Where(rc => !rc.Application.ResidencyConfirmations
        .Any(x => x.ResidencyConfirmationDate >= startOfTheYear && x.ResidencyConfirmationDate < nextYear)
    )
    .GroupBy(rc => rc.Application.AppNumber)
    .Select(rc => rc
        .OrderByDescending(x => x.ResidencyConfirmationDate)
        .First()
    );

if (showConfirmed == "false")
    data = data.Where(rc => !rc.Deed || !rc.Utility || !rc.VitalRecords);

data = data.Concat(unconfirmed);

CodePudding user response：

To improve performance and to display the correct records, I made the following changes/additions:

if (resultYear == DateTime.Now.Year)
    data = data.Where(rc => rc.Application.ApplicationType.Label == "Home Rehab")
        .GroupBy(rc => rc.Application.AppNumber)
        .Select(rc => rc.OrderByDescending(x => x.ResidencyConfirmationDate)
        .FirstOrDefault());
else
    data = data.Where(rc => rc.Application.ApplicationType.Label == "Home Rehab");
    
IQueryable<ResidencyConfirmation> unconfirmed = null;

if (resultYear == DateTime.Now.Year)
    unconfirmed = complete.Where(rc => !rc.Application.ResidencyConfirmations
        .Any(x => x.ResidencyConfirmationDate.Year == resultYear));
else
    unconfirmed = complete.Where(rc => !rc.Application.ResidencyConfirmations
        .Any(x => x.ResidencyConfirmationDate.Year == resultYear))
        .GroupBy(rc => rc.Application.AppNumber)
        .Select(rc => rc.OrderByDescending(x => x.ResidencyConfirmationDate)
        .FirstOrDefault());

It now take right under 1 second for the page/datatable to load.