Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

LINQ Join 2 List<T>s

Tags:

c#

linq

Preface: I don't understand what this does:

o => o.ID, i => i.ID, (o, id) => o

So go easy on me. :-)


I have 2 lists that I need to join together:

// list1 contains ALL contacts for a customer.
// Each item has a unique ID.
// There are no duplicates.
ContactCollection list1 = myCustomer.GetContacts();

// list2 contains the customer contacts (in list1) relevant to a REPORT
// the items in this list may have properties that differ from those in list1.
/*****/// e.g.:
/*****/        bool SelectedForNotification; 
/*****///  may be different.
ContactCollection list2 = myReport.GetContacts();

I need to create a third ContactCollection that contains all of the contacts in list1 but with the properties of the items in list2, if the item is in the list[2] (list3.Count == list1.Count).


I need to replace all items in list1 with the items in list2 where items in list1 have the IDs of the items in list2. The resulting list (list3) should contain the same number of items at list1.

I feel as though I'm not making any sense. So, please ask questions in the comments and I'll try to clarify.

like image 276
David Murdoch Avatar asked Apr 27 '10 18:04

David Murdoch


3 Answers

Joins are not so difficult, but your problem could probably use some further explanation.

To join two lists, you could do something like

var joined = from Item1 in list1
             join Item2 in list2
             on Item1.Id equals Item2.Id // join on some property
             select new { Item1, Item2 };

this will give an IEnumerable<'a>, where 'a is an anonymous type holding an item from list1 and its related item from list2. You could then choose which objects' properties to use as needed.

To get the result to a concrete list, all that is needed is a call to .ToList(). You can do that like

var list3 = joined.ToList();
// or
var list3 = (from Item1 in list1
             join Item2 in list2
             on Item1.Id equals Item2.Id // join on some property
             select new { Item1, Item2 }).ToList();

To do a left join to select all elements from list1 even without a match in list2, you can do something like this

var list3 = (from Item1 in list1
             join Item2 in list2
             on Item1.Id equals Item2.Id // join on some property
             into grouping
             from Item2 in grouping.DefaultIfEmpty()
             select new { Item1, Item2 }).ToList();

This will give you a list where Item1 equals the item from the first list and Item2 will either equal the matching item from the second list or the default, which will be null for a reference type.

like image 96
Anthony Pegram Avatar answered Nov 13 '22 06:11

Anthony Pegram


Here is what I came up with (based on this):

List<Contact> list3 = (from item1 in list1
    join item2 in list2
    on item1.ContactID equals item2.ContactID into g
    from o in g.DefaultIfEmpty()
    select o == null ? item1 :o).ToList<Contact>();

My favorite part is the big nosed smiley

:o)

Thanks for your help!

like image 33
David Murdoch Avatar answered Nov 13 '22 08:11

David Murdoch


Here is a DotNetFiddle with a Linq Group Join

using System;
using System.Collections;
using System.Collections.Generic;
using System.Linq;

class Order 
{
    public int Id;
    public string Name;
    public Order(int id, string name)
    {
        this.Id = id;
        this.Name = name;
    }   
}
class OrderItem
{
    public int Id;
    public string Name;
    public int OrderId;
    public OrderItem(int id, string name, int orderId)
    {
        this.Id = id;
        this.Name = name;
        this.OrderId = orderId;
    }
}
List<Order> orders = new List<Order>() 
{
    new Order(1, "one"), 
    new Order(2, "two") 
};
List<OrderItem> orderItems = new List<OrderItem>()
{
    new OrderItem(1, "itemOne", 1),
    new OrderItem(2, "itemTwo", 1), 
    new OrderItem(3, "itemThree", 1), 
    new OrderItem(4, "itemFour", 2), 
    new OrderItem(5, "itemFive", 2)
};
var joined = 
    from o in orders
    join oi in orderItems
    on o.Id equals oi.OrderId into gj // gj means group join and is a collection OrderItem
    select new { o, gj };

// this is just to write the results to the console
string columns = "{0,-20} {1, -20}";
Console.WriteLine(string.Format(columns, "Order", "Item Count"));
foreach(var j in joined)
{
    Console.WriteLine(columns, j.o.Name, j.gj.Count() );
}
like image 4
Shaun Luttin Avatar answered Nov 13 '22 08:11

Shaun Luttin