Dynamic LinqToSQL pivot table query

Question

Hi All

http://img7.imageshack.us/img7/3050/downtime.png

I have two calendar pickers ( To and From ) I need to build the Year1 -> 4 dynamicly. I am also getting dupplicate records for 1 items that have values for 2006 and 2009. They can select 100 years if they wanted too.Check attached image.

  public ActionResult DownTimeSummaryTabular(int Start,int End)
    {
        var q = from item in new iSppms.Models.iSppmsDataContext().Incidents
                group item by new
                {
                    item.Supplier.Id,
                    item.Supplier.Name,
                    item.SupplierPlant,item.DownTime
                }
                    into supplier
                    select new
                    {
                        SupplierId = supplier.Key.Id,
                        SupplierName = supplier.Key.Name,
                        SupplierPlant = supplier.Key.SupplierPlant.Plant,
                        Years = from incident in supplier
                                let year = incident.IncidentDate.Year
                                where year <= End and year >= Start
                                group incident by year into incidentForYear
                                select incidentForYear.DownTime
                    };

        return View();
    }

Answer 1

My eye! I wish I had the power to edit.

Here's my solution to the problem:

// This should be in your controller
var q = from item in new iSppms.DataAccess.IncidentRepository()
        group item by new { 
            item.Supplier.Id, 
            item.Supplier.Name, 
            item.Supplier.Plant } 
        into supplier 
        select new {
          SupplierId = supplier.Key.Id,
          SupplierName = supplier.Key.Name,
          SupplierPlant = supplier.Key.Plant,
          Years = from incident in supplier
                  let year = incident.IncidentDate.Year
                  where year <= EndYear and year >= StartYear
                  group incident by year into incidentForYear
                  select incidentForYear.DownTime.ToIntOrDefault()
        }

<%        
 foreach (var row in q)
 { %>
            <tr>
                <td>
                    <%= incident.SupplierName %>
                </td>
                <td>
                    <%= incident.SupplierPlant %>
                </td>
  <% for(var y = StartYear; y < EndYear; ++y) 
     { 
        var year = row.Years[y]; %>                
                <td>
                    <%= year.Sum() %>
                </td>
<% } %>                
            </tr>
 <% } %>

And this is the extension method to make the conversion nicer.

 public static int ToIntOrDefault(this string value)
 {
  int result;
  Int32.TryParse(value, out result);
  return result;
 }

Note that you have way too much code in your view. The work to choose what gets displayed should be done in your controller, including converting to ints. The view should just blindly iterate over collections (one could argue that the "Sum()" should be done in the controller as well).