CODEWITHSUNDEEP
c#.netlinqasp.net-mvc-4
user2206329
user2206329

Reputation: 2842

When I call Tolist it takes so long to convert, how can I improve this?

I have the following bit of code and the last statement with the toList takes a long time. Any ideas how I can improve this?

//This statement takes less than 1 second
    var inspectorData = context.COESDetails.Where(x => x.UploadCOESDetails.AuditZoneId == criteria.AuditZoneId && x.UploadCOESDetails.AuditMonth.Contains(criteria.AuditYear)).Select(x => x.Inspector).Where(y => y.Id != 0).Distinct().OrderBy(x => x.Firstname).ToList();

//This statement takes less than 1 second
var coesData = context.COESDetails.Where(x => x.UploadCOESDetails.AuditZoneId == criteria.AuditZoneId && x.UploadCOESDetails.AuditMonth.Contains(criteria.AuditYear)).ToList();

//this takes less than 1 second
var nonComplianceData = inspectorData
                .Select(ud =>
                    new NonComplianceData
                    {
                        InspectorId = ud.Id,
                        InspectorName = ud.Firstname + " " + ud.Surname,
                        FullYearData = Constants.Months.Select(month => new MonthData
                        {
                            Month  = month,
                            TotalAuditsCompleted = coesData.Count(x => x.UploadCOESDetails.AuditZoneId == criteria.AuditZoneId && x.UploadCOESDetails.AuditMonth == (month + " " + criteria.AuditYear) && x.InspectorId == ud.Id && x.AuditType != (int)AuditType.NotSelected),
                            TotalNoDefects = coesData.Count(x => x.UploadCOESDetails.AuditZoneId == criteria.AuditZoneId && x.UploadCOESDetails.AuditMonth == (month + " " + criteria.AuditYear) && x.InspectorId == ud.Id && x.AuditType != (int)AuditType.NotSelected && x.COESDetailsCOESDefects.Any())
                        }).ToList()


                    });


// this statement takes about 14 seconds
return nonComplianceData.ToList();

I thought when I called the toList() in the first two statements, I thought the query was being executed and I had the required data. So why is the last Tolist() taking so long? considering all the required data is there already..

any insights? thoughts?

Upvotes: 0

Views: 5446

Answers (2)

Mike Koder
Mike Koder

Reputation: 1928

Assuming you are using Entity Framework (or other ORM)...
Those coesData.Count(... cause loading UploadCOESDetailsand COESDetailsCOESDefects lazily.

Use 

context.COESDetails
    .Include(x => x.UploadCOESDetails)
    .Include(x => x.COESDetailsCOESDefects)

to load them eagerly.

Or pick the things you need in the query

var coesData = context.CoesData.Select(x => new 
{ 
    x.Id,
    x.UploadCOESDetails.AuditZoneId,
    x.UploadCOESDetails.AuditMonth,
    x.InspectorId,
    x.AuditType,
    /* etc. */

    DefectCount = x.COESDetailsCOESDefects.Count(),
    /* or this if it works... */
    HasDefects = x.COESDetailsCOESDefects.Any()
})

Upvotes: 2

Robert Fricke
Robert Fricke

Reputation: 3643

When the last ToList() is called it will execute this code for all items in the nonCompianceData and that is probably what is taking so long.

FullYearData = Constants.Months.Select(month => new MonthData
{
    Month  = month,
    TotalAuditsCompleted = coesData.Count(x => x.UploadCOESDetails.AuditZoneId == criteria.AuditZoneId && x.UploadCOESDetails.AuditMonth == (month + " " + criteria.AuditYear) && x.InspectorId == ud.Id && x.AuditType != (int)AuditType.NotSelected),
    TotalNoDefects = coesData.Count(x => x.UploadCOESDetails.AuditZoneId == criteria.AuditZoneId && x.UploadCOESDetails.AuditMonth == (month + " " + criteria.AuditYear) && x.InspectorId == ud.Id && x.AuditType != (int)AuditType.NotSelected && x.COESDetailsCOESDefects.Any())
}).ToList()

What I understand you should not use ToList() until really necessary, before that use the IEnumerable<T> query returned by Linq.

Other than that it is about optimizing your Linq to generate faster SQL.

Upvotes: 1

Related Questions