Hooked on LINQ

Hooked on LINQ - Developers' Wiki
for .NET Language Integrated Query

 Companion book for this site
LINQ to Objects Using C# 4.0:
Using and Extending LINQ to Objects and Parallel LINQ (PLINQ) 		Quick Search

Advanced Search »
Edit

Chapter 4 - Grouping and Sorting


Table of Contents [Hide/Show]


Chapter 4 - Grouping and Sorting
   Grouping
      Listing 4-1 : Simple grouping
      Listing 4-2 : Handling null values using the null-coalescing-operator (??) and the ternary operator (?).
      Listing 4 : Handling null values
      Listing 4-3 : Grouping by multiple values
      Listing 4-4 : Grouping by multiple values
      Listing 4-5 : Soundex Custom Equality Comparer
      Listing 4-6 : Custom Equality Comparer
      Listing 4-7 : Custom element projection into group.
      Listing 4-8 : Custom element projection into group.
      Listing 4-9 : Grouping Query Continuation and Aggregation.
      Listing 4-10 : Nested Grouping Query Continuation.
   Joining
      Listing 4 : Cross-Join example.
      Listing 4-11 : Cross-Join binary sequence example.
      Listing 4 : Cross-Join bitmap sequence.
      Listing 4 : Simple Join example.
      Listing 4-12 : One-one join using Join syntax.
      Listing 4-13 : One-one outer-join using the Join syntax.
      Listing 4 : Composite-key join example.
      Listing 4-14 : One-one join using Sub-Query syntax.
      Listing 4-15 : One-one join using SingleOrDefault syntax.
      Listing 4-16 : One-one join using cross-join/where syntax.
      Listing 4-12,14,15,16 : One-one joins two collections.
      Listing 4-17 : One-many join using Join/Into syntax.
      Listing 4-18 : One-many join using sub-query syntax.
      Listing 4-19 : One-many join using ToLookup operator syntax.
      Listing 4-17,18,19 : One-Many joins two collections.




Edit

Grouping

Edit

Listing 4-1 : Simple grouping

This sample demonstrates grouping, and then working with each group.

public void Listing_4_1_Simple_Grouping()
{
    string[] partNumbers = new string[] { "SCW10", "SCW1", 
        "SCW2", "SCW11", "NUT10", "NUT1", "NUT2", "NUT11" };
    
    var q = from pn in partNumbers
            group pn by pn.Substring(0, 3);
 
    foreach (var group in q)
    {
        Console.WriteLine("Group key: {0}", group.Key);
        foreach (var part in group)
            Console.WriteLine(" – {0}", part);
    }
}
 

Console output (Execution time: 3ms): [Hide/Show]


Top



Edit

Listing 4-2 : Handling null values using the null-coalescing-operator (??) and the ternary operator (?).

This sample demonstrates how to handle null values in the selector expression when grouping

public void Listing_4_2_Handling_Nulls_WithNCOAndTernary()
{
    // Guard against null data using 
    // ternary (? as shown) 
    var q1 = from c in Contact.SampleData()
             group c by
                 c.State == null ? "(null)" : c.State;
 
    // Guard against null data using 
    // the null coalescing operator (??).
    var q2 = from c in Contact.SampleData()
             group c by
                 c.State ?? "(null)";
}
 



Top



Edit

Listing 4 : Handling null values

This sample demonstrates how to handle null values in the selector expression when grouping

public void Listing_4_Handling_Nulls()
{
    string[] partNumbers = new string[] { "SCW10", null, 
        "SCW2", "SCW11", null, "NUT1", "NUT2", null };
 
    // Advice – Guard against null data using ternary 
    // (? as shown) or the null coalescing operator (??).
    var q1 = from pn in partNumbers
             group pn by 
                 pn == null ? "(null)" : pn.Substring(0,3);
 
    Console.WriteLine("q1 result – ");
    Console.WriteLine();
    foreach (var group in q1)
    {
        // works as expected. All nulls grouped in "(null)"
        Console.WriteLine("Group key: {0}", group.Key);
        foreach (var part in group)
            Console.WriteLine(" – {0}", part);
    }
 
    // Beware – nulls in source data can break your group
    // expression. This grouping will fail.
    var q2 = from pn in partNumbers
             group pn by pn.Substring(0, 3);
 
    Console.WriteLine();
    Console.WriteLine("q2 result – Not handling nulls –");
    Console.WriteLine();
    foreach (var group in q2)
    {
        // fails with null exception!
    }
 
}
 

Console output (Execution time: 19ms): [Hide/Show]


Top



Edit

Listing 4-3 : Grouping by multiple values

This sample demonstrates how to use anonymous types to group by more than one value.

public void Listing_4_3_Grouping_Multiple_Values()
{
    /* This sample uses the same data as we saw in Table 2–2, 
       but i've added 2 Gottshall's (one from the same state 
       and another out of that state), and two Gauwain's –
 
            Firstname    Lastname   State
            –––––––––––––––––––––––––––––
            Barney      Gottshall   CA
            Mandy       Gottshall   CA
            Bernadette  Gottshall   WA
            Armando     Valdes      WA
            Adam        Gauwain     AK
            Chris       Gauwain     AK
            Anthony     Gauwain     CA
            Jeffery     Deane       CA
            Collin      Zeeman      FL
            Stewart     Kagel       WA
            Chance      Lard        WA
            Blaine      Reifsteck   TX
            Mack        Kamph       TX
            Ariel       Hazelgrove  OR
    */
 
    var q = from c in Contact.SampleData()
            group c by new { c.LastName, c.State };
 
    foreach (var grp in q)
    {
        Console.WriteLine("Group – {0}, {1} – count = {2}", 
            grp.Key.LastName, 
            grp.Key.State,
            grp.Count());
    }
}
 
public class Contact
{
    public string FirstName { get; set; }
    public string LastName { get; set; }
    public string Email { get; set; }
    public string Phone { get; set; }
    public string Extension { get; set; }
    public DateTime DateOfBirth { get; set; }
    public string State { get; set; }
 
    public static List<Contact> SampleData()
    {
        return new List<Contact> {
            new Contact {FirstName = "Barney",     LastName = "Gottshall",     DateOfBirth = new DateTime(1945,10,19), Phone = "885 983 8858", Extension = "", Email = "bgottshall@aspiring–technology.com", State = "CA" },
            new Contact {FirstName = "Mandy",      LastName = "Gottshall",     State = "CA" },
            new Contact {FirstName = "Bernadette", LastName = "Gottshall",     State = "WA" },
            new Contact {FirstName = "Armando",    LastName = "Valdes",        DateOfBirth = new DateTime(1973,12,09), Phone = "848 553 8487", Extension = "", Email = "val1@aspiring–technology.com", State = "WA" },
            new Contact {FirstName = "Adam",       LastName = "Gauwain",       DateOfBirth = new DateTime(1959,10,03), Phone = "115 999 1154", Extension = "", Email = "adamg@aspiring–technology.com", State = "AK" },
            new Contact {FirstName = "Chris",      LastName = "Gauwain",       State = "AK" },
            new Contact {FirstName = "Anthony",    LastName = "Gauwain",       State = "CA" },
            new Contact {FirstName = "Jeffery",    LastName = "Deane",         DateOfBirth = new DateTime(1950,12,16), Phone = "677 602 6774", Extension = "", Email = "jeff.deane@aspiring–technology.com", State = "CA" },
            new Contact {FirstName = "Collin",     LastName = "Zeeman",        DateOfBirth = new DateTime(1935,02,10), Phone = "603 303 6030", Extension = "", Email = "czeeman@aspiring–technology.com", State = "FL" },
            new Contact {FirstName = "Stewart",    LastName = "Kagel",         DateOfBirth = new DateTime(1950,02,20), Phone = "546 607 5462", Extension = "", Email = "kagels@aspiring–technology.com", State = "WA" },
            new Contact {FirstName = "Chance",     LastName = "Lard",          DateOfBirth = new DateTime(1951,10,21), Phone = "278 918 2789", Extension = "", Email = "lard@aspiring–technology.com", State = "WA" },
            new Contact {FirstName = "Blaine",     LastName = "Reifsteck",     DateOfBirth = new DateTime(1946,05,18), Phone = "715 920 7157", Extension = "", Email = "blaine@aspiring–technology.com", State = "TX" },
            new Contact {FirstName = "Mack",       LastName = "Kamph",         DateOfBirth = new DateTime(1977,09,17), Phone = "364 202 3644", Extension = "", Email = "mack.kamph@aspiring–technology.com", State = "TX" },
            new Contact {FirstName = "Ariel",      LastName = "Hazelgrove",    DateOfBirth = new DateTime(1922,05,23), Phone = "165 737 1656", Extension = "", Email = "arielh@aspiring–technology.com", State = "OR" }
        };
 
    }
}
 

