how to fix the following issue in linq?

Question

I am creating an MVC 4 application. There is a point where I need to populate a model with some data.

My model is the following,

public class NonComplianceData
{
    public NonComplianceData()
    {
        FullYearData = new List<MonthData>
        {
            new MonthData { Month = "January"  },
            new MonthData { Month = "February"  },
            new MonthData { Month = "March"  },
            new MonthData { Month = "April"  },
            new MonthData { Month = "May"  },
            new MonthData { Month = "June"  },
            new MonthData { Month = "July"  },
            new MonthData { Month = "August"  },
            new MonthData { Month = "September"  },
            new MonthData { Month = "October"  },
            new MonthData { Month = "November"  },
            new MonthData { Month = "December"  },
        };

    }

    public int InspectorId { get; set; }
    public string InspectorName { get; set; }
    public IEnumerable<MonthData> FullYearData { get; set; }
}

public class MonthData
{
    public string Month { get; set; }
    public int TotalAuditsCompleted { get; set; }
    public int TotalNoDefects { get; set; }
    public float NonComplianceRate { get; set; }
}

To get the data I have gone this far...

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)
    .Select(ud => new NonComplianceData
    {
        InspectorId = ud.Id,
        InspectorName = ud.Firstname + " " + ud.Surname,
        FullYearData =  ???????
    });

What I am having trouble with is the FullYearData. I need a way to loop through the fullYeardata, pick each month and do more queries. Is it possible for me to do this through linq?

I managed to get what I need by doing the following. Its a few foreach loops.. can this be done through linq? Where I am setting the InspectorName etc?

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)
        .Select(ud => new NonComplianceData
            {
                InspectorId = ud.Id,
                InspectorName = ud.Firstname + " " + ud.Surname
            }).ToList();

foreach (var Inspector in InspectorData)
{
    foreach (var MonthData in Inspector.FullYearData)
    {
        var auditMonthYear = MonthData.Month + " " + criteria.AuditYear;
        MonthData.TotalAuditsCompleted = context.COESDetails.Count(x => x.UploadCOESDetails.AuditZoneId == criteria.AuditZoneId && x.UploadCOESDetails.AuditMonth == auditMonthYear && x.InspectorId == Inspector.InspectorId && x.AuditType != (int)AuditType.NotSelected);
        MonthData.TotalNoDefects = context.COESDetails.Count(x => x.UploadCOESDetails.AuditZoneId == criteria.AuditZoneId && x.UploadCOESDetails.AuditMonth == auditMonthYear && x.InspectorId == Inspector.InspectorId && x.AuditType != (int)AuditType.NotSelected && x.COESDetailsCOESDefects.Any());
    }
}

any help would be apprecaited

Thanks

MiddlePat - is this what you mean? This does not work

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) 
                    .Select(ud =>
                        new NonComplianceData
                        {
                            InspectorId = ud.Id,
                            InspectorName = ud.Firstname + " " + ud.Surname,
FullYearData = Inspector.FullYearData.Select(fyd => {
MonthData monthData = new MonthData();
var auditMonthYear = MonthData.Month + " " + criteria.AuditYear;
monthData.TotalAuditsCompleted = context.COESDetails.Count(x => x.UploadCOESDetails.AuditZoneId == criteria.AuditZoneId && x.UploadCOESDetails.AuditMonth == auditMonthYear && x.InspectorId == Inspector.InspectorId && x.AuditType != (int)AuditType.NotSelected);
monthData.TotalNoDefects = context.COESDetails.Count(x => x.UploadCOESDetails.AuditZoneId == criteria.AuditZoneId && x.UploadCOESDetails.AuditMonth == auditMonthYear && x.InspectorId == Inspector.InspectorId && x.AuditType != (int)AuditType.NotSelected && x.COESDetailsCOESDefects.Any());

return monthData;}).ToList()
                        }).ToList();

Answer 1

.Select(ud => new NonComplianceData
        {
            InspectorId = ud.Id,
            InspectorName = ud.Firstname + " " + ud.Surname,
            FullYearData =  FullYearData.Select(fyd => fyd.Month) //results in an IEnumerable<String>
       });

You can go on making subqueries in linqqueries. You can also apply another query on the selected list.

For example:

FullYearData = Inspector.FullYearData.Select(fyd => {
MonthData monthData = new MonthData();
var auditMonthYear = MonthData.Month + " " + criteria.AuditYear;
monthData.TotalAuditsCompleted = context.COESDetails.Count(x => x.UploadCOESDetails.AuditZoneId == criteria.AuditZoneId && x.UploadCOESDetails.AuditMonth == auditMonthYear && x.InspectorId == Inspector.InspectorId && x.AuditType != (int)AuditType.NotSelected);
monthData.TotalNoDefects = context.COESDetails.Count(x => x.UploadCOESDetails.AuditZoneId == criteria.AuditZoneId && x.UploadCOESDetails.AuditMonth == auditMonthYear && x.InspectorId == Inspector.InspectorId && x.AuditType != (int)AuditType.NotSelected && x.COESDetailsCOESDefects.Any());

return monthData;}).ToList()