Home

Grouping Records

   

Grouping the Values

 

Uniting the Records

If you have two sets of records of the same type, you can unite them into a new common list. This is referred to as uniting the lists. The lists can be created as an array or a List<> object. To give you the ability to unite two lists, the Enumerable class is equipped with a method named Union. Its syntax is:

public static IEnumerable Union(this IEnumerable first, IEnumerable second);

The variable that calls this method and the argument must be of the same type. Here is an example:

private void btnUnionClick(object sender, EventArgs e)
{
    var names = new string[]
    {
        "Jerrie Sachs",
        "Stevens Souza",
        "Marianne Swanson",
        "Alain Gudmundson",
        "Jeannette Perkins",
        "Pierrette Perkins"
    };
            
    var values  = new string[]
    {
        "Gertrude Monay",
        "Raymond Kouma"
    };

    IEnumerable<string> result = numbers.Union<string>(values);

    foreach (var member in result)
        lbxNames.Items.Add(member.ToString());
}

In the same way, you can unite records from lists of classes. The most important rule to observe is that both lists must have values that are of the same type.

Grouping by Categories

In all statements we have created so far, we were storing the results in a select variable, and the select variable was able to provide its list when necessary. We also learned how to sort the values in select list. As an alternative, you can ask the compiler to produce a list where the values are grouped by categories. For example, if you have a list of students, you may want the list to be organized by gender, or by another category.

To support grouping, the LINQ provides the group...by operation. The primary formula to use it is:

var SubListName = from ValueHolder in List group ValueHolder by Category;

The new keywords are group and by. They are required. The ValueHolder factor is the same variable created from the from operator. The Category is the new factor of our formula. It must be a value that can be gotten from the ValueHolder list. Here is an example:

var videos = from vdos
             in lstVideos
             group vdos by vdos.Rating;

The group...by expression creates a list of categories, not a list of values, like the select statement would do. This means that each item of the list produced by the group...by clause is a category. Therefore, to access a category, use a foreach loop that applies to each item of the list. Here is an example:

var videos = from vdos
             in lstVideos
             group vdos by vdos.Rating;

foreach (var toView in videos)
{
      
}

Each category of the group...by list is its own list of items. Therefore, after accessing a category, inside of it, you can access an item. To do this, use a nested foreach loop. Here is an example:

using System;
using System.Linq;
using System.Drawing;
using System.Windows.Forms;
using System.Collections.Generic;

public class Exercise : Form
{
    private Button btnShow;

    private ColumnHeader colShelfNumber;
    private ColumnHeader colTitle;
    private ColumnHeader colDirector;
    private ColumnHeader colLength;
    private ColumnHeader colRating;
    private ColumnHeader colYearReleased;

    ListView lvwVideos;

    public Exercise()
    {
        InitializeComponent();
    }

    void InitializeComponent()
    {
        btnShow = new Button();
        btnShow.Location = new System.Drawing.Point(12, 12);
        btnShow.Width = 75;
        btnShow.Text = "Show";
        btnShow.Click += new System.EventHandler(this.btnShow_Click);

        lvwVideos = new ListView();
        lvwVideos.Anchor = AnchorStyles.Left | AnchorStyles.Top |
                             AnchorStyles.Right | AnchorStyles.Bottom;
        lvwVideos.FullRowSelect = true;
        lvwVideos.GridLines = true;
        lvwVideos.Location = new Point(12, 40);
        lvwVideos.Size = new System.Drawing.Size(490, 192);
        lvwVideos.View = View.Details;

        colShelfNumber = new ColumnHeader();
        colShelfNumber.Text = "Shelf #";
        colShelfNumber.Width = 50;
        lvwVideos.Columns.Add(colShelfNumber);

        colTitle = new ColumnHeader();
        colTitle.Text = "Title";
        colTitle.Width = 160;
        lvwVideos.Columns.Add(colTitle);

        colDirector = new ColumnHeader();
        colDirector.Text = "Director";
        colDirector.Width = 130;
        lvwVideos.Columns.Add(colDirector);

        colLength = new ColumnHeader();
        colLength.Text = "Length";
        colLength.Width = 50;
        colLength.TextAlign = HorizontalAlignment.Right;
        lvwVideos.Columns.Add(colLength);

        colRating = new ColumnHeader();
        colRating.Text = "Rating";
        colRating.Width = 50;
        colRating.TextAlign = HorizontalAlignment.Center;
        lvwVideos.Columns.Add(colRating);

        colYearReleased = new ColumnHeader();
        colYearReleased.Text = "Year";
        colYearReleased.Width = 40;
        colYearReleased.TextAlign = HorizontalAlignment.Right;
        lvwVideos.Columns.Add(colYearReleased);

        Size = new System.Drawing.Size(520, 270);
        Controls.Add(this.btnShow);
        Controls.Add(this.lvwVideos);
        Text = "Students Records";
    }

