Group by statement in nhibernate

senzacionale · **Joined:** Wed Jun 24, 2009 8:13 pm **Posts:** 12

Code:

public IList<SearchQueries> GetTopSearches()
        {
            return NHibernateSession.CreateCriteria(typeof(SearchQueries))
                .SetProjection(Projections.ProjectionList()
                .Add(Projections.RowCount(), "catCountQuery")
                .Add(Projections.GroupProperty("Query,")))
                .AddOrder(Order.Desc("catCountQuery"))
                .List<SearchQueries>();
        }

but i get error

The value "System.Object[]" is not of type "WebCrawler.Core.Entities.SearchQueries" and cannot be used in this generic collection.
Parameter name: value

i want to use group by Query statement so i use .Add(Projections.GroupProperty("Query,"))).

I want SQL like:
Select COUNT(Query) AS catCountQuery FROM SearchQueries GROUP BY Query DESC

What ia m doing wrong, thx

wolli · **Posted:** Thu Jun 25, 2009 1:20 am

.List<SearchQueries>();

expects that the query returns entities of this type. Your query returns an int (or maybe a long, not sure). Try this instead:

.List<int>();

senzacionale · **Joined:** Wed Jun 24, 2009 8:13 pm **Posts:** 12

Hi,

thx for your answer but i think that must be SearchQueries becouse now i get

Error 1 Cannot implicitly convert type 'System.Collections.Generic.IList<int>' to 'System.Collections.Generic.IList<WebCrawler.Core.Entities.SearchQueries>'. An explicit conversion exists (are you missing a cast?) D:\PROJEKTI\crawler\WebCrawlerSuite\WebCrawler.Data\SearchQueriesDao.cs 29 17 WebCrawler.Data

wolli · **Posted:** Thu Jun 25, 2009 4:04 am

Aarg, sorry, you don't only get a single value, you get an array:

NHibernateSession.CreateCriteria(typeof(SearchQueries))
.SetProjection(Projections.ProjectionList()
.Add(Projections.RowCount(), "catCountQuery")
.Add(Projections.GroupProperty("Query,")))
.AddOrder(Order.Desc("catCountQuery"))

will return an array { catCountQuery, Query }. You can't cast that into an IList<SearchQueries>.

senzacionale · **Joined:** Wed Jun 24, 2009 8:13 pm **Posts:** 12

hmm so how must whole method look like now?

I have IList becouse there is many results not only one...

wolli · **Posted:** Thu Jun 25, 2009 4:23 am

Depends on what you want to get ? Your query returns a count per query, so you can either build a class for that or return a list of object[].

senzacionale · **Joined:** Wed Jun 24, 2009 8:13 pm **Posts:** 12

yes i want to make list of objects but you said that i can not use .List<SearchQueries>(); in my method. Now i am little confused what to do...

wolli · **Posted:** Thu Jun 25, 2009 5:08 am

You want the SearchQueries plus their execution count ??

senzacionale · **Joined:** Wed Jun 24, 2009 8:13 pm **Posts:** 12

yes but if this is to complicated just group by statement.

I want this
Select COUNT(Query) AS catCountQuery FROM SearchQueries GROUP BY Query DESC

but if is to complicated then

Select * FROM SearchQueries GROUP BY Query DESC

but i don't know what is wrong with my method that does not working...

wolli · **Posted:** Thu Jun 25, 2009 10:32 am

That statement is not the problem:

Select COUNT(Query) AS catCountQuery FROM SearchQueries GROUP BY Query DESC

but you only get the count here, no objects. If you want the object plus the count, the statement will be much more complex (even the sql). And regardless how you write the statement, you will never get a result like List<SearchQueries> because the object and the count are treated as two "objects".

You can try this HBM query, but I'm not sure if a subselect in the select part is supported:

session.CreateQuery("select s1, (select count(*) from SearchQueries s2 where s2.Query = s1.Query) as catCountQuery from SearchQueries s1").List();