none
Linq - Group items and then pull the first item from each group

    Question

  • Hello,

    I have a program that reads in several XML files and creates a list of objects.

    The object contains three properties:  Area, Item, Description.

    Each area contains multiple items and each item contains multiple descriptions as follows:

    AREA       ITEM   DESCRIPTION

    area 1 1 Text 1 - 1

    area 1 1 Text 2 - 1

    area 1 Text 3 - 1

    area 1 2 Text 1 - 2

    area 1  Text 2 - 2

    area 2 3 Text 1 - 3

    area 2 Text 2 - 3

    I would like to be able to write a single LINQ statement that pulls out the item and first descripton (Text 1) for each Item in a given area.

    The result for area 1 above would be:

    1   Text 1 - 1

    2 Text 1 - 2

     

    This is my starting point.

                  List<rowObject> itemsInArea = (from rowObject in listOfRowObjects

                                                   where rowObject.area == area 1

                                                   select rowObject).ToList();

    It just do not know how to craft the balance of the query.

    Any suggestions would be greatly appreciated.

     

    Chris

     

    Thursday, March 24, 2011 1:44 AM

Answers

  • Hello,

    I was still struggling to find a solution so I got a book on LINQ and it turns out that .Net has a very eloquent solution.

    The following statement does exactly what I need.  I will explain what it does.

     

    var results = listOfRowObjects.Where(c => c.area == area1).GroupBy(c => c.item, c => c.description, (key, elements) => new { Key = key, Description = elements.ElementAt(0) });

     

    See notes above for a representation of the list and the results I want.

    The query works as follows:

    listOfRowObjects  //tells the query from what data to pull

    Where(c => c.area == area1)  // reduces to the list to just those objects whose area property equals the area that has been assigned to the variable "area1"

    GroupBy(c => c.item, c => c.description, (key, elements) => new { Key = key, Description = elements.ElementAt(0) })  // breakdown as follows:

    GroupBy(xxxx , yyyy , zzzz)  // general from of one of the groupby constructors.

    xxxx =    "c => c.item"  // tells groupby that you want the key to be the item number.

    yyyy =    "c => c.description"  // tells groupby that you want the elements in the collection assigned to each key to be the associated descriptions.

    zzzz =    "(key, elements) => new { Key = key, Description = elements.ElementAt(0)}"  //  takes the key and elements assignments above and creates a projection (a new data set) with two fields Key and Description.  Key is assigned to equal the item number and Description is assigned to the first item in the collection of descriptions.

     

    Chris

     

     

    • Marked as answer by Ha567 Saturday, March 26, 2011 9:44 PM
    Saturday, March 26, 2011 9:44 PM

