Aggregate projection list with linq to nhibernate

[Andrei Alecu]

Is it possible to generate a *single* query to the database, like:

select sum(x.value), count(*) from sometable

With NHibernate Linq?

I was able to do this with HQL and with QueryOver using
ProjectionList, but I can't seem to be able to find how to do it with
Linq.

Note that there's no grouping involved, I believe that if I had a
grouping I could've used GroupBy() and the query would've gotten
translated properly into one database hit.

If I need to tap into some deeply hidden Linq to HQL converters to do
it, it's fine, if only someone could point me in the right direction.

Thanks!

[Mohamed Meligy]

Although NH I think would happily do it, I think LINQ itself won't let you do it straight forward.

However, there is some way:

var baseProducyQuery = session.Query<Product>();
var query = from p in baseProducyQuery
            select new
                       {
                           Product = p,
                           Count = baseProducyQuery.Count(),
                           PriceOfAllProducts = baseProducyQuery.Sum(x => x.Price)
                       };

The generated SQL (working with Product model from http://gurustop.net/blog/2011/04/17/nh-nhibernate-mapping-jose-romaniello-ef-conform-domain-using-fnh-fluentnhibernate/ is:

    select
        product0_.Id as col_0_0_,
        (select
            cast(count(*) as INT)
        from
            [Product] product1_) as col_1_0_,
        (select
            cast(sum(product2_.Price) as DECIMAL(19,
            5))
        from
            [Product] product2_) as col_2_0_,
        product0_.Id as Id4_,
        product0_.Name as Name4_,
        product0_.Price as Price4_
    from
        [Product] product0_

 

Mohamed Meligy
Readify | Senior Developer

M:+61 451 835006 | W: readify.net

    

--
You received this message because you are subscribed to the Google Groups "nhusers" group.
To post to this group, send email to nhu...@googlegroups.com.
To unsubscribe from this group, send email to nhusers+u...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/nhusers?hl=en.

[Mohamed Meligy]

Sorry, baseProductQuery,  not baseProducyQuery.

 

Mohamed Meligy
Readify | Senior Developer

M:+61 451 835006 | W: readify.net

    

[Mohamed Meligy]

Just beware of the possible catches though with "where", "order" etc..

• If you put these into the select statement (assigned to variable "query" above), it will NOT be reflected in count/sum, because they work on the original queryable.
• If you add the where/order to baseProductQuery itself, it'll be repeated 3 times, once in the main SELECT, another in COUNT, and once more in SUM, due to the way the query is being constructed.

So, although it is possible, you may want to avoid it for the construction of the qyery (not sure about the performance as well, not expected very good one).

 

Mohamed Meligy
Readify | Senior Developer

M:+61 451 835006 | W: readify.net