CODEWITHSUNDEEP
sqlnhibernatesubquery
ForCripeSake
ForCripeSake

Reputation: 438

How do I select the Count(*) of an nHibernate Subquery's results

I need to do the following for the purposes of paging a query in nHibernate:

Select count(*) from 
(Select e.ID,e.Name from Object as e where...)

I have tried the following, 

select count(*) from Object e where e = (Select distinct e.ID,e.Name from ...)

and I get an nHibernate Exception saying I cannot convert Object to int32.

Any ideas on the required syntax?

EDIT

The Subquery uses a distinct clause, I cannot replace the e.ID,e.Name with Count(*) because Count(*) distinct is not a valid syntax, and distinct count(*) is meaningless.

Upvotes: 9

Views: 30273

Answers (6)

user8456
user8456

Reputation: 346

If you just need e.Id,e.Name:

select count(*) from Object where.....

Upvotes: 0

bipinkarms
bipinkarms

Reputation: 2089

NHibernate 3.0 allows Linq query.

Try this

int count = session.QueryOver<Orders>().RowCount();

Upvotes: 18

Marcelo Salazar
Marcelo Salazar

Reputation: 72

I prefer,

    public IList GetOrders(int pageindex, int pagesize, out int total)
    {
            var results = session.CreateQuery().Add(session.CreateQuery("from Orders o").SetFirstResult(pageindex).SetMaxResults(pagesize));

            var wCriteriaCount = (ICriteria)results.Clone());

            wCriteriaCount.SetProjection(Projections.RowCount());

            total = Convert.ToInt32(wCriteriaCount.UniqueResult());


            return results.List();
    }

Upvotes: 0

Matt Hinze
Matt Hinze

Reputation: 13679

var session = GetSession();
var criteria = session.CreateCriteria(typeof(Order))
                    .Add(Restrictions.Eq("Product", product))
                    .SetProjection(Projections.CountDistinct("Price"));
return (int) criteria.UniqueResult();

Upvotes: 17

ForCripeSake
ForCripeSake

Reputation: 438

Solved My own question by modifying Geir-Tore's answer.....

 IList results = session.CreateMultiQuery()
        .Add(session.CreateQuery("from Orders o").SetFirstResult(pageindex).SetMaxResults(pagesize))
        .Add(session.CreateQuery("select count(distinct e.Id) from Orders o where..."))
        .List();
    return results;

Upvotes: 2

geeTee
geeTee

Reputation: 1147

Here is a draft of how I do it:

Query:

public IList GetOrders(int pageindex, int pagesize)
{
    IList results = session.CreateMultiQuery()
        .Add(session.CreateQuery("from Orders o").SetFirstResult(pageindex).SetMaxResults(pagesize))
        .Add(session.CreateQuery("select count(*) from Orders o"))
        .List();
    return results;
}

ObjectDataSource:

[DataObjectMethod(DataObjectMethodType.Select)]
public DataTable GetOrders(int startRowIndex, int maximumRows)
{
    IList result = dao.GetOrders(startRowIndex, maximumRows);
    _count = Convert.ToInt32(((IList)result[1])[0]);

    return DataTableFromIList((IList)result[0]); //Basically creates a DataTable from the IList of Orders
}

Upvotes: 1

Related Questions