Console output (Execution time: 9ms): [Hide/Show]


Top



Edit

Listing 4-4 : Grouping by multiple values

This sample demonstrates how to use a concrete to group by more than one value.

public void Listing_4_4_Grouping_Multiple_Values()
{
    var q = from c in Contact.SampleData()
            group c by new LastNameState { 
                LastName = c.LastName, State = c.State };
 
    foreach (var grp in q)
    {
        Console.WriteLine("Group – {0}, {1} – count = {2}",
            grp.Key.LastName,
            grp.Key.State,
            grp.Count());
    }
}
 
public class Contact
{
    public string FirstName { get; set; }
    public string LastName { get; set; }
    public string Email { get; set; }
    public string Phone { get; set; }
    public string Extension { get; set; }
    public DateTime DateOfBirth { get; set; }
    public string State { get; set; }
 
    public static List<Contact> SampleData()
    {
        return new List<Contact> {
            new Contact {FirstName = "Barney",     LastName = "Gottshall",     DateOfBirth = new DateTime(1945,10,19), Phone = "885 983 8858", Extension = "", Email = "bgottshall@aspiring–technology.com", State = "CA" },
            new Contact {FirstName = "Mandy",      LastName = "Gottshall",     State = "CA" },
            new Contact {FirstName = "Bernadette", LastName = "Gottshall",     State = "WA" },
            new Contact {FirstName = "Armando",    LastName = "Valdes",        DateOfBirth = new DateTime(1973,12,09), Phone = "848 553 8487", Extension = "", Email = "val1@aspiring–technology.com", State = "WA" },
            new Contact {FirstName = "Adam",       LastName = "Gauwain",       DateOfBirth = new DateTime(1959,10,03), Phone = "115 999 1154", Extension = "", Email = "adamg@aspiring–technology.com", State = "AK" },
            new Contact {FirstName = "Chris",      LastName = "Gauwain",       State = "AK" },
            new Contact {FirstName = "Anthony",    LastName = "Gauwain",       State = "CA" },
            new Contact {FirstName = "Jeffery",    LastName = "Deane",         DateOfBirth = new DateTime(1950,12,16), Phone = "677 602 6774", Extension = "", Email = "jeff.deane@aspiring–technology.com", State = "CA" },
            new Contact {FirstName = "Collin",     LastName = "Zeeman",        DateOfBirth = new DateTime(1935,02,10), Phone = "603 303 6030", Extension = "", Email = "czeeman@aspiring–technology.com", State = "FL" },
            new Contact {FirstName = "Stewart",    LastName = "Kagel",         DateOfBirth = new DateTime(1950,02,20), Phone = "546 607 5462", Extension = "", Email = "kagels@aspiring–technology.com", State = "WA" },
            new Contact {FirstName = "Chance",     LastName = "Lard",          DateOfBirth = new DateTime(1951,10,21), Phone = "278 918 2789", Extension = "", Email = "lard@aspiring–technology.com", State = "WA" },
            new Contact {FirstName = "Blaine",     LastName = "Reifsteck",     DateOfBirth = new DateTime(1946,05,18), Phone = "715 920 7157", Extension = "", Email = "blaine@aspiring–technology.com", State = "TX" },
            new Contact {FirstName = "Mack",       LastName = "Kamph",         DateOfBirth = new DateTime(1977,09,17), Phone = "364 202 3644", Extension = "", Email = "mack.kamph@aspiring–technology.com", State = "TX" },
            new Contact {FirstName = "Ariel",      LastName = "Hazelgrove",    DateOfBirth = new DateTime(1922,05,23), Phone = "165 737 1656", Extension = "", Email = "arielh@aspiring–technology.com", State = "OR" }
        };
 
    }
}
 
public class LastNameState
{
    public string LastName { get; set; }
    public string State { get; set; }
 
    // follow the MSDN guidelines –
    // http://msdn.microsoft.com/en–us/library/
    //                     ms173147(VS.80).aspx
    public override bool Equals(object obj)
    {
        if (this != obj)
        {
            LastNameState item = obj as LastNameState;
            if (item == null) return false;
            if (State != item.State) return false;
            if (LastName != item.LastName) return false;
        }
 
        return true;
    }
 
    // follow the MSDN guidelines –
    // http://msdn.microsoft.com/en–us/library/
    //           system.object.gethashcode.aspx
    public override int GetHashCode()
    {
        int result = State != null ? State.GetHashCode() : 1;
        result = result ^ 
              (LastName != null ? LastName.GetHashCode() : 2);
 
        return result;
    }
}
 

Console output (Execution time: 6ms): [Hide/Show]


Top



Edit

Listing 4-5 : Soundex Custom Equality Comparer

This sample demonstrates how to construct a custom equality comparer. The sample builds a Soundex comparer.

public void Listing_4_5_Soundex()
{
    // example just to show the code for the Soundex Equality Comparer.
}
 
public class SoundexEqualityComparer
    : IEqualityComparer<string>
{
    public bool Equals(string x, string y)
    {
        return GetHashCode(x) == GetHashCode(y);
    }
 
    public int GetHashCode(string obj)
    {
        // E.g. convert soundex code A123,
        // to an integer: 65123
        int result = 0;
 
        string s = soundex(obj);
        if (string.IsNullOrEmpty(s) == false)
            result = Convert.ToInt32(s[0]) * 1000 +
                     Convert.ToInt32(s.Substring(1, 3));
 
        return result;
    }
 
    private string soundex(string s)
    {
        // Algorithm as listed on 
        //     http://en.wikipedia.org/wiki/Soundex.
        // builds a string code in the format: 
        //     A–Z0–60–60–6
        // based on the phonetic sound of the input.
 
        if (String.IsNullOrEmpty(s))
            return null;
        
        StringBuilder result =
            new StringBuilder();
 
         string source = s.ToUpper().Replace(" ", "");
 
        // add the first character, then loop the
        // string mapping as we go
        result.Append(source[0]);
        char previous = '0';
 
        for (int i = 1; i < source.Length; i++)
        {
            // map to the soundex numeral
            char mappedTo = '0';
            char thisChar = source[i];
            
            if ("BFPV".Contains(thisChar))
                mappedTo = '1';
            else if ("CGJKQSXZ".Contains(thisChar))
                mappedTo = '2';
            else if ("DT".Contains(thisChar))
                mappedTo = '3';
            else if ('L' == thisChar)
                mappedTo = '4';
            else if ("MN".Contains(thisChar))
                mappedTo = '5';
            else if ('R' == thisChar)
                mappedTo = '6';
 
            // ignore adjacent duplicates and 
            // non–matched characters
            if (mappedTo != previous && mappedTo != '0')
            {
                result.Append(mappedTo);
                previous = mappedTo;
            }
        }
 
        while (result.Length < 4) 
            result.Append("0");
 
        return result.ToString(0, 4);
    }
}
 



Top



Edit

Listing 4-6 : Custom Equality Comparer

This sample demonstrates how to use a custom equality comparer.

public void Listing_4_6_Cuatom_Equality_Comparer()
{
    string[] names = new string[] { "Janet", "Janette", "Joanne", 
        "Jo–anne", "Johanne", "Katy", "Katie", "Ralph", "Ralphe" };
 
    var q = names.GroupBy(s => s, 
        new SoundexEqualityComparer());
 
    foreach (var group in q)
    {
        Console.WriteLine(group.Key);
        foreach (var name in group)
            Console.WriteLine(" – {0}", name);
    }
 
}
 
