linq join and count

I’m new to Linq and was wondering how I would go about obtaining a List of Customer Id, and a count of their transactions

public class Transaction
{
    public int TransactionId {get; set;}
    public int CustomerId {get; set;}   
}


public class Customer
{
    public int ID {get; set;}
    public string Name {get; set;}
    public string Surname {get; set;}
}

I think I need to join customers with transactions but not too sure how I would get the count.

    var query  =    (from c  in customers
                    join t in transactions on c.ID equals t.CustomerId

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

var query = transactions
                .GroupBy(t => t.CustomerId)
                    .Select (t => new { Id = t.Key, TranCount = t.Count() })
                        .ToList();

No need to join you have all the information on the Transaction object.

You would, however need to join if you wanted extra customer information such as customer surname, in which case you could do the following;

var query  =    (from c  in customers
                join t in transactions on c.ID equals t.CustomerId
                group c by c.ID  into grp 
                select new 
                { 
                    Id = grp.Key, 
                    Surname = grp.First().Surname, 
                    TranCount = grp.Count() 
                }).ToList();

Method 2

saj’s answer will only work if every customer has a transaction. Instead, it would be better to use a group join starting with Customer, and count the result:

var query = from customer in customers
            join transaction in transactions
              on customer.Id equals transaction.CustomerId
              into customerTransactions
            select new { customer.Id, Count = customerTransactions.Count() };

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0