Linq join and count

Question

I am new to Linq and wondered how I can get a list of customer IDs and the number 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 clients with transactions, but not too confident how I get the bill.

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

Answer 1

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

There is no need to join you, you have all the information about the Transaction object.

However, you need to join if you want to get additional information about the client, such as the name of the client, 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(); 

Answer 2

Answer to

saj will only work if every client has a transaction. Instead, it would be better to use a group join starting with Customer and calculate 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() };