    private void btnShow_Click(object sender, EventArgs e)
    {
        Video vdo = null;

        List<Video> lstVideos = new List<Video>();

        lstVideos.Add(new Video("730294", "Two for the Money", "D.J. Caruso", "123", "R", "2008"));
        lstVideos.Add(new Video("283759", "Her Alibi", "Bruce Beresford", "94", "PG-13", "1998"));
        
        vdo = new Video();
        vdo.Title = "Distinguished Gentleman (The)";
        vdo.Director = "Jonathan Lynn";
        vdo.Length = "112";
        vdo.Rating = "R";
        lstVideos.Add(vdo);

        lstVideos.Add(new Video("905840", "Memoirs of a Geisha", "Rob Marshall", "145", "PG-13", "2006"));
        
        vdo = new Video();
        vdo.Title = "Silence of the Lambs (The)";
        vdo.Director = "Jonathan Demme";
        vdo.YearReleased = "1991";
        vdo.Length = "118";
        lstVideos.Add(vdo);

        lstVideos.Add(new Video("849613", "Wall Street", "Oliver Stone", "126", "R", "2000"));
      
        vdo = new Video();
        vdo.Title = "A Few Good Men";
        vdo.Director = "Rob Reiner";
        vdo.YearReleased = "1992";
        vdo.Length = "138";
        lstVideos.Add(vdo);

        vdo = new Video();
        vdo.ShelfNumber = "707461";
        vdo.Title = "Michael Jackson Live in Bucharest";
        vdo.Director = "Andy Morahan";
        vdo.Length = "122";
		vdo.Rating = "N/R";
        vdo.YearReleased = "1992";
        lstVideos.Add(vdo);
        
        vdo = new Video();
        vdo.ShelfNumber = "479703";
        vdo.Title = "Lady Killers (The)";
        vdo.Director = "Joel Coen & Ethan Coen";
		vdo.Rating = "R";
        vdo.Length = "104";
        lstVideos.Add(vdo);
        
        vdo = new Video();
        vdo.Title = "Outfoxed";
        vdo.Director = "Robert Greenwald";

        lstVideos.Add(new Video("900741", "Sneakers", "Phil Alden Robinson", "126", "PG-13", "1992"));
        lstVideos.Add(new Video("119411", "Soldier", "Paul Anderson", "99", "R", "1998"));
        lstVideos.Add(new Video("279357", "War of the Roses (The)", "Dany de Vito", "116", "R", "2001"));
        lstVideos.Add(new Video("927947", "Last Castle (The)", "Rod Lurie", "133", "R", "2001"));
        lstVideos.Add(new Video("", "Ghosts of Mississippi", "Rob Reiner", "130", "", ""));      

        var videos = from vdos
                     in lstVideos
                     group vdos by vdos.Rating;

        foreach (var toView in videos)
        {
            foreach (Video item in toView)
            {
                ListViewItem lviStudent = new ListViewItem(item.ShelfNumber);

                lviStudent.SubItems.Add(item.Title);
                lviStudent.SubItems.Add(item.Director);
                lviStudent.SubItems.Add(item.Length);
                lviStudent.SubItems.Add(item.Rating);
                lviStudent.SubItems.Add(item.YearReleased);
                lvwVideos.Items.Add(lviStudent);
            }
        }
    }

    [STAThread]
    public static int Main()
    {
        System.Windows.Forms.Application.Run(new Exercise());
        return 0;
    }
}

public class Video
{
    public string ShelfNumber { get; set; }
    public string Title { get; set; }
    public string Director { get; set; }
    public string Length { get; set; }
    public string Rating { get; set; }
    public string YearReleased { get; set; }

    public Video(string number = "",
                 string ttl = "",
                 string dir = "",
                 string len = "",
                 string ratings = "",
                 string year = "")
    {
        ShelfNumber = number;
        Title = ttl;
        Director = dir;
        Length = len;
        Rating = ratings;
        YearReleased = year;
    }
}

This would produce:

Video Collection

Notice that you do not create a select statement after the group...by clause.

To restrict the list of records in the result, you can add a where condition. Here is an example:

var videos = from vdos
             in lstVideos
             where vdos.Title.StartsWith("S")
             group vdos by vdos.Rating;

This would produce:

Video Collection

