NHibernate 3 LINQ - how to create a valid parameter for Average()

Say I have a very simple entity like this:

public class TestGuy
{
    public virtual long Id {get;set;}
    public virtual string City {get;set;}
    public virtual int InterestingValue {get;set;}
    public virtual int OtherValue {get;set;}
}

This contrived example object is mapped with NHibernate (using Fluent) and works fine.

Time to do some reporting. In this example, "testGuys" is an IQueryable with some criteria already applied.

var byCity = testGuys
    .GroupBy(c => c.City)
    .Select(g => new { City = g.Key, Avg = g.Average(tg => tg.InterestingValue) });

This works just fine. In NHibernate Profiler I can see the correct SQL being generated, and the results are as expected.

Inspired by my success, I want to make it more flexible. I want to make it configurable so that the user can get the average of OtherValue as well as InterestingValue. Shouldn't be too hard, the argument to Average() seems to be a Func (since the values are ints in this case). Easy peasy. Can't I just create a method that returns a Func based on some condition and use that as an argument?

var fieldToAverageBy = GetAverageField(SomeEnum.Othe开发者_运维知识库r);

private Func<TestGuy,int> GetAverageField(SomeEnum someCondition)
{
    switch(someCondition)
    {
        case SomeEnum.Interesting:
            return tg => tg.InterestingValue;
        case SomeEnum.Other:
            return tg => tg.OtherValue;
    }

    throw new InvalidOperationException("Not in my example!");
}

And then, elsewhere, I could just do this:

var byCity = testGuys
    .GroupBy(c => c.City)
    .Select(g => new { City = g.Key, Avg = g.Average(fieldToAverageBy) });

Well, I thought I could do that. However, when I do enumerate this, NHibernate throws a fit:

Object of type 'System.Linq.Expressions.ConstantExpression' cannot be converted to type 'System.Linq.Expressions.LambdaExpression'.

So I am guessing that behind the scenes, some conversion or casting or some such thing is going on that in the first case accepts my lambda, but in the second case makes into something NHibernate can't convert to SQL.

My question is hopefully simple - how can my GetAverageField function return something that will work as a parameter to Average() when NHibernate 3.0 LINQ support (the .Query() method) translates this to SQL?

Any suggestions welcome, thanks!

EDIT

Based on the comments from David B in his answer, I took a closer look at this. My assumption that Func would be the right return type was based on the intellisense I got for the Average() method. It seems to be based on the Enumerable type, not the Queryable one. That's strange.. Need to look a bit closer at stuff.

The GroupBy method has the following return signature:

IQueryable<IGrouping<string,TestGuy>>

That means it should give me an IQueryable, all right. However, I then move on to the next line:

.Select(g => new { City = g.Key, Avg = g.Average(tg => tg.InterestingValue) });

If I check the intellisense for the g variable inside the new { } object definition, it is actually listed as being of type IGrouping - NOT IQueryable>. This is why the Average() method called is the Enumerable one, and why it won't accept the Expression parameter suggested by David B.

So somehow my group value has apparently lost it's status as an IQueryable somewhere.

Slightly interesting note:

I can change the Select to the following:

.Select(g => new { City = g.Key, Avg = g.AsQueryable<TestGuy>().Average(fieldToAverageBy) });

And now it compiles! Black magic! However, that doesn't solve the issue, as NHibernate now doesn't love me anymore and gives the following exception:

Could not parse expression '[-1].AsQueryable()': This overload of the method 'System.Linq.Queryable.AsQueryable' is currently not supported, but you can register your own parser if needed.

What baffles me is that this works when I give the lambda expression to the Average() method, but that I can't find a simple way to represent the same expression as an argument. I am obviously doing something wrong, but can't see what...!?

I am at my wits end. Help me, Jon Skeet, you're my only hope! ;)

You won't be able to call a "local" method within your lambda expression. If this were a simple non-nested clause, it would be relatively simple - you'd just need to change this:

private Func<TestGuy,int> GetAverageField(SomeEnum someCondition)

to this:

private Expression<Func<TestGuy,int>> GetAverageField(SomeEnum someCondition)

and then pass the result of the call into the relevant query method, e.g.

var results = query.Select(GetAverageField(fieldToAverageBy));

In this case, however, you'll need to build the whole expression tree up for the Select clause - the anonymous type creation expression, the extraction of the Key, and the extraction of the average field part. It's not going to be fun, to be honest. In particular, by the time you've built up your expression tree, that's not going to be statically typed in the same way as a normal query expression would be, due to the inability to express the anonymous type in a declaration.

If you're using .NET 4, dynamic typing may help you, although you'd pay the price of not having static typing any more, of course.

One option (horrible though it may be) would be try to use a sort of "template" of the anonymous type projection expression tree (e.g. always using a single property), and then build a copy of that expression tree, inserting the right expression instead. Again, it's not going to be fun.

Marc Gravell may be able to help more on this - it does sound like the kind of thing which should be possible, but I'm at a loss as to how to do it elegantly at the moment.

Eh? the parameter to Queryable.Average is not Func<T, U>. It's Expression<Func<T, U>>

The way to do this is:

private Expression<Func<TestGuy,int>> GetAverageExpr(SomeEnum someCondition)
{
switch(someCondition)
{
case SomeEnum.Interesting:
  return tg => tg.InterestingValue;
case SomeEnum.Other:
  return tg => tg.OtherValue;
}
throw new InvalidOperationException("Not in my example!");
} 

Followed by:

Expression<Func<TestGuy, int>> averageExpr = GetAverageExpr(someCondition);
var byCity = testGuys
  .GroupBy(c => c.City)
  .Select(g => new { City = g.Key, Avg = g.Average(averageExpr) });