All replies

  • Hi HA567,

    I have prepared the class according to ur senario..(correct me if it's wrong)

     public class Area
      {
        public string AreaName { get; set; }
    
        private List<Item> _items = new List<Item>();
    
        public List<Item> Items
        {
          get { return _items; }
          set { _items = value; }
        }
    
      }
      public class Item
      {
        public string Itm { get; set; }
    
        private List<Description> _descriptions = new List<Description>();
    
        public List<Description> Descriptions
        {
          get { return _descriptions; }
          set { _descriptions = value; }
        } 
      }
      public class Description
      {
        public string Desc { get; set; }
      }

     

    Now i've added the values

    Area a1 = new Area();
    
           Description d1i1 = new Description { Desc = "D1I1"};
           Description d2i1 = new Description { Desc = "D2I1"};
           Item i1a1 = new Item();
           i1a1.Descriptions.Add(d1i1);
           i1a1.Descriptions.Add(d1i1);
    
           Description d1i2 = new Description { Desc = "D1I2" };
           Description d2i2 = new Description { Desc = "D2I2" };
    
           Item i2a1 = new Item();
           i2a1.Descriptions.Add(d1i2);
           i2a1.Descriptions.Add(d2i2);
    
           a1.AreaName = "Area1";
           a1.Items.Add(i1a1);
           a1.Items.Add(i2a1);
    
    
           Area a2 = new Area();
    
           Description d3i2 = new Description { Desc = "D3" };
           Description d4i2 = new Description { Desc = "D4" };
           Item i3a2 = new Item();
           i3a2.Descriptions.Add(d3i2);
           i3a2.Descriptions.Add(d4i2);
    
           Item i4a2 = new Item();
           i4a2.Descriptions.Add(d3i2);
           i4a2.Descriptions.Add(d4i2);
    
           a2.AreaName = "Area2";
           a2.Items.Add(i3a2);
           a2.Items.Add(i4a2);
    
           List<Area> listOfArea = new List<Area>();
           listOfArea.Add(a1);
           listOfArea.Add(a2);

     

    Now i've written the LiNQ query to find the records Gruped By Area and then selecting each Item with their first description only from the list of Descriptions

     var items = from c in listOfArea
                 group c by c.AreaName into g
                 select new {AreaName = g.Key,Item = g.Select(p => p.Items.ToList().Select(q => q.Descriptions.FirstOrDefault())) };

     

    correct me if ur requirement is differement

    hope this helps u...
    Thanks, BHavik
    Thursday, March 24, 2011 6:37 AM
  • Hi,a single LINQ statement  ia a good idea!

    But,in my opinion,you should sperate to several statements,it is easy to modify. When change function,maybe trouble.

    Thursday, March 24, 2011 9:26 AM
  • Thanks for the reply BHavik.

    Unfortunatley, the LINQ statement does not work.  It will not even compile.

    I have modified your query slightly and it works partially.

    The query is:

    string area1 = "area 4";

    var itemsInArea = from obj in listOfRowObjects

                                      where obj.area == area1

                                      group obj by obj.item into grp

                                      select new{Item = grp.Key, Descrip = grp.Select(d => d.descrip.FirstOrDefault().ToString())};

     

    Followed by:

    foreach (var x in itemsInArea)

                    {

                        MessageBox.Show("item , description = " + x.Item + " - " + x.Descrip);

                    }

    I get the following output in the message box:

    item, description = 1 - System.Linq.Enumerable+WhereSelectEnumerableIterator2[WpfBoundListView.CreateListOfObjects+rowObject,System.String]

    The message box shows up 99 times with x.item incrementing from 1 to 99.  There are 99 unique item numbers in area 4 so that part is ok.  I am just unable to get for each item number the first description.

    Since this part works I would guess that the following two statements work in the query:

    where obj.area == area1

    group obj by obj.item into grp

    If I understand it properly, the group by statement takes all of the objects with an area property = area1 and groups them by item number.

    It returns a collection of collections.  The main collection is the item numbers 1 thru 99 and each of these items contains a collection of descriptions.

    If I could just figure out how to pull the first description from the collection associated with each item number in the linq query.

    The list i am working from (listOfRowObjects) is a list of objects of type rowObjects:

    Area ------Item----Description

    area 1------1--------text 1-1

    area 1----- 1--------text 1-2

    area 1------1--------text 1-3

    area 1------2--------text 2-1

    area 1------2--------text 2-2

    area 2------3--------text 3-1

    area 2------3--------text 3-2

    If area1 = "area 1" I would like the following as the result:

    1-----text 1-1

    2-----text 2-1

    Note: I am using the dashes in hopes of keeping my format correct.  Please look at them like blank space.

    Any additional help/insight would be greatly appreciated.

    Chris

     

     

    Thursday, March 24, 2011 8:30 PM
  • Hi,

    My query will work according to the class structure i've provided in the reply....


    Thanks, BHavik
    Friday, March 25, 2011 3:56 AM
  • Hello,

    I was still struggling to find a solution so I got a book on LINQ and it turns out that .Net has a very eloquent solution.

    The following statement does exactly what I need.  I will explain what it does.

     

    var results = listOfRowObjects.Where(c => c.area == area1).GroupBy(c => c.item, c => c.description, (key, elements) => new { Key = key, Description = elements.ElementAt(0) });

     

    See notes above for a representation of the list and the results I want.

    The query works as follows:

    listOfRowObjects  //tells the query from what data to pull

    Where(c => c.area == area1)  // reduces to the list to just those objects whose area property equals the area that has been assigned to the variable "area1"

    GroupBy(c => c.item, c => c.description, (key, elements) => new { Key = key, Description = elements.ElementAt(0) })  // breakdown as follows:

    GroupBy(xxxx , yyyy , zzzz)  // general from of one of the groupby constructors.

    xxxx =    "c => c.item"  // tells groupby that you want the key to be the item number.

    yyyy =    "c => c.description"  // tells groupby that you want the elements in the collection assigned to each key to be the associated descriptions.

    zzzz =    "(key, elements) => new { Key = key, Description = elements.ElementAt(0)}"  //  takes the key and elements assignments above and creates a projection (a new data set) with two fields Key and Description.  Key is assigned to equal the item number and Description is assigned to the first item in the collection of descriptions.

     

    Chris

     

     

    • Marked as answer by Ha567 Saturday, March 26, 2011 9:44 PM
    Saturday, March 26, 2011 9:44 PM