Problem VB.NET LINQ Group joining

Ok I know there is a lot of posts on this but I have trying to solve this for a few hours and my brain is completely off.

Here is the thing

I have a list of object with 3 properties (quantity, service, name)

I want to have all names and services in my list (kinda cross join) and the grouped quantity of the row corresponding. Not clear i guess

Quantity Service Name 3 Srv2 Bob 4 Srv2 Paul 2 Srv1 Paul 1 Srv2 Nick

I want as output All the services and all the names and corresponding quantities (0 if none)

Srv1 Paul 2 Srv1 Bob 0 Srv1 Nick 0 Srv2 Paul 4 Srv2 Bob 3 Srv2 Nick 1

Here is what I got so far, but I dont even get the expected results And I am acutally certain there is a pretty easy way of achieving what i want... I little help would be welcome...


    Dim services = (From a In interventions Select New With {.Service = a.Service}).Distinct()
    Dim months = (From b In interventions Select New With {.Month = b.DateHeureIntervention.Month}).Distinct()

    'Dim query = (From s In services _
    '             From m In months _
    '             From i In interventions.Where(Function(x) x.Service = s.Service And x.DateHeureIntervention.Month = m.Month).DefaultIfEmpty(New Intervention()) _
    '             Select New ChartItem With {.Service = s.Service, _
    '                                        .Name = MonthName(m.Month), _
    '                                        .Quantite = i.Quantite}).ToList()

    'Return (From q In query _
    '       Group By srv = q.Service, n = q.Name Into Group _
    '       Select New ChartItem With {.Name = n, _
    '                                  .Service = srv, _
    '                                  .Quantite = Group.Sum(Function(i) i.Quantite)}).ToList()

    Dim q = (From i In interventions _
            Group Join s In services On s.Service Equals i.Service Into si = _
            Group Join m In months On m.Month Equals i.DateHeureIntervention.Month _
            From x In si.DefaultIfEmpty() _
            Group By m = i.DateHeureIntervention.Month, srv = i.Service Into Group _
            Select New ChartItem With {.Service = srv, _
                                       .Name = MonthName(m), _
                                       .Quantite = Group.Sum(Function(y) y.i.Quantite)}).ToList()

    Return q


I got it here is my result, there surely is a better way, but anyhow, it works

    Dim months As List(Of Integer) = (From b In interventions Select b.DateHeureIntervention.Month).Distinct().ToList()
    Dim services As List(Of String) = (From c In interventions Select c.Service).Distinct().ToList()

    Dim q = (From s In services _
             From m In months _
             Select New ChartItem With {.Name = MonthName(m), _
                                        .Service = s, _
                                        .Quantite = (From i In AccesDonneesHelper.GetInterventionsDetails() _
                                                     Where i.Service = s And i.DateHeureIntervention.Month = m _
                                                     Select i.Quantite).Sum()}).ToList()

Need Your Help

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.