Get Row Count InvalidCast Exception from ScalarQuery

Question

ScalarQuery<int> query = new ScalarQuery<int>(typeof(Role), 
                         "select count(role.RoleId) from Role as role");
return query.Execute();

It fails with the invalidcast exception but succeeds when count is replaced with max.

Answer 1

Edit: Some databases will return long for count queries. For example SQL Server.

ScalarQuery<long> query = new ScalarQuery<long>(typeof(Role), 
                          "select count(r) from Role r");
return query.Execute();

Answer 2

What database are you using? Could be that count does not return an int.

Your could also try using http://api.castleproject.org/html/T_Castle_ActiveRecord_Queries_CountQuery.htm

Answer 3

Not exactly the answer to the question, but a recommendation: if you want to avoid the hassle of having to issue a query at all yourself, then just use ActiveRecordMediator<T>.Count() (which has overloads that take criteria / filter strings if you want a conditional count) and all return int against all databases.

Answer 4

Based on testing the answers given to date, the following worked for me (including a where clause):

// Option 1
int result = ActiveRecordMediator<Post>.Count("BlogId = ?", blogId);

// Option 2
CountQuery query = new CountQuery(typeof(Post), "BlogId = ?", blogId);
int result = ActiveRecordMediator.ExecuteQuery(query);

// Option 3
ScalarQuery<long> query= new ScalarQuery<long>(typeof(Post),
  "SELECT COUNT(*) FROM Post WHERE BlogId = ?", blogId);
long result = query.Execute();