Deborah's Developer MindScape






         Tips and Techniques for Web and .NET developers.

March 3, 2010

Lambda Expressions: Join

Filed under: C#,Lambda Expressions,VB.NET @ 12:24 am

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.

image

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.

9 Comments

  1.   Thiago Alves — March 4, 2010 @ 8:36 pm    Reply

    Excellent post. But it also shows how VB.NET is verbose!

  2.   DeborahK — March 5, 2010 @ 10:27 am    Reply

    Hi Thiago –

    Thanks for posting. VB is *so* much more verbose here because C# has automatic properties and VB 9 does not.

    The GOOD NEWS is that VB is getting automatic properties in VB 10 (VS 2010) so the property statements will be just as short in VB 10 as in the C# code!

  3.   Ax Plains — March 11, 2010 @ 2:45 am    Reply

    Hello, maybe a little off topic but I have a question:
    is not LINQ designed to avoid creating business objects in code?

    I mean, instead of creating the “Customer” and “Contact” classes in code, the ORM designer should create them (from a a SQL Server database).
    So what is the need of creating “business objects”?
    More in general, how should a project be approached from the start if you plan to use Linq to SQL in WinForms? Should business objects (classes) be retained or disposed?

    (Ok, that was more than one question, but I hope it makes sense…)

    Thanks a lot in advance.

  4.   Travis — April 30, 2010 @ 4:20 pm    Reply

    Holy crap! I’m glad I went the c# route instead of VB

  5.   DeborahK — May 2, 2010 @ 1:59 pm    Reply

    Hi Travis –
    With VS 2010, VB is now as concise as C#.

  6.   Hal Martin — August 10, 2010 @ 9:29 pm    Reply

    Thanks! First really lucid description about how joins work when using the Join() extension method and lambda expressions.

  7.   DeborahK — August 11, 2010 @ 9:50 am    Reply

    Hi Ax –

    Linq is “Lanaguage Integrated Query” and is not an ORM.

    However, Linq is used by ORM-type features in Visual Studio such as Linq to Entities (Entity Framework) and Linq to SQL.

    This example uses Linq to Objects and expects that you already have created business objects that you want to work with.

    Entity Framework is Microsoft’s current recommended approach for building business objects from your database. However, I don’t use it. I have code already that automatically populates business objects from the database.

    Hope this helps.

  8.   Reinaldo — July 8, 2011 @ 7:20 am    Reply

    Hi, I need to convert the query to lambda, but I don’t know.
    This’s the query:
    Dim query = From c In Me.ObjectContext.Clientes
    Join uc In Me.ObjectContext.UsuarioCliente On c.idCompania Equals uc.idCompania And c.idCliente Equals uc.idCliente
    Where c.idCompania = UsuarioActual.Compania.idCompania And uc.Usuario = UsuarioActual.Usuario.Usuario
    Select c

    Please, help me. thanks!!
    My email: r_porto1.1@hotmail.com

  9.   Craig Burkett — September 30, 2011 @ 11:28 am    Reply

    Here is an alternative way in VB to assign a list of objects without having to call the custList’s Add method multiple times (works in VB9 or VB10). It kind of helps reduce the verbosity of VB while being explicit of intent.

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

    Function NewArray(Of T)(ByVal ParamArray values() As T) As T()
    Return values
    End Function

RSS feed for comments on this post. TrackBack URI

Leave a comment

*

© 2014 Deborah's Developer MindScape   Provided by WPMU DEV -The WordPress Experts   Hosted by Microsoft MVPs