public class SoundexEqualityComparer
    : IEqualityComparer<string>
{
    public bool Equals(string x, string y)
    {
        return GetHashCode(x) == GetHashCode(y);
    }
 
    public int GetHashCode(string obj)
    {
        // E.g. convert soundex code A123,
        // to an integer: 65123
        int result = 0;
 
        string s = soundex(obj);
        if (string.IsNullOrEmpty(s) == false)
            result = Convert.ToInt32(s[0]) * 1000 +
                     Convert.ToInt32(s.Substring(1, 3));
 
        return result;
    }
 
    private string soundex(string s)
    {
        // Algorithm as listed on 
        //     http://en.wikipedia.org/wiki/Soundex.
        // builds a string code in the format: 
        //     A–Z0–60–60–6
        // based on the phonetic sound of the input.
 
        if (String.IsNullOrEmpty(s))
            return null;
        
        StringBuilder result =
            new StringBuilder();
 
         string source = s.ToUpper().Replace(" ", "");
 
        // add the first character, then loop the
        // string mapping as we go
        result.Append(source[0]);
        char previous = '0';
 
        for (int i = 1; i < source.Length; i++)
        {
            // map to the soundex numeral
            char mappedTo = '0';
            char thisChar = source[i];
            
            if ("BFPV".Contains(thisChar))
                mappedTo = '1';
            else if ("CGJKQSXZ".Contains(thisChar))
                mappedTo = '2';
            else if ("DT".Contains(thisChar))
                mappedTo = '3';
            else if ('L' == thisChar)
                mappedTo = '4';
            else if ("MN".Contains(thisChar))
                mappedTo = '5';
            else if ('R' == thisChar)
                mappedTo = '6';
 
            // ignore adjacent duplicates and 
            // non–matched characters
            if (mappedTo != previous && mappedTo != '0')
            {
                result.Append(mappedTo);
                previous = mappedTo;
            }
        }
 
        while (result.Length < 4) 
            result.Append("0");
 
        return result.ToString(0, 4);
    }
}
 

Console output (Execution time: 8ms): [Hide/Show]


Top



Edit

Listing 4-7 : Custom element projection into group.

This sample demonstrates how to change the element projection into each grouping.

public void Listing_4_7_Cuatom_Element_Projection()
{
    IList<Contact> contacts = Contact.SampleData();
 
    var q = contacts.GroupBy(
                c => c.State, 
                c => c.FirstName + " " + c.LastName);
 
    foreach (var group in q)
    {
        Console.WriteLine("State: {0}", group.Key);
        foreach (string name in group)
            Console.WriteLine("  {0}", name);
    }
}
 

Console output (Execution time: 2ms): [Hide/Show]


Top



Edit

Listing 4-8 : Custom element projection into group.

This sample demonstrates how to change the element projection into each grouping.

public void Listing_4_8_Custom_Element_Projection_AnonymousType()
{
    List<Contact> contacts = Contact.SampleData();
 
    var q = contacts.GroupBy(
                c => c.State ?? "state unknown",
                c => new
                {
                    Title = c.FirstName + " " + c.LastName,
                    Email = c.Email ?? "email address unknown"
                });
 
    foreach (var group in q)
    {
        Console.WriteLine("State: {0}", group.Key);
        foreach (var element in group)
            Console.WriteLine("  {0} ({1})", 
                element.Title, 
                element.Email);
    }
}
 
public class Contact
{
    public string FirstName { get; set; }
    public string LastName { get; set; }
    public string Email { get; set; }
    public string Phone { get; set; }
    public string Extension { get; set; }
    public DateTime DateOfBirth { get; set; }
    public string State { get; set; }
 
    public static List<Contact> SampleData()
    {
        return new List<Contact> {
            new Contact {FirstName = "Barney",     LastName = "Gottshall",     DateOfBirth = new DateTime(1945,10,19), Phone = "885 983 8858", Extension = "", Email = "bgottshall@aspiring–technology.com", State = "CA" },
            new Contact {FirstName = "Mandy",      LastName = "Gottshall",     State = "CA" },
            new Contact {FirstName = "Bernadette", LastName = "Gottshall",     State = "WA" },
            new Contact {FirstName = "Armando",    LastName = "Valdes",        DateOfBirth = new DateTime(1973,12,09), Phone = "848 553 8487", Extension = "", Email = "val1@aspiring–technology.com", State = "WA" },
            new Contact {FirstName = "Adam",       LastName = "Gauwain",       DateOfBirth = new DateTime(1959,10,03), Phone = "115 999 1154", Extension = "", Email = "adamg@aspiring–technology.com", State = "AK" },
            new Contact {FirstName = "Chris",      LastName = "Gauwain",       State = "AK" },
            new Contact {FirstName = "Anthony",    LastName = "Gauwain",       State = "CA" },
            new Contact {FirstName = "Jeffery",    LastName = "Deane",         DateOfBirth = new DateTime(1950,12,16), Phone = "677 602 6774", Extension = "", Email = "jeff.deane@aspiring–technology.com", State = "CA" },
            new Contact {FirstName = "Collin",     LastName = "Zeeman",        DateOfBirth = new DateTime(1935,02,10), Phone = "603 303 6030", Extension = "", Email = "czeeman@aspiring–technology.com", State = "FL" },
            new Contact {FirstName = "Stewart",    LastName = "Kagel",         DateOfBirth = new DateTime(1950,02,20), Phone = "546 607 5462", Extension = "", Email = "kagels@aspiring–technology.com", State = "WA" },
            new Contact {FirstName = "Chance",     LastName = "Lard",          DateOfBirth = new DateTime(1951,10,21), Phone = "278 918 2789", Extension = "", Email = "lard@aspiring–technology.com", State = "WA" },
            new Contact {FirstName = "Blaine",     LastName = "Reifsteck",     DateOfBirth = new DateTime(1946,05,18), Phone = "715 920 7157", Extension = "", Email = "blaine@aspiring–technology.com", State = "TX" },
            new Contact {FirstName = "Mack",       LastName = "Kamph",         DateOfBirth = new DateTime(1977,09,17), Phone = "364 202 3644", Extension = "", Email = "mack.kamph@aspiring–technology.com", State = "TX" },
            new Contact {FirstName = "Ariel",      LastName = "Hazelgrove",    DateOfBirth = new DateTime(1922,05,23), Phone = "165 737 1656", Extension = "", Email = "arielh@aspiring–technology.com", State = "OR" }
        };
 
    }
}
 

Console output (Execution time: 6ms): [Hide/Show]


Top



Edit

Listing 4-9 : Grouping Query Continuation and Aggregation.

This sample demonstrates how to use grouping results in the same query and extract aggregate values.

public void Listing_4_9_Grouping_Query_Continuation()
{
    List<CallLog> calls = CallLog.SampleData();
 
    var q = from c in calls
            group c by c.Number into g
            select new
            {
                Number = g.Key,
                InCalls = g.Count(c => c.Incoming),
                OutCalls = g.Count(c => !c.Incoming),
                TotalTime = g.Sum(c => c.Duration),
                AvgTime = g.Average(c => c.Duration)
            };
 
    foreach (var number in q)
        Console.WriteLine(
            "{0} ({1} in, {2} out) Avg Time: {3} mins",
            number.Number,
            number.InCalls,
            number.OutCalls,
            Math.Round(number.AvgTime, 2));
}
 
public class CallLog
{
    public string Number { get; set; }
    public int Duration { get; set; }
    public bool Incoming { get; set; }
    public DateTime When { get; set; }
    public string Extension { get; set; }
 
