none
Group by a group RRS feed

  • Question

  • I have two tables. One contains one or more part numbers associated with a vendor. So you might see something like:

    PartNumber Vendor
    1                A
    2                B
    3                A
    4                B

    The other table gives be orders. So this table looks like:

    PartNumber Date       Quantity
    1                1/1/2009 10
    2                1/2/2009 2
    3                1/1/2009 5
    4                1/3/2009 6

    Because of other queries I form a List<> of orders that fall with in a certain date range. The partial query that I have come up with so far is:


                            var orders = from o in orderHistory
                                         group o by ??? into vendorList
                                         select new
                                         {
                                             Vendor = vendorList.Key,
                                             Summary = from h in vendorList
                                                       group h by new DateTime(h.Date.Year, h.Date.Month, 1) into skuHistory
                                                       orderby skuHistory.Key
                                                       select new { Period = skuHistory.Key, Quantity = skuHistory.Sum(s => s.Quantity) }
                                         };
    

    The question is what to put in the ??? spot. The orderHistory is the List<> of orders that I described above and the selection groups the orders into a period. Basically I want this group to group all of the part numbers (skus) that are tied to a particular vendor (as specified by the table described above) and the associated vendor name to be in the vendorList.Key. I am not sure how to frame this query. Any help?

    Thank you.
    Wednesday, June 10, 2009 5:25 PM

Answers

  • Try the folloving decision. I suppose that both tables are in 1to1 relation

    var orders = from o in orderHistory
                 join v in Vendors on o.PartNumber equals v.PartNumber 
    group new { o, v } by o.Vendor into vendorList
    select new { Vendor = vendorList.Key, Summary = from h in vendorList.o group h by new DateTime(h.Date.Year, h.Date.Month, 1) into skuHistory orderby skuHistory.Key select new { Period = skuHistory.Key, Quantity = skuHistory.Sum(s => s.Quantity) } };
    • Marked as answer by KevinBurton Thursday, June 11, 2009 7:08 PM
    • Edited by Devart team Friday, June 12, 2009 8:00 AM
    Thursday, June 11, 2009 5:39 PM

All replies

  • Have you tried "o.Vendor"?

    Also, is this a LINQ to SQL query?  And if so, do you have association properties set up?

    Can you tell us the final result that you're after?

    Joe



    Write LINQ queries interactively - www.linqpad.net
    Thursday, June 11, 2009 12:50 AM
    Answerer
  • The orderHistory is generated with a LINQ to SQL query but as above it is just a List<T>.

    In the end I want a sequence that is grouped by Vendor.  So still using the above sample data I would end up with a swquence like:

    Vendor Date       Quantity
    A         1/1/2009 15
    B         1/2/2009   2
    B         1/3/2009   6

    I haven't further grouped the sequence into a period (which is what the sub-query does) as that may complicate matters.

    Kevin

    Thursday, June 11, 2009 3:27 PM
  • Try the folloving decision. I suppose that both tables are in 1to1 relation

    var orders = from o in orderHistory
                 join v in Vendors on o.PartNumber equals v.PartNumber 
    group new { o, v } by o.Vendor into vendorList
    select new { Vendor = vendorList.Key, Summary = from h in vendorList.o group h by new DateTime(h.Date.Year, h.Date.Month, 1) into skuHistory orderby skuHistory.Key select new { Period = skuHistory.Key, Quantity = skuHistory.Sum(s => s.Quantity) } };
    • Marked as answer by KevinBurton Thursday, June 11, 2009 7:08 PM
    • Edited by Devart team Friday, June 12, 2009 8:00 AM
    Thursday, June 11, 2009 5:39 PM
  • Thank you. I am unfamiliar with the new {o,v} syntax. This was the magic I guess I was missing. Can you elighten me as to what this statement along with the group does? Thanks.

     

    Kevin

     

    Thursday, June 11, 2009 7:08 PM
  • To undestand this see Expression's which are generated for this query. "new {o,v}" becomes a source for Queryable.GroupBy. And "group" is translated to the next

    .GroupBy(
     source => source.O.Vendor, // key selector
     source => new vendorListAnonymousType0`2( //result selector
      c = source.c, 
      o = source.o
     )
    )
    
    Friday, June 12, 2009 8:09 AM
  • I am sorry but Vendor is not a member of o (orderHistory). The orderHistory only has part numbers. The Vendors table would have the mapping from part number to vendor.

    So the Vendor table basically looks like:

    Vendor PartNumber

    And the order table (order history) has

    PartNumber Date Quantity

    Also there can be a (and often is) more than one vendor for a given part number.

    Kevin
    Friday, June 12, 2009 8:32 PM
  • Excuse me, of course the correct method call is the following

    .GroupBy(
     source => source.v.Vendor, // key selector
     source => new { //result selector
      o = source.o, 
      v = source.v
     }
    )

    where "source" and result selector are the complex object "new {o,v}".

    Friday, June 12, 2009 10:14 PM
  • The problem that I don't understand is the 'group new {o,v} by o.Vendor' when o.Vendor doesn't exist. The Vendors table (v) is the only table that has the name/identifier of the vendor in it.

    Thanks again.

    Kevin
    Friday, June 12, 2009 10:19 PM
  • It is mistake.
    var orders = from o in orderHistory
                 join v in Vendors on o.PartNumber equals v.PartNumber
                 group new { o, v } by v.Vendor into vendorList
                 select new
                                         {
                                             Vendor = vendorList.Key,
                                             Summary = from h in vendorList.o
                                                       group h by new DateTime(h.Date.Year, h.Date.Month, 1) into skuHistory
                                                       orderby skuHistory.Key
                                                       select new { Period = skuHistory.Key, Quantity = skuHistory.Sum(s => s.Quantity) }
                                         };
    
    Saturday, June 13, 2009 5:04 AM