How to merge two lists that share 1 attribute into a third one with LINQ?

Question

I'd wish to create a list of the following class (List):

public class StatusData
{
    public DateTime? Date { get; set; }
    public int RegisteredUsers { get; set; }
    public int Orders { get; set; }
    public decimal Import { get; set; }
}

This list should be filled with with information of another 2 lists: a List<Contact> and a List<Purchase>. Both classes have the following shape:

public class Contact
{
    public DateTime? RegisterDate { get; set; }
    public string Name { get; set; }
}

public class Purchase
{
    public DateTime? PurchaseDate { get; set; }
    public int PurchaseID {get;set;}
    public int Import { get; set; }
}

The idea is that the List should, for every day, which is the amount of Registered users, the amount of purchases and the total import of these purchases. So, in essence, both just share the Date.

However, I can't come with a good LINQ expression to create the List. One solution I considered is to retrieve all the distinct dates among the 2 lists and then iterate over them to retrieve the information I want, but I think this approach is rather "ugly". Is there any way to do what I want to do easily?

EDIT: An example of what I want would be the following:

Contacts
--------
Name      RegisteredDate

David     10/10/2013
Paul      09/10/2013
Gina      10/10/2013
Roger     09/10/2013
Rose      05/10/2013
Jean      07/10/2013
Mark      04/10/2013
Lisa      04/10/2013


Purchases
-----------
ID          PurchaseDate           Import
1           10/10/2013             10
2           10/10/2013             10
3           10/10/2013             20
4           04/10/2013             15
5           04/10/2013             15
6           07/10/2013             20
7           07/10/2013             2
8           07/10/2013             2


Expected result
----------------
Date          RegisteredUsers     Purchases      Import
04/10/2013    2                   2              30
05/10/2013    1                   0              0
07/10/2013    1                   3              24
09/10/2013    2                   0              0
10/10/2013    2                   3              40

Kind regards

Answer 1

var contacts = new List<Contact>();
var purchases = new List<Purchase>();

var dates = contacts.Select(x => x.RegisterDate.Value)
                    .Concat(purchases.Select(x => x.PurchaseDate.Value))
                    .Distinct();

var data = from date in dates
           join c in contacts on date equals c.RegisterDate.Value into registered
           join p in purchases on date equals p.PurchaseDate.Value into purchased
           select new StatusData {
               Date = date,
               RegisteredUsers = registered.Count(),
               Orders = purchases.Count(),
               Import = purchases.Sum(x => x.Import)
           };

It will return StatusData for all days, when at least one registration OR one purchase was made.

Answer 2

So there are several separate tasks here. The first thing that you're doing is grouping your contacts by the registration date. Next, you're joining that result to the Purchases table based on the date, aggregating count/sums on two different columns. LINQ has a method for each of these two operations:

var query = from contact in contacts
    group contact by contact.RegisterDate into contactGroup
    join purchase in purchases
    on contactGroup.Key equals purchase.PurchaseDate into purchaseGroup
    select new StatusData
    {
        Date = contactGroup.Key,
        RegisteredUsers = contactGroup.Count(),
        Orders = purchaseGroup.Count(),
        Import = purchaseGroup.Sum(p => p.Import),

    };