Statistical query in SQL - is this possible with NHibernate LINQ?

Question

I have an application that uses a few data warehousing principles such as dimensional modeling to do reporting on a fairly simple database.

An example (simplified) entity named Call looks like this:

    public virtual long Id { get; set; }
    public virtual string OriginatorNumber { get; set; }
    public virtual string DestinationNumber { get; set; }
    public virtual DateDimension DateDimension { get; set; }

A few of the properties of the real model have been removed as they are irrelevant. The simplified DateDimension looks like this:

    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 a LOT more columns like this - they are prepopulated for the current decade by application setup. So each date in the entire decade has a row in this table, and each Call has a link to the date that it occured. This is all mapped in Fluent NHibernate and working fine.

If I want to do some reporting, I can do this easily with the improved NHibernate LINQ provider in 3.0. We would like to use LINQ for the improved maintainability it gives us, but if we really MUST, we'll consider HQL, ICriteria or even plain SQL.

So say I want to build a report that shows the number of calls from a certain number, divided by the day of the week they occur. I can do that easily this way:

        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 an IQueryable returned from NHibernates LINQ provider (Query) through a repository interface. The query above gives me the correct results, NHibernate Profiler shows me that the SQL is pretty optimal, all is well.

However, if I want to do something slightly more advanced, I get stuck. Say I want the average number of calls per weekday. Not too far from the above, right? I just need to figure out the number of unique dates each weekday has in the result set, divide the total number of calls by it, and we're all set - right? Well, no, this is where I start to hit the limitations of the NHibernate LINQ provider. With LINQ to objects I could construct a query to do it - something along the lines of

.Select(g => g.Count() / g.GroupBy(c => c.DateDimension.Date).Count());

However, this does not convert into the correct query when using it in NHibernate. Rather, it turns both .Count() calls in the above to the same count(*) of call records, so the result is always 1.

I COULD of course just query for each call, weekday and date as a new anonymous object, then do the math on the application side, but according to conventional wisdom, That's Just Wrong (tm). I could end up doing it in desperation, tho, even tho it means pain when the table grows to a million++ calls.

The below is an SQL query that gives me the result I am 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 that is not possible, how close can I get before I have to let the application fetch the intermediary result and do the rest?

Answer 1

There are a lot of things you can't do with the LINQ provider. Using HQL or CreateCriteria is just something you'll have to accept with NHibernate.

I haven't tried it, but it looks like you should be able to do what you want to do using HQL or CreateCriteria (with DetatchedCriteria).

If you are desperate you can also fall back to plain SQL using CreateSqlQuery.