    public static List<CallLog> SampleData()
    {
        return new List<CallLog> {
            new CallLog { Number = "885 983 8858", Extension = "", Duration = 2,  Incoming = true,  When = new DateTime(2006,	8,	7,	8,	12,	0)},
            new CallLog { Number = "165 737 1656", Extension = "", Duration = 15, Incoming = true,  When = new DateTime(2006,	8,	7,	9,	23,	0) },
            new CallLog { Number = "364 202 3644", Extension = "", Duration = 1,  Incoming = false, When = new DateTime(2006,	8,	7,	10,	5,	0) },
            new CallLog { Number = "603 303 6030", Extension = "", Duration = 2,  Incoming = false, When = new DateTime(2006,	8,	7,	10,	35,	0) },
            new CallLog { Number = "546 607 5462", Extension = "", Duration = 4,  Incoming = true,  When = new DateTime(2006,	8,	7,	11,	15,	0) },
            new CallLog { Number = "885 983 8858", Extension = "", Duration = 15, Incoming = false, When = new DateTime(2006,	8,	7,	13,	12,	0) },
            new CallLog { Number = "885 983 8858", Extension = "", Duration = 3,  Incoming = true,  When = new DateTime(2006,	8,	7,	13,	47,	0) },
            new CallLog { Number = "546 607 5462", Extension = "", Duration = 1,  Incoming = false, When = new DateTime(2006,	8,	7,	20,	34,	0) },
            new CallLog { Number = "546 607 5462", Extension = "", Duration = 3,  Incoming = false, When = new DateTime(2006,	8,	8,	10,	10,	0) },
            new CallLog { Number = "603 303 6030", Extension = "", Duration = 23, Incoming = false, When = new DateTime(2006,	8,	8,	10,	40,	0) },
            new CallLog { Number = "848 553 8487", Extension = "", Duration = 3,  Incoming = false, When = new DateTime(2006,	8,	8,	14,	0,	0) },
            new CallLog { Number = "848 553 8487", Extension = "", Duration = 7,  Incoming = true,  When = new DateTime(2006,	8,	8,	14,	37,	0) },
            new CallLog { Number = "278 918 2789", Extension = "", Duration = 6,  Incoming = true,  When = new DateTime(2006,	8,	8,	15,	23,	0) },
            new CallLog { Number = "364 202 3644", Extension = "", Duration = 20, Incoming = true,  When = new DateTime(2006,	8,	8,	17,	12,	0) },
            new CallLog { Number = "885 983 8858", Extension = "", Duration = 5,  Incoming = true,  When = new DateTime(2006,	7,	12,	8,	12,	0)},
            new CallLog { Number = "165 737 1656", Extension = "", Duration = 12, Incoming = true,  When = new DateTime(2006,	6,	14,	9,	23,	0) },
            new CallLog { Number = "364 202 3644", Extension = "", Duration = 10,  Incoming = false, When = new DateTime(2006,	7,	9,	10,	5,	0) },
            new CallLog { Number = "603 303 6030", Extension = "", Duration = 22,  Incoming = false, When = new DateTime(2006,	7,	5,	10,	35,	0) },
            new CallLog { Number = "546 607 5462", Extension = "", Duration = 9,  Incoming = true,  When = new DateTime(2006,	6,	7,	11,	15,	0) },
            new CallLog { Number = "885 983 8858", Extension = "", Duration = 10, Incoming = false, When = new DateTime(2006,	6,	7,	13,	12,	0) },
            new CallLog { Number = "885 983 8858", Extension = "", Duration = 21,  Incoming = true,  When = new DateTime(2006,	7,	7,	13,	47,	0) },
            new CallLog { Number = "546 607 5462", Extension = "",Duration = 7,  Incoming = false, When = new DateTime(2006,	7,	7,	20,	34,	0) },
            new CallLog { Number = "546 607 5462", Extension = "", Duration = 2,  Incoming = false, When = new DateTime(2006,	6,	8,	10,	10,	0) },
            new CallLog { Number = "603 303 6030", Extension = "", Duration = 3, Incoming = false, When = new DateTime(2006,	6,	8,	10,	40,	0) },
            new CallLog { Number = "848 553 8487", Extension = "", Duration = 32,  Incoming = false, When = new DateTime(2006,	7,	8,	14,	0,	0) },
            new CallLog { Number = "848 553 8487", Extension = "", Duration = 13,  Incoming = true,  When = new DateTime(2006,	7,	8,	14,	37,	0) },
            new CallLog { Number = "278 918 2789", Extension = "", Duration = 16,  Incoming = true,  When = new DateTime(2006,	5,	8,	15,	23,	0) },
            new CallLog { Number = "364 202 3644", Extension = "", Duration = 24, Incoming = true,  When = new DateTime(2006,	6,	8,	17,	12,	0) }
        };
    }
}
 

Console output (Execution time: 13ms): [Hide/Show]


Top



Edit

Listing 4-10 : Nested Grouping Query Continuation.

This sample demonstrates how to nest and aggregate grouped data.

public void Listing_4_10_Nested_Grouping_Query_Continuation()
{
    List<Contact> contacts = Contact.SampleData();
    List<CallLog> callLog = CallLog.SampleData();
 
    var q = from contact in contacts
            select new
            {
                Name = contact.FirstName + " " + 
                            contact.LastName,
                YearGroup = from call in callLog
                            where call.Number == contact.Phone
                            group call by call.When.Year 
                                into groupYear
                            select new
                            {
                                Year = groupYear.Key,
                                MonthGroup = 
                                   from c in groupYear
                                   group c by c.When.ToString("MMMM") 
                            }
            };
    
    foreach (var con in q)
    {
        Console.WriteLine("Customer: {0}", con.Name);
        foreach (var year in con.YearGroup)
        {
            Console.WriteLine(" Year:{0}", year.Year);
            foreach (var month in year.MonthGroup)
            {
                Console.WriteLine("    Month:{0}", month.Key);
                foreach (var call in month)
                {
                    Console.WriteLine("      {0} – for {1} minutes",
                                     call.When, call.Duration);
                }
            }
        }
    }
}
 
public class CallLog
{
    public string Number { get; set; }
    public int Duration { get; set; }
    public bool Incoming { get; set; }
    public DateTime When { get; set; }
    public string Extension { get; set; }
 
    public static List<CallLog> SampleData()
    {
        return new List<CallLog> {
            new CallLog { Number = "885 983 8858", Extension = "", Duration = 2,  Incoming = true,  When = new DateTime(2006,	8,	7,	8,	12,	0)},
            new CallLog { Number = "165 737 1656", Extension = "", Duration = 15, Incoming = true,  When = new DateTime(2006,	8,	7,	9,	23,	0) },
            new CallLog { Number = "364 202 3644", Extension = "", Duration = 1,  Incoming = false, When = new DateTime(2006,	8,	7,	10,	5,	0) },
            new CallLog { Number = "603 303 6030", Extension = "", Duration = 2,  Incoming = false, When = new DateTime(2006,	8,	7,	10,	35,	0) },
            new CallLog { Number = "546 607 5462", Extension = "", Duration = 4,  Incoming = true,  When = new DateTime(2006,	8,	7,	11,	15,	0) },
            new CallLog { Number = "885 983 8858", Extension = "", Duration = 15, Incoming = false, When = new DateTime(2006,	8,	7,	13,	12,	0) },
            new CallLog { Number = "885 983 8858", Extension = "", Duration = 3,  Incoming = true,  When = new DateTime(2006,	8,	7,	13,	47,	0) },
            new CallLog { Number = "546 607 5462", Extension = "", Duration = 1,  Incoming = false, When = new DateTime(2006,	8,	7,	20,	34,	0) },
            new CallLog { Number = "546 607 5462", Extension = "", Duration = 3,  Incoming = false, When = new DateTime(2006,	8,	8,	10,	10,	0) },
            new CallLog { Number = "603 303 6030", Extension = "", Duration = 23, Incoming = false, When = new DateTime(2006,	8,	8,	10,	40,	0) },
            new CallLog { Number = "848 553 8487", Extension = "", Duration = 3,  Incoming = false, When = new DateTime(2006,	8,	8,	14,	0,	0) },
            new CallLog { Number = "848 553 8487", Extension = "", Duration = 7,  Incoming = true,  When = new DateTime(2006,	8,	8,	14,	37,	0) },
            new CallLog { Number = "278 918 2789", Extension = "", Duration = 6,  Incoming = true,  When = new DateTime(2006,	8,	8,	15,	23,	0) },
            new CallLog { Number = "364 202 3644", Extension = "", Duration = 20, Incoming = true,  When = new DateTime(2006,	8,	8,	17,	12,	0) },
            new CallLog { Number = "885 983 8858", Extension = "", Duration = 5,  Incoming = true,  When = new DateTime(2006,	7,	12,	8,	12,	0)},
            new CallLog { Number = "165 737 1656", Extension = "", Duration = 12, Incoming = true,  When = new DateTime(2006,	6,	14,	9,	23,	0) },
            new CallLog { Number = "364 202 3644", Extension = "", Duration = 10,  Incoming = false, When = new DateTime(2006,	7,	9,	10,	5,	0) },
            new CallLog { Number = "603 303 6030", Extension = "", Duration = 22,  Incoming = false, When = new DateTime(2006,	7,	5,	10,	35,	0) },
            new CallLog { Number = "546 607 5462", Extension = "", Duration = 9,  Incoming = true,  When = new DateTime(2006,	6,	7,	11,	15,	0) },
            new CallLog { Number = "885 983 8858", Extension = "", Duration = 10, Incoming = false, When = new DateTime(2006,	6,	7,	13,	12,	0) },
            new CallLog { Number = "885 983 8858", Extension = "", Duration = 21,  Incoming = true,  When = new DateTime(2006,	7,	7,	13,	47,	0) },
            new CallLog { Number = "546 607 5462", Extension = "",Duration = 7,  Incoming = false, When = new DateTime(2006,	7,	7,	20,	34,	0) },
            new CallLog { Number = "546 607 5462", Extension = "", Duration = 2,  Incoming = false, When = new DateTime(2006,	6,	8,	10,	10,	0) },
            new CallLog { Number = "603 303 6030", Extension = "", Duration = 3, Incoming = false, When = new DateTime(2006,	6,	8,	10,	40,	0) },
            new CallLog { Number = "848 553 8487", Extension = "", Duration = 32,  Incoming = false, When = new DateTime(2006,	7,	8,	14,	0,	0) },
            new CallLog { Number = "848 553 8487", Extension = "", Duration = 13,  Incoming = true,  When = new DateTime(2006,	7,	8,	14,	37,	0) },
            new CallLog { Number = "278 918 2789", Extension = "", Duration = 16,  Incoming = true,  When = new DateTime(2006,	5,	8,	15,	23,	0) },
            new CallLog { Number = "364 202 3644", Extension = "", Duration = 24, Incoming = true,  When = new DateTime(2006,	6,	8,	17,	12,	0) }
        };
    }
}
 
