Deborah's Developer MindScape

If you have a set of business objects that are related to another set of business objects by a foreign key, you can use the Join method in a Lambda expression to join the two sets of business objects into one. You can then use the resulting set as a single list. This is useful when you need to perform operations on a single  list, like for binding.

Say you have a Customer class and a Contact class. The Customer class defines your set of customers. The Contact class tracks the contacts (email, text, or phone messages) from those customers. So the Contact class has a CustomerId foreign key that maps the message to the associated customer.

Here is the Customer class.

In C#:

public class Customer
{
    public int CustomerId { get; set; }
    public string LastName { get; set; }
    public string FirstName { get; set; }
    public string EmailAddress { get; set; }
}

In VB 10 (VS 2010):

Public Class Customer
    Public Property CustomerId As Integer
    Public Property FirstName() As String
    Public Property LastName() As String
    Public Property EmailAddress() As String
End Class

In VB 9 (VS 2008):

Public Class Customer

    Private _CustomerId As Integer
    Public Property CustomerId() As Integer
        Get
            Return _CustomerId
        End Get
        Set(ByVal value As Integer)
            _CustomerId = value
        End Set
    End Property

    Private _FirstName As String
    Public Property FirstName() As String
        Get
            Return _FirstName
        End Get
        Set(ByVal value As String)
            _FirstName = value
        End Set
    End Property

    Private _LastName As String   
    Public Property LastName() As String
        Get
            Return _LastName
        End Get
        Set(ByVal value As String)
            _LastName = value
        End Set
    End Property

    Private _EmailAddress As String
    Public Property EmailAddress () As String
        Get
            Return _EmailAddress
        End Get
        Set(ByVal value As String)
            _EmailAddress = value
        End Set
    End Property
End Class

And the Contact class:

In C#:

public class Contact
{
    public int ContactId { get; set; }
    public int CustomerId { get; set; }
    public string MessageText { get; set; }
}

In VB 10 (VS 2010):

Public Class Contact
    Public Property ContactId As Integer
    Public Property CustomerId() As Integer
    Public Property MessageText() As String
End Class

In VB 9 (VS 2008):

Public Class Contact
    Private _ContactId As Integer
    Public Property ContactId() As Integer
        Get
            Return _ContactId
        End Get
        Set(ByVal value As Integer)
            _ContactId = value
        End Set
    End Property
    Private _CustomerId As Integer
    Public Property CustomerId() As Integer
        Get
            Return _CustomerId
        End Get
        Set(ByVal value As Integer)
            _CustomerId = value
        End Set
    End Property
    Private _MessageText As String
    Public Property MessageText() As String
        Get
            Return _MessageText
        End Get
        Set(ByVal value As String)
            _MessageText = value
        End Set
    End Property
End Class

Then you populate the list of business objects, probably from a table. In this example the lists are hard-coded so you don’t have to hook up a database.

In C#:

List<Customer> custList = new List<Customer>
                    {new Customer()
                          { CustomerId = 1,
                            FirstName="Bilbo",
                            LastName = "Baggins",
                            EmailAddress = "bb@hob.me"},
                    new Customer()
                          { CustomerId = 2,
                            FirstName="Frodo",
                            LastName = "Baggins",
                            EmailAddress = "fb@hob.me"},
                    new Customer()
                          { CustomerId = 3,
                            FirstName="Samwise",
                            LastName = "Gamgee",
                            EmailAddress = "sg@hob.me"},
                    new Customer()
                          { CustomerId = 4,
                            FirstName="Rosie",
                            LastName = "Cotton",
                            EmailAddress = "rc@hob.me"}};

List<Contact> contactList = new List<Contact>
        {new Contact()
              { ContactId = 1,
                CustomerId = 1, 
     MessageText="Please provide me with the status of my order."},
         new Contact()
              { ContactId = 2,
                CustomerId = 1,
                MessageText="Can I get the order by Friday?"},
         new Contact()
              { ContactId = 3,
                CustomerId = 2,
                MessageText="Follow up on Order # 2355"}};

In VB:

Dim custList As New List(Of Customer)
custList.Add(New Customer With {.CustomerId = 1, _
                                .LastName = "Baggins", _
                                .FirstName = "Bilbo", _
                                .EmailAddress="bb@hob.me"})
custList.Add(New Customer With {.CustomerId = 2, _
                                .LastName = "Baggins", _
                                .FirstName = "Frodo", _
                                .EmailAddress = "fb@hob.me"})
custList.Add(New Customer With {.CustomerId = 3, _
                                .LastName = "Gamgee", _
                                .FirstName = "Samwise", _
                                .EmailAddress = "sg@hob.me"})
custList.Add(New Customer With {.CustomerId = 4, _
                                .LastName = "Cotton", _
                                .FirstName = "Rosie", _
                                .EmailAddress = "rc@hob.me"})

Dim contactList As New List(Of Contact)
contactList.Add(New Contact With _
                  {.ContactId = 1, _
                    .CustomerId = 1, _
   .MessageText = "Please provide me with the status of my order."})
contactList.Add(New Contact With _
                  {.ContactId = 2, _
                    .CustomerId = 1, _
                    .MessageText = "Can I get the order by Friday?"})
contactList.Add(New Contact With _
                  {.ContactId = 3, _
                    .CustomerId = 2, _
                    .MessageText = "Follow up on Order # 2355"})

To perform the join, use the Join method from the Enumerable class:

In C#:

var contactJoin = contactList.Join(custList,
                    msg => msg.CustomerId,
                    cust => cust.CustomerId,
                    (msg, cust ) =>
                        new {LastName = cust.LastName,
                            FirstName = cust.FirstName,
                            Message = msg.MessageText });

In VB:

Dim contactJoin = contactList.Join(custList, _
               Function(msg) msg.CustomerId, _
               Function(cust) cust.CustomerId, _
               Function(msg, cust) _
                    New With {.LastName = cust.LastName, _
                        .FirstName = cust.FirstName, _
                        .Message = msg.MessageText})

In this example, the desired result is a list of contact messages but with the CustomerId replaced with the first and last name of the customer.

Notice in the above code  that the Join method is used on the contactList. This ensures that all contact messages are in the list along with their matching customers. This is called the outer list.

The first parameter to the Join method is the inner list. This is the list joined to the first (outer) list. In this case, it is custList to join the appropriate customers to their contact messages.

The joining is done using keys. So the next two parameters define the two keys. In this example, the name of the keys are the same: CustomerId.

So, the second parameter of the Join method defines the outer list’s key. In this case it is the key in Contact class that is used for the join.

The third parameter of the Join method defines the inner list’s key. In this example, it is the key in the Customer class that is used for the join.

The fourth parameter defines the values to return. In this example, the code is creating an anonymous type. The anonymous type has the contact message along with the customer’s last and first name.

You can then bind the result to a DataGridView or other control as follows.

In C#:

CustomersDataGridView.DataSource = contactJoin.ToList();

In VB:

CustomersDataGridView.DataSource = contactJoin.ToList()

And the result appears as shown below.

Use this technique any time you want to join two related lists.

Enjoy!

EDITED 5/2/2010: Added the VB 10 code to demonstrate the new auto-implemented properties in VB 10.