Replace foreach to make loop into queryable

Question

I have a function returning a report object, but currently i am going through a foreach look and then using the asQueryable method.

I would like to do it in one query and not have to use the AsQueryable function.

var query = from item in context.Dealers
            where item.ManufacturerId == manufacturerId
            select item;

IList<DealerReport> list = new List<DealerReport>();

foreach (var deal in query)
{
  foreach (var bodyshop in deal.Bodyshops1.Where(x => x.Manufacturer2Bodyshop.Select(s => s.ManufacturerId).Contains(manufacturerId)))
  {
      DealerReport report = new DealerReport();
      report.Dealer = deal.Name;
      report.Bodyshop = bodyshop.Name;
      short stat = bodyshop.Manufacturer2Bodyshop.FirstOrDefault(x => x.ManufacturerId == manufacturerId).ComplianceStatus;
      report.StatusShort = stat;
      list.Add(report);
   }
}

return list.OrderBy(x => x.Dealer).AsQueryable();

Answer 1

I think you want something like this:

var query = from deal in context.Dealers
            where deal.ManufacturerId == manufacturerId
            from bodyshop in deal.Bodyshops1
            where bodyshop.Manufacturer2Bodyshop.Select(s => s.ManufacturerId).Contains(manufacturerId)
            let stat = bodyshop.Manufacturer2Bodyshop.FirstOrDefault(x => x.ManufacturerId == manufacturerId)
            orderby deal.Name
            select new DealerReport
            {
                Dealer = deal.Name,
                Bodyshop = bodyshop.Name,
                StatusShort = stat != null ? stat.ComplianceStatus : 0, // or some other default
            };

return query;