I have an application that uses several principles of data storage, such as size modeling, to create reports on a fairly simple database.
An example (simplified) object called Call is as follows:
public virtual long Id { get; set; } public virtual string OriginatorNumber { get; set; } public virtual string DestinationNumber { get; set; } public virtual DateDimension DateDimension { get; set; }
Some properties of the real model have been removed because they are irrelevant. A simplified DateDimension is as follows:
public virtual long Id { get; set; } public virtual DateTime Date { get; set; } public virtual int DayOfMonth { get; set; } public virtual int Weekday { get; set; }
There are many more columns, such as this - they are laid in the current decade by installing the application. Thus, each date throughout the decade has a row in this table, and each call has a link to the date when it occurred. All of this is displayed in Fluent NHibernate and works fine.
If I want to do some reports, I can do it easily with the improved NHibernate LINQ provider in version 3.0. We would like to use LINQ to improve the service it gives us, but if we really MUST, we will consider HQL, ICriteria, or even plain SQL.
So, say I want to create a report that shows the number of calls from a specific number divided by the day of the week when they occur. I can do this easily as follows:
var query = Calls .Where(c => c.OriginatorNumber == "402") .GroupBy(c => c.DateDimension.Weekday) .Select(g => new { Day = g.Key, Calls = g.Count() } );
In this example, “Calls” is basically IQueryable, returned by the NHibernates LINQ (Query) provider through the repository interface. The above query gives me the correct results, NHibernate Profiler shows me that SQL is pretty optimal, all is well.
However, if I want to do something more advanced, I'm stuck. Let's say I need the average number of calls on a weekday. Not too far from above, right? I just need to find out the number of unique dates that every weekday has in the result set, divide the total number of calls, and we are all set up - right? Well, no, it is here that I begin to face the limitations of the NHibernate LINQ provider. With LINQ for objects, I could build a query to do this - something along the lines
.Select(g => g.Count() / g.GroupBy(c => c.DateDimension.Date).Count());
However, this does not translate into the correct query when used in NHibernate. Rather, it calls both .Count () calls in the above example for the same counter (*) call records, so the result is always 1.
I CAN, of course, simply request for each call, day of the week and date as a new anonymous object, and then do the math on the application side, but in accordance with the generally accepted wisdom of That Just Wrong (tm). I could do it in desperation, even if it means pain, when the table grows to millions of calls to ++.
Below is an SQL query that gives me the result I'm looking for.
select ss.Weekday, AVG(cast(ss.Count as decimal)) from ( select dd.Weekday, dd.Date, COUNT(*) as Count from Call c left outer join DateDimension dd on c.DateDimension_id = dd.Id where c.OriginatorNumber = '402' group by dd.Weekday, dd.Date ) ss group by ss.Weekday order by ss.Weekday
Is it possible to do this with the NHibernate LINQ provider? Or, if this is not possible, how close can I get before I have to let the application get the result of the intermediary and do the rest?