public class Contact
{
    public string FirstName { get; set; }
    public string LastName { get; set; }
    public string Email { get; set; }
    public string Phone { get; set; }
    public string Extension { get; set; }
    public DateTime DateOfBirth { get; set; }
    public string State { get; set; }
 
    public static List<Contact> SampleData()
    {
        return new List<Contact> {
            new Contact {FirstName = "Barney",     LastName = "Gottshall",     DateOfBirth = new DateTime(1945,10,19), Phone = "885 983 8858", Extension = "", Email = "bgottshall@aspiring–technology.com", State = "CA" },
            new Contact {FirstName = "Mandy",      LastName = "Gottshall",     State = "CA" },
            new Contact {FirstName = "Bernadette", LastName = "Gottshall",     State = "WA" },
            new Contact {FirstName = "Armando",    LastName = "Valdes",        DateOfBirth = new DateTime(1973,12,09), Phone = "848 553 8487", Extension = "", Email = "val1@aspiring–technology.com", State = "WA" },
            new Contact {FirstName = "Adam",       LastName = "Gauwain",       DateOfBirth = new DateTime(1959,10,03), Phone = "115 999 1154", Extension = "", Email = "adamg@aspiring–technology.com", State = "AK" },
            new Contact {FirstName = "Chris",      LastName = "Gauwain",       State = "AK" },
            new Contact {FirstName = "Anthony",    LastName = "Gauwain",       State = "CA" },
            new Contact {FirstName = "Jeffery",    LastName = "Deane",         DateOfBirth = new DateTime(1950,12,16), Phone = "677 602 6774", Extension = "", Email = "jeff.deane@aspiring–technology.com", State = "CA" },
            new Contact {FirstName = "Collin",     LastName = "Zeeman",        DateOfBirth = new DateTime(1935,02,10), Phone = "603 303 6030", Extension = "", Email = "czeeman@aspiring–technology.com", State = "FL" },
            new Contact {FirstName = "Stewart",    LastName = "Kagel",         DateOfBirth = new DateTime(1950,02,20), Phone = "546 607 5462", Extension = "", Email = "kagels@aspiring–technology.com", State = "WA" },
            new Contact {FirstName = "Chance",     LastName = "Lard",          DateOfBirth = new DateTime(1951,10,21), Phone = "278 918 2789", Extension = "", Email = "lard@aspiring–technology.com", State = "WA" },
            new Contact {FirstName = "Blaine",     LastName = "Reifsteck",     DateOfBirth = new DateTime(1946,05,18), Phone = "715 920 7157", Extension = "", Email = "blaine@aspiring–technology.com", State = "TX" },
            new Contact {FirstName = "Mack",       LastName = "Kamph",         DateOfBirth = new DateTime(1977,09,17), Phone = "364 202 3644", Extension = "", Email = "mack.kamph@aspiring–technology.com", State = "TX" },
            new Contact {FirstName = "Ariel",      LastName = "Hazelgrove",    DateOfBirth = new DateTime(1922,05,23), Phone = "165 737 1656", Extension = "", Email = "arielh@aspiring–technology.com", State = "OR" }
        };
 
    }
}
 

Console output (Execution time: 16ms): [Hide/Show]


Top



Edit

Joining

Edit

Listing 4 : Cross-Join example.

This sample demonstrates a simple cross-join.

public void Listing_4_Cross_Join()
{
    var outer = Enumerable.Range(1, 3);
    var inner = Enumerable.Range(1, 3);
 
    // query Expression syntax
    var q = from x in outer
              from y in inner
              select new { x, y };
 
    foreach (var element in q)
        Console.WriteLine("{0}, {1}", element.x, element.y);
 
    // extension Method syntax 
    var q1 = outer
             .SelectMany(
                x => inner, // inner collection selector
                (x, y) => new { x, y } // select projection
              );
 
    Console.WriteLine();
 
    foreach (var element in q1)
        Console.WriteLine("{0}, {1}", element.x, element.y);
}
 

Console output (Execution time: 8ms): [Hide/Show]


Top



Edit

Listing 4-11 : Cross-Join binary sequence example.

This sample demonstrates a cross-join that build all combinations of a 4-bit binary sequence.

public void Listing_4_11_Cross_Join_BinaryExample()
{
    var binary = new int[] { 0, 1 };
 
    var q = from b4 in binary
            from b3 in binary
            from b2 in binary
            from b1 in binary
            select String.Format(
                "{0}{1}{2}{3}", b4, b3, b2, b1);
 
    foreach (var element in q)
        Console.WriteLine(element);
}
 

Console output (Execution time: 8ms): [Hide/Show]


Top



Edit

Listing 4 : Cross-Join bitmap sequence.

This sample demonstrates a cross-join that accesses each pixel in a bitmap.

public void Listing_4_Cross_Join_BitmapExample()
{
    Bitmap bm = new Bitmap(5,5);
 
    var q = from x in Enumerable.Range(0, bm.Width)
            from y in Enumerable.Range(0, bm.Height)
            select bm.GetPixel(x, y);
 
    foreach (var element in q)
        Console.WriteLine(element);
}
 

Console output (Execution time: 4ms): [Hide/Show]


Top



Edit

Listing 4 : Simple Join example.

This sample demonstrates a simple inner-join between two string arrays.

public void Listing_4_Simple_Join()
{
    string[] outer = new string[] { "a", "b", "c", "d" };
    string[] inner = new string[] { "b", "c", "d", "e" };
 
    var q = from s1 in outer
            join s2 in inner on s1 equals s2
            select string.Format(
                    "Outer: {0} Inner: {1}", s1, s2);
 
    foreach (string s in q)
        Console.WriteLine(s);
}
 

Console output (Execution time: 1ms): [Hide/Show]


Top



Edit

Listing 4-12 : One-one join using Join syntax.

This sample demonstrates a one-one join using the join Query Expression syntax.

public void Listing_4_12_One_to_One_JoinSyntax()
{
    // scenario: You have an order number, 
    // and need the customer details.
    var customers = Customer.SampleData();
    var orders = Order.SampleData();
 
    var q1 = from o in orders
             join c in customers on
                 o.CustomerID equals c.CustomerID
             select new
             {
                 o.OrderNumber,
                 c.LastName
             };
 
    foreach (var order in q1)
        Console.WriteLine(
            "Customer: {0}  Order Number: {1}",
            order.LastName.PadRight(9, ' '),
            order.OrderNumber);
}
 
public class Customer
{
    public string CustomerID { get; set; }
    public string LastName { get; set; }
 
    public static List<Customer> SampleData()
    {
        return new List<Customer> {
            new Customer { CustomerID = "GOT1", LastName = "Gottshall"},
            new Customer { CustomerID = "VAL1", LastName = "Valdes"},
            new Customer { CustomerID = "GAU1", LastName = "Gauwain"},
            new Customer { CustomerID = "DEA1", LastName = "Deane"},
            new Customer { CustomerID = "ZEE1", LastName = "Zeeman"}
        };
    }
}
 
public class Order
{
    public string CustomerID { get; set; }
    public string OrderNumber { get; set; }
 
 
    public static List<Order> SampleData()
    {
        return new List<Order> {
            new Order { CustomerID = "GOT1", OrderNumber = "Order 1"},
            new Order { CustomerID = "GOT1", OrderNumber = "Order 2"},
            new Order { CustomerID = "DEA1", OrderNumber = "Order 3"},
            new Order { CustomerID = "DEA1", OrderNumber = "Order 4"},
            new Order { CustomerID = "ZEE1", OrderNumber = "Order 5"}
        };
    }
}
 

Console output (Execution time: 4ms): [Hide/Show]


Top



Edit

Listing 4-13 : One-one outer-join using the Join syntax.

This sample demonstrates a one-one outer-join using the join Query Expression syntax.

