Subsonic 3 and Linq Group by with Count

Question

I have the following two tables:

Customer
{
int Id
int Name
}

Bills
{
int Id
int CustomerId
decimal Amount
bool IsDue
}

Now I am trying to get a list where I have:

1. A entry for every customer with the count of associated bills.
2. A entry for every customer with the count of associated bills where IsDue is true.

I tried doing the first one like this:

var results = from c in _db.Customers
        join b in _db.Bills on c.Id equals b.CustomerId into j1
        from j2 in j1
        group j2 by c.Id into grouped
        select new
         {
          CustomerId = grouped.Key, 
          NoOfBills = grouped.Count()
         };

This is throwing a error: Expression of type 'System.Collections.Generic.IEnumerable1[OutstandingMonitor.MonitorData.Customer]' cannot be used for parameter of type 'System.Linq.IQueryable1[OutstandingMonitor.MonitorData.Customer]' ...

Please help me solve this.

Further, can both the queries be combined?

PS: Using Subsonic 3.0.0.3 with ActiveRecord

Answer 1

Wherever you're using the value for the "customer's count of associated bills", can't you simply do this in LINQ (this will only work, however, if you have the appropriate relationships set up in the DBML):

var customers = _db.Customers; // Not necessary, but here for clarity

// ... more code

// Access the number of bills for each customer:

foreach (var customer in customers)
{
    int totalBills = customers.Bills.Count();
    int totalDueBills customers.Bills.Where(b => b.IsDue).Count();
}

Answer 2

I found a not so efficient solution (if it is efficient indeed, I will love to know...:P). Here it is:

var results = from d in _db.Bills
       group d by d.CustomerId into g
       select new
       {
        CustomerId = g.Key,
        NoOfBills = g.Count()
       };

var results2 = from c in _db.Customers
        join r in results on c.Id equals r.CustomerId
        select new
        {
         CustomerId = r.CustomerId,
         CustomerName = c.Name,
         City = c.City,
         NoOfBills = r.NoOfBills
        };

For the time being it works fine.