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

It is unclear what you want. Can you provide the test data and desired result? — Hamlet Hakobyan
– Hamlet Hakobyan, Commented Mar 17, 2014 at 16:50
I added an example to illustrate what I want to obtain. My apologies. — David Jiménez Martínez
– David Jiménez Martínez, Commented Mar 17, 2014 at 17:03

MarcinJuraszek · Accepted Answer · 2014-03-17 17:14:05Z

3

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.

answered Mar 17, 2014 at 17:14

MarcinJuraszek

126k15 gold badges200 silver badges267 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

David Jiménez Martínez Over a year ago

This approach is interesting as it has enabled me to think about a way to include ways without any registration nor purchase. Thank you!

MarcinJuraszek Over a year ago

@DavidJiménezMartínez Yeah, it's really easy to do with that approach. Just change dates definition to contain all days between two dates you're interested in showing data for.

David Jiménez Martínez Over a year ago

I guess I'll have to modify the linq expression to allow for left outer joins for dates without any registration nor purchases.

MarcinJuraszek Over a year ago

I don't think you need any changes to get that.

Servy · Accepted Answer · 2014-03-17 17:11:57Z

1

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),

    };

answered Mar 17, 2014 at 17:11

Servy

204k27 gold badges347 silver badges470 bronze badges

1 Comment

MarcinJuraszek Over a year ago

It won't return record when there is a purchase at given date, but no user registration.

Aducci · Accepted Answer · 2014-03-17 17:18:31Z

1

This is really two separate queries with their results merged at the end.

var contact = from c in Contact
              group c by c.RegisterDate into c
              select new StatusData
              {
                Date = c.Key,
                RegisteredUsers = c.Count(),
                Orders = 0,
                Import = 0,
              };

var purchases = from p in Purchases
                group p by p.PurchaseDate into p
                select new StatusData
                {
                  Date = p.Key,
                  RegisteredUsers = 0,
                  Orders = p.Count(),
                  Import = p.Sum(x => x.Import),
                };

var final = from x in contact.Concat(purchases)
            group x by x.Date into x
            select new StatusData
            {
              Date = x.Key,
              RegisteredUsers = x.Sum(y => y.RegisteredUsers),
              Orders = x.Sum(y => y.Orders),
              Import = x.Sum(y => y.Import),
             };

answered Mar 17, 2014 at 17:18

Aducci

26.8k8 gold badges67 silver badges69 bronze badges

2 Comments

David Jiménez Martínez Over a year ago

This approach, as well as the one of @MarcinJuraszek, have been rather simple to understand and implement. Thank you both :)

Seb Over a year ago

Do you think it can be made with .Aggregate() to decrease the number of enumerations (presumably due to .Sum()) ?

Collectives™ on Stack Overflow

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

3 Answers 3

4 Comments

1 Comment

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

4 Comments

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related