LINQ To LLBL Group By – Two Queries To Get Count

For a persistence layer I’ve been using an ORM tool called LLBL GenPro.  I’ve chosen this tool up until now because LINQ to SQL or DLINQ or LINQ to Entities, as it’s called now, seemed like it was very prone to change.   I decided to wait until it settled and may begin to use it soon.

In the meanwhile LLBLGen Pro put out a LINQ implementation of their own, so I’ve been happily using LINQ with my ORM tool.  I’ve been having some interesting Group By issues, however.

The main problem I’ve had is when trying to use a function like count, or sum.  When I write the below I get a sql error :

var customerOrderByDate = from c in Customer
join 0 in Order on c.CustomerID equals o.CustomerID  into fullRow
from x in fullRow.DefaultIfEmpty()  //forces left join
group x by new { x.CustomerID, x.CustomerName} into  ordersGroup
select new  { ordersGroup.Key.CustomerID, ordersGroup.Key.CustomerName,  ordersGroup.Count(x.OrderDate != null) };

Oddly enough I can make the above create valid sql by separating the LINQ query that creates the data to group and the grouping itself into two separate LINQ queries.  Even though the below is two LINQ queries it produces one sql query which does what I want.

var customerOrderByDateData = from c in Customer
join 0 in Order on c.CustomerID equals o.CustomerID  into fullRow
from x in fullRow.DefaultIfEmpty()  //forces left join

var customerOrderByDateGroup = from data in customerOrderByDateData
group data by new {data.CustomerID, data.OrderDate.Date } in ordersGroup
select new  { ordersGroup.Key.CustomerID, ordersGroup.Key.CustomerName, ordersGroup.Count(og.OrderDate != null) };

I would expect the first query to function the same as the the two split out but they don’t.  Probably an issue with the LINQ to LLBL implementation.  I’m sure that was very difficult to do and I’m happy it works as well as it does.

One other problem I’ve noticed is that the boolean lambda expression in the Count method is required, but it doesn’t seem to do anything.  The SQL always produces Count(*) regardless of what is in the lambda expression and Count() doesn’t compile.