After doing extensive Googling, I am baffled Overview: Using: SQL Server 2005:
I simply want to do this: SQL: Select count(*) from(select MyUrl from MyTable Group By MyUrl) myAlias Now I think the whole problem is the fact that you must put an alias on your select "myAlias" By doing the above select statement I should be able to count the urls that are grouped in MyTable If MyTable had 4 rows ------------MyURL------------------ http://www.google.com http://www.google.com http://www.google.com http://groups.google.com/group/sharp-architecture The above query would retrieve 2 (1 count for google, 1 count for sharp) First Attempt: var query = Session.QueryOver<MyTable>() .Select(Projections.Group<MyTable>(ca => ca.MyUrl)) // .RowCount(); .List<object[]>(); The above will return 2 objects and I can get that count, but as you know this is inefficient (if there was a count of 100,000). If you comment out the last line and uncomment the .RowCount(); , you would expect that to work, but alas it does not. You will get SELECT count(*) From MyTable Which ignores my grouping. Second attempt: I have read this article but it seems outdated, and unanswered. http://groups.google.com/group/nhusers/browse_thread/thread/9a18c7cf9... var detached = QueryOver.Of<MyTable>() .Select(Projections.Group<MyTable>(ca => ca.ContentUrl)); var itemCountCriteria = Session.QueryOver(()=>detached) .Select(Projections.Count(Projections.SubQuery(detached))); var multiList = Session.CreateMultiCriteria() .Add(itemCountCriteria) .List(); I get this error ERROR: Interceptor.OnPrepareStatement(SqlString) returned null or empty SqlString.An AssertionFailure occurred - this may indicate a bug in NHibernate or in your custom types. Final: In the end I created a SQL view with the grouping like this: CREATE VIEW [dbo].[MyTableView] AS SELECT MyUrl FROM MyTable Group By MyUrl so I could perform: select COUNT(*) From MyTableView I created an entity class: public class MyTableViewDto:Entity { public virtual string MyUrl { get; set; } } And a mapping class public class MyTableViewMap : IAutoMappingOverride<MyTableViewDto> { public void Override(AutoMapping<MyTableViewDto> mapping) { mapping.Table("MyTableView"); } } Finally I do this: var ret = Session.QueryOver<MyTableViewDto>().RowCount(); This however now makes this test fail [TestFixture] [Category("DB Tests")] public class MappingIntegrationTests{ [Test] public void CanConfirmDatabaseMatchesMappings() { var allClassMetadata = NHibernateSession.GetDefaultSessionFactory().GetAllClassMetadata(); foreach (var entry in allClassMetadata) { NHibernateSession.Current.CreateCriteria(entry.Value.GetMappedClass(EntityM ode.Poco)) .SetMaxResults(0).List(); } } } With this error: NHibernate.Exceptions.GenericADOException : could not execute query [ SELECT TOP (@p0) this_.MyTableViewDtoId as MyTableV1_1_0_, this_.MyUrl as MyUrl1_0_ FROM MyTableView this_ ] [SQL: SELECT TOP (@p0) this_.MyTableViewDtoId as MyTableV1_1_0_, this_.MyUrl as MyUrl1_0_ FROM MyTableView this_] ----> System.Data.SqlClient.SqlException : Invalid column name 'MyTableViewDtoId'. Now I know that this is due to the fact that I created the Entity class and it is trying to select the id, which is non-existent So: 1. How do I exclude the id column in my select clause(since in my example if I add that real ID column into the select it will mess up the grouping). 2. Does anyone have any idea how to accomplish this? 3. Is this impossible in nhibernate? Please help :) -- You received this message because you are subscribed to the Google Groups "nhusers" group. To post to this group, send email to nhusers@googlegroups.com. To unsubscribe from this group, send email to nhusers+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/nhusers?hl=en.