public void Listing_4_13_One_to_One_OuterJoinSyntax()
{
    // scenario: You have an order number, 
    // and need the customer details.
    var customers = Customer.SampleData();
    var orders = Order.SampleData();
 
    var q = from c in customers
            join o in orders on
                c.CustomerID equals o.CustomerID into j
            from order in j.DefaultIfEmpty()
            select new
            {
                LastName = c.LastName,
                Order = order
            };
 
    foreach (var element in q)
        Console.WriteLine(
            "Customer: {0}  Order Number: {1}",
            element.LastName.PadRight(9, ' '),
            element.Order == null ?
                "(no orders)" : element.Order.OrderNumber);
 
 
    /* alternative, move null handling to select projection */
    var q1 = from c in customers
             join o in orders on
                c.CustomerID equals o.CustomerID into j
             from order in j.DefaultIfEmpty()
             select new
             {
                 LastName = c.LastName,
                 OrderNumber = order == null ?
                       "(no order)" : order.OrderNumber
             };
}
 
public class Customer
{
    public string CustomerID { get; set; }
    public string LastName { get; set; }
 
    public static List<Customer> SampleData()
    {
        return new List<Customer> {
            new Customer { CustomerID = "GOT1", LastName = "Gottshall"},
            new Customer { CustomerID = "VAL1", LastName = "Valdes"},
            new Customer { CustomerID = "GAU1", LastName = "Gauwain"},
            new Customer { CustomerID = "DEA1", LastName = "Deane"},
            new Customer { CustomerID = "ZEE1", LastName = "Zeeman"}
        };
    }
}
 
public class Order
{
    public string CustomerID { get; set; }
    public string OrderNumber { get; set; }
 
 
    public static List<Order> SampleData()
    {
        return new List<Order> {
            new Order { CustomerID = "GOT1", OrderNumber = "Order 1"},
            new Order { CustomerID = "GOT1", OrderNumber = "Order 2"},
            new Order { CustomerID = "DEA1", OrderNumber = "Order 3"},
            new Order { CustomerID = "DEA1", OrderNumber = "Order 4"},
            new Order { CustomerID = "ZEE1", OrderNumber = "Order 5"}
        };
    }
}
 

Console output (Execution time: 5ms): [Hide/Show]


Top



Edit

Listing 4 : Composite-key join example.

This sample demonstrates a composite-key join.

public void Listing_4_CompositeKey_Join()
{
    List<Contact> outer = Contact.SampleData();
    List<CallLog> inner = CallLog.SampleData();
 
    var q = from contact in outer
            join call in inner on
                new { 
                    phone = contact.Phone, 
                    contact.Extension 
                } 
            equals 
                new { 
                    phone = call.Number, 
                    call.Extension 
                }
            select new { call, contact };
 
    foreach (var e in q)
        Console.WriteLine(
            "{0}, {1}, {2}", 
            e.call.When, e.call.Number, e.contact.LastName);
}
 

Console output (Execution time: 4ms): [Hide/Show]


Top



Edit

Listing 4-14 : One-one join using Sub-Query syntax.

This sample demonstrates a one-one join using the Sub-Query syntax.

public void Listing_4_14_One_to_One_SubQuerySyntax()
{
    // scenario: You have an order number, 
    // and need the customer details.
    var customers = Customer.SampleData();
    var orders = Order.SampleData();
 
    var q2 = from o in orders
             select new
             {
                 OrderNumber = o.OrderNumber,
                 LastName = (from c in customers
                             where c.CustomerID == o.CustomerID
                             select c.LastName).SingleOrDefault()
             };
 
    foreach (var order in q2)
        Console.WriteLine(
            "Customer: {0}  Order Number: {1}",
            order.LastName.PadRight(9, ' '),
            order.OrderNumber);
}
 
public class Order
{
    public string CustomerID { get; set; }
    public string OrderNumber { get; set; }
 
 
    public static List<Order> SampleData()
    {
        return new List<Order> {
            new Order { CustomerID = "GOT1", OrderNumber = "Order 1"},
            new Order { CustomerID = "GOT1", OrderNumber = "Order 2"},
            new Order { CustomerID = "DEA1", OrderNumber = "Order 3"},
            new Order { CustomerID = "DEA1", OrderNumber = "Order 4"},
            new Order { CustomerID = "ZEE1", OrderNumber = "Order 5"}
        };
    }
}
 
public class Customer
{
    public string CustomerID { get; set; }
    public string LastName { get; set; }
 
    public static List<Customer> SampleData()
    {
        return new List<Customer> {
            new Customer { CustomerID = "GOT1", LastName = "Gottshall"},
            new Customer { CustomerID = "VAL1", LastName = "Valdes"},
            new Customer { CustomerID = "GAU1", LastName = "Gauwain"},
            new Customer { CustomerID = "DEA1", LastName = "Deane"},
            new Customer { CustomerID = "ZEE1", LastName = "Zeeman"}
        };
    }
}
 

Console output (Execution time: 2ms): [Hide/Show]


Top



Edit

Listing 4-15 : One-one join using SingleOrDefault syntax.

This sample demonstrates a one-one join using the SingleOrDefault syntax.

public void Listing_4_15_One_to_One_SingleOrDefaultSyntax()
{
    // scenario: You have an order number, 
    // and need the customer details.
    var customers = Customer.SampleData();
    var orders = Order.SampleData();
 
    var q3 = from o in orders
             let cust = customers
                        .SingleOrDefault(
                           c => c.CustomerID == o.CustomerID)
             select new
             {
                 OrderNumber = o.OrderNumber,
                 LastName = cust.LastName
             };
 
    foreach (var order in q3)
        Console.WriteLine(
            "Customer: {0}  Order Number: {1}",
            order.LastName.PadRight(9, ' '),
            order.OrderNumber);
}
 
public class Order
{
    public string CustomerID { get; set; }
    public string OrderNumber { get; set; }
 
 
    public static List<Order> SampleData()
    {
        return new List<Order> {
            new Order { CustomerID = "GOT1", OrderNumber = "Order 1"},
            new Order { CustomerID = "GOT1", OrderNumber = "Order 2"},
            new Order { CustomerID = "DEA1", OrderNumber = "Order 3"},
            new Order { CustomerID = "DEA1", OrderNumber = "Order 4"},
            new Order { CustomerID = "ZEE1", OrderNumber = "Order 5"}
        };
    }
}
 
public class Customer
{
    public string CustomerID { get; set; }
    public string LastName { get; set; }
 
    public static List<Customer> SampleData()
    {
        return new List<Customer> {
            new Customer { CustomerID = "GOT1", LastName = "Gottshall"},
            new Customer { CustomerID = "VAL1", LastName = "Valdes"},
            new Customer { CustomerID = "GAU1", LastName = "Gauwain"},
            new Customer { CustomerID = "DEA1", LastName = "Deane"},
            new Customer { CustomerID = "ZEE1", LastName = "Zeeman"}
        };
    }
}
 

Console output (Execution time: 2ms): [Hide/Show]


Top



Edit

Listing 4-16 : One-one join using cross-join/where syntax.

This sample demonstrates a one-one join using the Cross-join/where syntax.

public void Listing_4_16_One_to_One_CrossJoinSyntax()
{
    // scenario: You have an order number, 
    // and need the customer details.
    var customers = Customer.SampleData();
    var orders = Order.SampleData();
 
    var q4 = from o in orders
             from c in customers
             where c.CustomerID == o.CustomerID
             select new
             {
                 o.OrderNumber,
                 c.LastName
             };
 
    foreach (var order in q4)
        Console.WriteLine(
            "Customer: {0}  Order Number: {1}",
            order.LastName.PadRight(9, ' '),
            order.OrderNumber);
}
 
public class Order
{
    public string CustomerID { get; set; }
    public string OrderNumber { get; set; }
 
 
    public static List<Order> SampleData()
    {
        return new List<Order> {
            new Order { CustomerID = "GOT1", OrderNumber = "Order 1"},
            new Order { CustomerID = "GOT1", OrderNumber = "Order 2"},
            new Order { CustomerID = "DEA1", OrderNumber = "Order 3"},
            new Order { CustomerID = "DEA1", OrderNumber = "Order 4"},
            new Order { CustomerID = "ZEE1", OrderNumber = "Order 5"}
        };
    }
}
 
public class Customer
{
    public string CustomerID { get; set; }
    public string LastName { get; set; }
 
    public static List<Customer> SampleData()
    {
        return new List<Customer> {
            new Customer { CustomerID = "GOT1", LastName = "Gottshall"},
            new Customer { CustomerID = "VAL1", LastName = "Valdes"},
            new Customer { CustomerID = "GAU1", LastName = "Gauwain"},
            new Customer { CustomerID = "DEA1", LastName = "Deane"},
            new Customer { CustomerID = "ZEE1", LastName = "Zeeman"}
        };
    }
}
 