Practical Learning: Introducing Operations

  1. Start Microsoft Visual Studio
  2. Open the AltairRealtors2 application
  3. Display the form and change its design as follows:
     
    Altair Realtors
     
    Control Text Name Other Properties
    GroupBox Group By   Anchor: Bottom, Left, Right
    RadioButton Radio Button Property Type rdoPropertyType CheckAlin: MiddleRight
    RadioButton Radio Button State rdoState CheckAlin: MiddleRight
    RadioButton Radio Button Property Condition rdoPropertyCondition CheckAlin: MiddleRight
    RadioButton Radio Button None rdoNone Checked: True
    CheckAlin: MiddleRight
    Button Button Close btnClose Anchor: Bottom, Right
  4. Save all

The Key to a Group

When you create grouping of values, the resulting list is stored in a variable of type IGrouping. The IGrouping interface is defined in the System.Linq namespace of the System.Core.dll assembly. The IGrouping interface is derived from the IEnumerable interface. This means that it gets most of its behaviors from that interface. This also means that using the IGrouping interface gives you access to the members of the Enumerable class.

The IGrouping interface is a generic class declared as follows:

public interface IGrouping<TKey, TElement> : IEnumerable<TElement>,
					     IEnumerable

In our introduction to grouping, we saw that its operation identifies the categories of items from the from variable. Each category is referred to as a key and each category can be recognized as a TKey object of the IGrouping list. This allows you to access each category. In fact, you can access a category and perform an operation on it.

Although the IGrouping interface inherits most of its functionality from the IEnumerable interface and implemented through the Enumerable class, it is equipped with only one property, named Key. To get the value of an IGrouping category, you can retrieve it from the Key property.

