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

Question

Let's say I have a very simple entity:

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 sample object maps to NHibernate (using Fluent) and works great.

Time to create reports. In this example, "testGuys" is 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 great. In the NHibernate Profiler, I see how the correct SQL is created and the results are expected.

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

 var fieldToAverageBy = GetAverageField(SomeEnum.Other); 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, in another place, 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 it. However, when I list this, NHibernate throws a fit:

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

So, I suppose that behind the scenes there is some kind of conversion or casting or something that takes my lambda in the first case, but does something in the second case that NHibernate cannot convert to SQL.

My question, I hope, is simple - how will my GetAverageField function return something that will work as a parameter for Average () when LINQ NHibernate 3.0 support (.Query () method) translates this into SQL?

Any suggestions are welcome, thanks!

EDIT

Based on David B's comments in my answer, I dwell on this in more detail. My suggestion that Func would be the correct return type was based on the intellisense that I got for the Average () method. It seems to be based on the Enumerable type, not the Queryable. This is strange .. You need to get a little closer to the point.

The GroupBy method has the following reverse signature:

 IQueryable<IGrouping<string,TestGuy>> 

This means that he should give me IQueryable, everything is in order. However, I proceed to the next line:

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

If I check intellisense for the variable g inside the new definition of the {} object, it is actually specified as the IGrouping type - NOT IQueryable>. This is why the called Average () method is Enumerable, and why it will not accept the expression parameter proposed by David B.

So somehow, my group value seems to have lost the IQueryable status somewhere.

A little interesting note:

I can change the "Select" value to the following:

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

And now it compiles! Black magic! However, this does not solve the problem, since NHibernate now no longer loves me 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 puzzles me is that it works when I give a lambda expression to the Average () method, but I cannot find an easy way to present the same expression as the argument. Obviously, I’m doing something wrong, but I can’t understand what ...!?

I am on my way. Help me, John Skeet, you are my only hope! ;)

Answer 1

You cannot call the "local" method in your lambda expression. If it were a simple non-nested sentence, it would be relatively simple - you just need to change this:

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

:

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

and then pass the result of the call to the appropriate request method, for example.

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

In this case, however, you need to build the entire expression tree for the Select clause — an anonymous type creation expression, key extraction and middle field extraction. Honestly, this will not be fun. In particular, by the time you create your expression tree, it will not be statically typed in the same way as a regular query expression, due to the impossibility of expressing an anonymous type in the declaration.

If you use .NET 4, dynamic typing can help you, although you will certainly pay the price for not having static typing.

One of the options (possibly terrible) may try to use a kind of “template” of an anonymous type design expression tree (for example, always using one property), and then build a copy of this expression tree by inserting the correct expression instead. Again, this will not be fun.

Marc Gravell can help more with this - it seems like it should be possible, but I don’t understand how to do it elegantly at the moment.

Answer 2

A? Queryable.Average parameter is not Func<T, U> . This is 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!"); } 

The following are:

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