Console output (Execution time: 3ms): [Hide/Show]


Top



Edit

Listing 4-12,14,15,16 : One-one joins two collections.

This sample demonstrates the different ways to achieve one-one joins between two collections.

public void Listing_4_One_to_One_Comparisons()
{
    // comment out one of the two following lines for test
    //var loopOuter = 19; // 100 outer records
    var loopOuter = 9; // 50 records
    //var loopOuter = 0; // 5 outer records 
 
    var customers = Customer.SampleData();
    
    var orders = Order.SampleData();
    for (int i = 0; i < loopOuter; i++)
        orders = orders.Union(Order.SampleData()).ToList();
 
 
    // You have an order number, and need the customer details
 
    // join syntax
    // fastest when looking up all order records
    var q1 = from o in orders
             //where o.OrderNumber == "Order 3"
             join c in customers on 
                 o.CustomerID equals c.CustomerID
             select new
             {
                 o.OrderNumber,
                 c.LastName
             };
 
    // sub–query syntax in select clause
    // * not recommended *, use the SingleOrDefault operator
    var q2 = from o in orders
             //where o.OrderNumber == "Order 3"
             select new
             {
                OrderNumber = o.OrderNumber,
                LastName = (from c in customers
                            where c.CustomerID == o.CustomerID
                            select c.LastName).SingleOrDefault()
             };
 
    // sub–query syntax using SingleOrDefault operator
    // fastest when looking up a single order record
    var q3 = from o in orders
             //where o.OrderNumber == "Order 3"
             let cust = customers
                        .SingleOrDefault(
                           c => c.CustomerID == o.CustomerID)
             select new
             {
                 OrderNumber = o.OrderNumber,
                 LastName = cust.LastName
             };
    
    // cross–join / where syntax
    // * not recommended *, use the Join syntax or sub–query
    var q4 = from o in orders
             //where o.OrderNumber == "Order 3"
             from c in customers 
             where c.CustomerID == o.CustomerID 
             select new
             {
                o.OrderNumber,
                c.LastName
             };
 
    
    Console.WriteLine("q1 = {0}ms, q2 = {1}ms, q3 = {2}ms, q4 = {3}ms",
        MeasureTime(delegate { q1.ToList(); }, 1000000),
        MeasureTime(delegate { q2.ToList(); }, 1000000),
        MeasureTime(delegate { q3.ToList(); }, 1000000),
        MeasureTime(delegate { q4.ToList(); }, 1000000)
        );
     
 
    Console.WriteLine(Environment.NewLine + "Join syntax:" );
    foreach (var order in q1)
        Console.WriteLine(
            "Customer: {0}  Order Number: {1}",
            order.LastName.PadRight(9, ' '),
            order.OrderNumber);
 
    Console.WriteLine(Environment.NewLine + "Sub–query syntax:");
    foreach (var order in q2)
        Console.WriteLine(
            "Customer: {0}  Order Number: {1}",
            order.LastName.PadRight(9, ' '),
            order.OrderNumber);
 
    Console.WriteLine(Environment.NewLine + "Sub–query / SingleOrDefault syntax:");
    foreach (var order in q3)
        Console.WriteLine(
            "Customer: {0}  Order Number: {1}",
            order.LastName.PadRight(9, ' '),
            order.OrderNumber);
 
    Console.WriteLine(Environment.NewLine + "Cross–Join / where syntax:");
    foreach (var order in q4)
        Console.WriteLine(
            "Customer: {0}  Order Number: {1}",
            order.LastName.PadRight(9, ' '),
            order.OrderNumber);
 
}
 
public class Order
{
    public string CustomerID { get; set; }
    public string OrderNumber { get; set; }
 
 
    public static List<Order> SampleData()
    {
        return new List<Order> {
            new Order { CustomerID = "GOT1", OrderNumber = "Order 1"},
            new Order { CustomerID = "GOT1", OrderNumber = "Order 2"},
            new Order { CustomerID = "DEA1", OrderNumber = "Order 3"},
            new Order { CustomerID = "DEA1", OrderNumber = "Order 4"},
            new Order { CustomerID = "ZEE1", OrderNumber = "Order 5"}
        };
    }
}
 
public class Customer
{
    public string CustomerID { get; set; }
    public string LastName { get; set; }
 
    public static List<Customer> SampleData()
    {
        return new List<Customer> {
            new Customer { CustomerID = "GOT1", LastName = "Gottshall"},
            new Customer { CustomerID = "VAL1", LastName = "Valdes"},
            new Customer { CustomerID = "GAU1", LastName = "Gauwain"},
            new Customer { CustomerID = "DEA1", LastName = "Deane"},
            new Customer { CustomerID = "ZEE1", LastName = "Zeeman"}
        };
    }
}
 

Console output (Execution time: 115765ms): [Hide/Show]


Top



Edit

Listing 4-17 : One-many join using Join/Into syntax.

This sample demonstrates a one-many join using the join/into Query Expression syntax.

public void Listing_4_17_One_to_Many_JoinIntoSyntax()
{
    // Scenario: You have a customer, and need the orders 
    //           for that customer
    var customers = Customer.SampleData();
    var orders = Order.SampleData();
 
    var q1 = from c in customers
             join o in orders on 
                c.CustomerID equals o.CustomerID
             into cust_orders
             select new
             {
                 LastName = c.LastName,
                 Orders = cust_orders
             };
 
    foreach (var customer in q1)
    {
        Console.WriteLine("Last name: {0}", customer.LastName);
        foreach (var order in customer.Orders)
            Console.WriteLine(" – Order number: {0}", order.OrderNumber);
    }
 
 
    /* Extension method syntax */
    var q1a = customers
              .GroupJoin(
                   orders,
                   c => c.CustomerID,
                   o => o.CustomerID,
                   (c, o) => new
                   {
                       LastName = c.LastName,
                       Orders = o
                   }
              );
}
 
public class Order
{
    public string CustomerID { get; set; }
    public string OrderNumber { get; set; }
 
 
    public static List<Order> SampleData()
    {
        return new List<Order> {
            new Order { CustomerID = "GOT1", OrderNumber = "Order 1"},
            new Order { CustomerID = "GOT1", OrderNumber = "Order 2"},
            new Order { CustomerID = "DEA1", OrderNumber = "Order 3"},
            new Order { CustomerID = "DEA1", OrderNumber = "Order 4"},
            new Order { CustomerID = "ZEE1", OrderNumber = "Order 5"}
        };
    }
}
 
public class Customer
{
    public string CustomerID { get; set; }
    public string LastName { get; set; }
 
    public static List<Customer> SampleData()
    {
        return new List<Customer> {
            new Customer { CustomerID = "GOT1", LastName = "Gottshall"},
            new Customer { CustomerID = "VAL1", LastName = "Valdes"},
            new Customer { CustomerID = "GAU1", LastName = "Gauwain"},
            new Customer { CustomerID = "DEA1", LastName = "Deane"},
            new Customer { CustomerID = "ZEE1", LastName = "Zeeman"}
        };
    }
}
 

Console output (Execution time: 3ms): [Hide/Show]


Top



Edit

Listing 4-18 : One-many join using sub-query syntax.

This sample demonstrates a one-many join using the sub-query syntax.

public void Listing_4_18_One_to_Many_SubQuerySyntax()
{
    // Scenario: You have a customer, and need the orders 
    //           for that customer
    var customers = Customer.SampleData();
    var orders = Order.SampleData();
 
    var q2 = from c in customers
             select new
             {
                 LastName = c.LastName,
                 Orders = from o in orders
                          where o.CustomerID == c.CustomerID
                          select o
             };
 
    foreach (var customer in q2)
    {
        Console.WriteLine("Last name: {0}", customer.LastName);
        foreach (var order in customer.Orders)
            Console.WriteLine(" – Order number: {0}", order.OrderNumber);
    }
}
 
public class Order
{
    public string CustomerID { get; set; }
    public string OrderNumber { get; set; }
 
 
    public static List<Order> SampleData()
    {
        return new List<Order> {
            new Order { CustomerID = "GOT1", OrderNumber = "Order 1"},
            new Order { CustomerID = "GOT1", OrderNumber = "Order 2"},
            new Order { CustomerID = "DEA1", OrderNumber = "Order 3"},
            new Order { CustomerID = "DEA1", OrderNumber = "Order 4"},
            new Order { CustomerID = "ZEE1", OrderNumber = "Order 5"}
        };
    }
}
 