Practical Learning: Grouping the Categories

  1. Double-click the Property Type radio button and implement its event as follows:
    private void rdoPropertyType_CheckedChanged(object sender, EventArgs e)
    {
                lvwProperties.Items.Clear();
                colPropertyType.Width = 1;
                colState.Width = 38;
                colCondition.Width = 65;
    
                var properties = from props
                                 in lstProperties
                                 group props by props.Type;
    
                ListViewItem lviProperty = null;
                ListViewGroup grpSingleFamilies = new ListViewGroup("Single Families");
                ListViewGroup grpTownhouses = new ListViewGroup("Townhouses");
                ListViewGroup grpCondominiuims = new ListViewGroup("Condominiums");
                ListViewGroup grpOthers = new ListViewGroup("Others");
    
                int i = 1;
                foreach (var propertyTypes in properties)
                {
                    foreach (Property prop in propertyTypes)
                    {
                        if (propertyTypes.Key.ToString() == "SingleFamily")
                        {
                            lviProperty = new ListViewItem(i.ToString(), grpSingleFamilies);
                        }
                        else if (propertyTypes.Key.ToString() == "Townhouse")
                        {
                            lviProperty = new ListViewItem(i.ToString(), grpTownhouses);
                        }
                        else if (propertyTypes.Key.ToString() == "Condominium")
                        {
                            lviProperty = new ListViewItem(i.ToString(), grpCondominiuims);
                        }
                        else
                        {
                            lviProperty = new ListViewItem(i.ToString(), grpOthers);
                        }
                        lviProperty.SubItems.Add(prop.PropertyNumber.ToString());
                        lviProperty.SubItems.Add(prop.Type.ToString());
                        lviProperty.SubItems.Add(prop.City);
                        lviProperty.SubItems.Add(prop.State);
                        lviProperty.SubItems.Add(prop.Condition.ToString());
                        lviProperty.SubItems.Add(prop.Bedrooms.ToString());
                        lviProperty.SubItems.Add(prop.Bathrooms.ToString("F"));
                        lviProperty.SubItems.Add(prop.Stories.ToString());
                        lviProperty.SubItems.Add(prop.YearBuilt.ToString());
                        lviProperty.SubItems.Add(prop.MarketValue.ToString("F"));
    
                        lvwProperties.Items.Add(lviProperty);
                        lvwProperties.Groups.Add(grpSingleFamilies);
                        lvwProperties.Groups.Add(grpTownhouses);
                        lvwProperties.Groups.Add(grpOthers);
                        lvwProperties.Groups.Add(grpCondominiuims);
    
                        i++;
                    }
                }
            }
  2. Return to the form and double-click the State radio button
  3. Implement the event as follows:
    private void rdoState_CheckedChanged(object sender, EventArgs e)
    {
                lvwProperties.Items.Clear();
                colState.Width = 1;
                colPropertyType.Width = 75;
                colCondition.Width = 65;
    
                var properties = from props
                                 in lstProperties 
                                 group props by props.State;
    
                ListViewItem lviProperty = null;
                ListViewGroup grpDC = new ListViewGroup("District of Columbia");
                ListViewGroup grpMaryland = new ListViewGroup("Maryland");
                ListViewGroup grpVirginia = new ListViewGroup("Virginia");
                ListViewGroup grpWestVirginia = new ListViewGroup("West Virginia");
                ListViewGroup grpPennsylvania = new ListViewGroup("Pennsylvania");
    
                int i = 1;
                foreach (var States in properties)
                {
                    foreach (Property prop in States)
                    {
                        if (States.Key.ToString() == "DC")
                        {
                            lviProperty = new ListViewItem(i.ToString(), grpDC);
                        }
                        else if (States.Key.ToString() == "MD")
                        {
                            lviProperty = new ListViewItem(i.ToString(), grpMaryland);
                        }
                        else if (States.Key.ToString() == "VA")
                        {
                            lviProperty = new ListViewItem(i.ToString(), grpVirginia);
                        }
                        else if (States.Key.ToString() == "WV")
                        {
                            lviProperty = new ListViewItem(i.ToString(), grpWestVirginia);
                        }
                        else
                        {
                            lviProperty = new ListViewItem(i.ToString(), grpPennsylvania);
                        }
                        lviProperty.SubItems.Add(prop.PropertyNumber.ToString());
                        lviProperty.SubItems.Add(prop.Type.ToString());
                        lviProperty.SubItems.Add(prop.City);
                        lviProperty.SubItems.Add(prop.State);
                        lviProperty.SubItems.Add(prop.Condition.ToString());
                        lviProperty.SubItems.Add(prop.Bedrooms.ToString());
                        lviProperty.SubItems.Add(prop.Bathrooms.ToString("F"));
                        lviProperty.SubItems.Add(prop.Stories.ToString());
                        lviProperty.SubItems.Add(prop.YearBuilt.ToString());
                        lviProperty.SubItems.Add(prop.MarketValue.ToString("F"));
    
                        lvwProperties.Items.Add(lviProperty);
                        lvwProperties.Groups.Add(grpMaryland);
                        lvwProperties.Groups.Add(grpVirginia);
                        lvwProperties.Groups.Add(grpWestVirginia);
                        lvwProperties.Groups.Add(grpDC);
                        lvwProperties.Groups.Add(grpPennsylvania);
    
                        i++;
                    }
                }
    }
  4. Return to the form and double-click the Property Condition radio button
  5. Implement the event as follows:
    private void rdoPropertyCondition_CheckedChanged(object sender, EventArgs e)
    {
                lvwProperties.Items.Clear();
                colState.Width = 38;
                colPropertyType.Width = 75;
                colCondition.Width = 1;
    
                var properties = from Props
                                 in lstProperties 
                                 group Props by Props.Condition;
    
                ListViewItem lviProperty = null;
                ListViewGroup grpExcellent = new ListViewGroup("Excellent Condition");
                ListViewGroup grpGoodCondition = new ListViewGroup("Good Shape");
                ListViewGroup grpBadShape = new ListViewGroup("Bad - May Need Some Repair");
                ListViewGroup grpOther = new ListViewGroup("Other - Either Unknown or Useless (Land Important)");
    
                int i = 1;
                foreach (var Conditions in properties)
                {
                    foreach (Property prop in Conditions)
                    {
                        if (Conditions.Key.ToString() == "Excellent")
                        {
                            lviProperty = new ListViewItem(i.ToString(), grpExcellent);
                        }
                        else if (Conditions.Key.ToString() == "Good")
                        {
                            lviProperty = new ListViewItem(i.ToString(), grpGoodCondition);
                        }
                        else if (Conditions.Key.ToString() == "BadShape")
                        {
                            lviProperty = new ListViewItem(i.ToString(), grpBadShape);
                        }
                        else
                        {
                            lviProperty = new ListViewItem(i.ToString(), grpOther);
                        }
    
                        lviProperty.SubItems.Add(prop.PropertyNumber.ToString());
                        lviProperty.SubItems.Add(prop.Type.ToString());
                        lviProperty.SubItems.Add(prop.City);
                        lviProperty.SubItems.Add(prop.State);
                        lviProperty.SubItems.Add(prop.Condition.ToString());
                        lviProperty.SubItems.Add(prop.Bedrooms.ToString());
                        lviProperty.SubItems.Add(prop.Bathrooms.ToString("F"));
                        lviProperty.SubItems.Add(prop.Stories.ToString());
                        lviProperty.SubItems.Add(prop.YearBuilt.ToString());
                        lviProperty.SubItems.Add(prop.MarketValue.ToString("F"));
    
                        lvwProperties.Items.Add(lviProperty);
                        lvwProperties.Groups.Add(grpGoodCondition);
                        lvwProperties.Groups.Add(grpExcellent);
                        lvwProperties.Groups.Add(grpOther);
                        lvwProperties.Groups.Add(grpBadShape);
    
                        i++;
                    }
                }
    }
  6. Return to the form and double-click the None radio button
  7. Implement the event as follows:
    private void rdoNone_CheckedChanged(object sender, EventArgs e)
    {
                lvwProperties.Items.Clear();
                lvwProperties.Groups.Clear();
                colState.Width = 38;
                colPropertyType.Width = 75;
                colCondition.Width = 65;
    
                var properties = from Props
                                 in lstProperties 
                                 select Props;
    
                int i = 1;
    
        foreach (Property prop in properties)
        {
            ListViewItem lviProperty = new ListViewItem(i.ToString());
    
            lviProperty.SubItems.Add(prop.PropertyNumber.ToString());
            lviProperty.SubItems.Add(prop.Type.ToString());
            lviProperty.SubItems.Add(prop.City);
            lviProperty.SubItems.Add(prop.State);
            lviProperty.SubItems.Add(prop.Condition.ToString());
            lviProperty.SubItems.Add(prop.Bedrooms.ToString());
            lviProperty.SubItems.Add(prop.Bathrooms.ToString("F"));
            lviProperty.SubItems.Add(prop.Stories.ToString());
            lviProperty.SubItems.Add(prop.YearBuilt.ToString());
            lviProperty.SubItems.Add(prop.MarketValue.ToString("F"));
    
            lvwProperties.Items.Add(lviProperty);
            i++;
        }
    }
  8. Execute the application to test the radio buttons
     
    Altair Realtors
     
    Altair Realtors
     
    Altair Realtors
     
    Altair Realtors
  9. Close the form and return to your programming environment

