nhibernate complex projection expression

I'm trying to use nhibernate to retri开发者_Python百科eve data from mysql db. However I need the projection to run some calculations. Nhibernate doesn't seem to like that. It works great with linq to sql.

        var purchases = _session.QueryOver<Purchase>()
                        .Where(validPID<Purchase>(portfolioID))
                        .SelectList(list => list
                            .SelectGroup(c => c.currency)                                
                            .SelectSum(c => c.shares * c.price - c.commission))

I get "Could not determine member from ((c.shares * c.price) - c.commission)" as error.

How can I write this to work?

Thanks

---

What library of Linq extensions are you using that has SelectList, SelectGroup and SelectSum? They're not the standard methods for those operations, and they're not NH-provided extensions. As such, NHibernate probably has no clue what to do with them. As a rule, when constructing a Linq2NH query, you cannot use any custom extension methods; if it didn't come from System.Linq or NHibernate.Linq, you can't use it (one of the very few exceptions is IEnumerable.Contains() which translates to an IN query)

Try inserting an AsEnumerable() call after the Where method. This will basically force evaluation of the Queryable expression tree, and all further operations will be performed in-memory on the objects (or their NH proxies, populated as necessary).

---

Why dont you use HQL instead. I am not sure how to translate your query to HQl cos I cant make head or tail of it, but your artihematic operations will be supported in HQL.

If you can explain what you are doing i cou;d probably help you with the query.

---

I actaully ended up summing up in code like this

        var purchases = (from p in _session.QueryOver<Purchase>()
                                    .Where(validPID<Purchase>(portfolioID)).List().ToList()
                         group p by p.currency into currencies
                         select new object[2] {
                            currencies.Key, 
                            currencies.Sum(f => -1*f.price*f.shares - f.commission)
                        }).ToList();