public class Customer
{
    public string CustomerID { get; set; }
    public string LastName { get; set; }
 
    public static List<Customer> SampleData()
    {
        return new List<Customer> {
            new Customer { CustomerID = "GOT1", LastName = "Gottshall"},
            new Customer { CustomerID = "VAL1", LastName = "Valdes"},
            new Customer { CustomerID = "GAU1", LastName = "Gauwain"},
            new Customer { CustomerID = "DEA1", LastName = "Deane"},
            new Customer { CustomerID = "ZEE1", LastName = "Zeeman"}
        };
    }
}
 

Console output (Execution time: 1ms): [Hide/Show]


Top



Edit

Listing 4-19 : One-many join using ToLookup operator syntax.

This sample demonstrates a one-many join using the ToLookup operator syntax.

public void Listing_4_19_One_to_Many_ToLookupSyntax()
{
    // Scenario: You have a customer, and need the orders 
    //           for that customer
    var customers = Customer.SampleData();
    var orders = Order.SampleData();
 
    // build a lookup list for the inner sequence
    var orders_lookup = orders.ToLookup(o => o.CustomerID);
 
    var q3 = from c in customers
             select new
             {
                 LastName = c.LastName,
                 Orders = orders_lookup[c.CustomerID]
             };
 
    foreach (var customer in q3)
    {
        Console.WriteLine("Last name: {0}", customer.LastName);
        foreach (var order in customer.Orders)
            Console.WriteLine(" – Order number: {0}", order.OrderNumber);
    }
}
 
public class Order
{
    public string CustomerID { get; set; }
    public string OrderNumber { get; set; }
 
 
    public static List<Order> SampleData()
    {
        return new List<Order> {
            new Order { CustomerID = "GOT1", OrderNumber = "Order 1"},
            new Order { CustomerID = "GOT1", OrderNumber = "Order 2"},
            new Order { CustomerID = "DEA1", OrderNumber = "Order 3"},
            new Order { CustomerID = "DEA1", OrderNumber = "Order 4"},
            new Order { CustomerID = "ZEE1", OrderNumber = "Order 5"}
        };
    }
}
 
public class Customer
{
    public string CustomerID { get; set; }
    public string LastName { get; set; }
 
    public static List<Customer> SampleData()
    {
        return new List<Customer> {
            new Customer { CustomerID = "GOT1", LastName = "Gottshall"},
            new Customer { CustomerID = "VAL1", LastName = "Valdes"},
            new Customer { CustomerID = "GAU1", LastName = "Gauwain"},
            new Customer { CustomerID = "DEA1", LastName = "Deane"},
            new Customer { CustomerID = "ZEE1", LastName = "Zeeman"}
        };
    }
}
 

Console output (Execution time: 18ms): [Hide/Show]


Top



Edit

Listing 4-17,18,19 : One-Many joins two collections.

This sample demonstrates the different ways to achieve one-many joins between two collections and compares their performance.

public void Listing_4_One_to_Many_Performance()
{
    // comment out one of the two following lines for test
    //var loopOuter = 19; // 100 outer records
    //var loopOuter = 9; // 50 records
    var loopOuter = 0; // 5 outer records 
 
    var customers = Customer.SampleData();
    for (int i = 0; i < loopOuter; i++)
        customers = customers.Union(Customer.SampleData()).ToList();
 
    var orders = Order.SampleData();
 
    // You have a customer, and need the orders for that customer
 
    // GroupJoin or "join into" syntax
    var q1 = from c in customers
             //where c.CustomerID == "DEA1"
             join o in orders on c.CustomerID equals o.CustomerID 
             into cust_orders
             select new
             {
                 LastName = c.LastName,
                 Orders = cust_orders
             };
 
    var q1a = customers
               //.Where(c => c.CustomerID == "DEA1")
               .GroupJoin(
                    orders,
                    c => c.CustomerID,
                    o => o.CustomerID,
                    (c, o) => new { 
                        LastName = c.LastName, 
                        Orders = o }
               );
   
 
    // inner join sample, exclude customers with no orders
    // q. why does this take 14 seconds????
 
    var q1b = from c in customers
             //where c.CustomerID == "DEA1"
             join o in orders on c.CustomerID equals o.CustomerID
             into cust_orders
             where cust_orders.Any()
             select new
             {
                 LastName = c.LastName,
                 Orders = cust_orders
             };
 
 
    // subquery syntax
    var q2 = from c in customers
             //where c.CustomerID == "DEA1"
             select new
             {
                 LastName = c.LastName,
                 Orders = from o in orders 
                          where o.CustomerID == c.CustomerID
                          select o
             };
 
    // ToLookup syntax
    var orders_lookup = orders.ToLookup(o => o.CustomerID);
 
    var q3 = from c in customers
             //where c.CustomerID == "DEA1"
             select new
             {
                 LastName = c.LastName,
                 Orders = orders_lookup[c.CustomerID]
             };
 
 
    var q1p = from c in customers
             //where c.CustomerID == "DEA1"
             join o in orders.AsParallel() on c.CustomerID equals o.CustomerID
             into cust_orders
             select new
             {
                 LastName = c.LastName,
                 Orders = cust_orders
             };
    
    Console.WriteLine("q1 = {0}ms, q2 = {1}ms, q3 = {2}ms, q1p = {3}ms",
        MeasureTime(delegate { q1.ToList(); }, 1000000),
        MeasureTime(delegate { q2.ToList(); }, 1000000),
        MeasureTime(delegate { q3.ToList(); }, 1000000),
        MeasureTime(delegate { q1p.ToList(); }, 1000000)
        );
 
 
    Console.WriteLine(Environment.NewLine + "Join syntax:");
    foreach (var customer in q1)
    {
        Console.WriteLine("Last name: {0}", customer.LastName);
        foreach (var order in customer.Orders)
            Console.WriteLine(" – Order number: {0}", order.OrderNumber);
    }
 
    Console.WriteLine(Environment.NewLine + "Subquery syntax:");
    foreach (var customer in q2)
    {
        Console.WriteLine("Last name: {0}", customer.LastName);
        foreach (var order in customer.Orders)
            Console.WriteLine(" – Order number: {0}", order.OrderNumber);
    }
 
    Console.WriteLine(Environment.NewLine + "ToLookup syntax:");
    foreach (var customer in q3)
    {
        Console.WriteLine("Last name: {0}", customer.LastName);
        foreach (var order in customer.Orders)
            Console.WriteLine(" – Order number: {0}", order.OrderNumber);
    }
 
 
    Console.WriteLine(Environment.NewLine + "Parallel syntax:");
    foreach (var customer in q1p)
    {
        Console.WriteLine("Last name: {0}", customer.LastName);
        foreach (var order in customer.Orders)
            Console.WriteLine(" – Order number: {0}", order.OrderNumber);
    }
}
 
public class Customer
{
    public string CustomerID { get; set; }
    public string LastName { get; set; }
 
    public static List<Customer> SampleData()
    {
        return new List<Customer> {
            new Customer { CustomerID = "GOT1", LastName = "Gottshall"},
            new Customer { CustomerID = "VAL1", LastName = "Valdes"},
            new Customer { CustomerID = "GAU1", LastName = "Gauwain"},
            new Customer { CustomerID = "DEA1", LastName = "Deane"},
            new Customer { CustomerID = "ZEE1", LastName = "Zeeman"}
        };
    }
}
 
public class Order
{
    public string CustomerID { get; set; }
    public string OrderNumber { get; set; }
 
 
    public static List<Order> SampleData()
    {
        return new List<Order> {
            new Order { CustomerID = "GOT1", OrderNumber = "Order 1"},
            new Order { CustomerID = "GOT1", OrderNumber = "Order 2"},
            new Order { CustomerID = "DEA1", OrderNumber = "Order 3"},
            new Order { CustomerID = "DEA1", OrderNumber = "Order 4"},
            new Order { CustomerID = "ZEE1", OrderNumber = "Order 5"}
        };
    }
}
 

Console output (Execution time: 7984ms): [Hide/Show]


Top



If you would like to comment on this page, click on the Discuss button located on the top-right of each page. Feel free to edit any mistakes or omissions you find. If you have an objection or find in-appropriate content then contact the administrator. This website is not affiliated with Microsoft®, all content and opinions are those of the specific author and some advice, solutions and article may contain unintentional errors - please use care. Powered by ScrewTurn Wiki version 2.0.33. Some of the icons created by FamFamFam.