Grouping Into a Variable

When you create a grouping, you get a list of categories of values and that list becomes ready to be used. In some cases, before exploring the list, you may want to perform an operation on it. One way you can do this, you can store that list in a (local) variable and use that variable as if it were a from variable.

To declare a variable to store the grouping values, you use the into contextual keyword through the following formula:

var SubListName = from ValueHolder
		  in List
		  group ValueHolder by Category into GroupVariable ...;

The GroupVariable is the new factor in our formula. You specify it as a regular name of a variable. Here is an example:

var empls = from staffMembers
             in employees
             group staffMembers by staffMembers.Gender into Categories

After creating the name, you can perform any operation on it inside the LINQ statement. The variable is of type IGrouping. This means that you can access its Key property or you can access one of the methods that the interface gets from IEnumerable, and then use it as you see fit. Here is an example:

var empls = from staffMembers
             in employees
             group staffMembers by staffMembers.Gender into Categories
             where Categories.Contains(students[0])

Before ending the LINQ statement, you must create either a group...by expression or a select statement that uses the into variable. Here is an example:

var videos = from vdos
             in lstVideos
             group vdos by vdos.Rating into categories
             where categories.Contains(lstVideos[0])
             select categories;

This statement, particularly the Enumerable.Contains(lstVideos[0]) produces only the category (group) identified as the first index (0) of the values in the main list (students):

Video Collection

Notice that all records in the final result have a common category, which in this case is the R rating of each video. For this reason, you can omit that column when presenting the values to the user. Here is an example (the column for the rating was removed from the list view):

var videos = from vdos
             in lstVideos
             group vdos by vdos.Rating into categories
             where categories.Contains(lstVideos[0])
             select categories;

foreach (var toView in videos)
{
    foreach (Video item in toView)
    {
        ListViewItem lviStudent = new ListViewItem(item.ShelfNumber);

        lviStudent.SubItems.Add(item.Title);
        lviStudent.SubItems.Add(item.Director);
        lviStudent.SubItems.Add(item.Length);
        // lviStudent.SubItems.Add(item.Rating);
        lviStudent.SubItems.Add(item.YearReleased);
        lvwVideos.Items.Add(lviStudent);
    }
}

This would produce:

Video Collection

In the same, to get the category stored in the second index of the grouping, you would use Enumerable.Contains(lstVideos[1]). Of course this means that you can use grouping and the into operator to get a list of items of only one particular category.

Although the GroupVariable can be selected or grouped...by, it cannot be used outside the LINQ statement. It is only available in the local LINQ expression.

 

Previous Copyright © 2010-2016, FunctionX Next