I Assume that this is an impossibility with nhibernate 3.0 Group By SubQuery

[thecodefool]

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 :)

[Richard Brown (gmail)]

I'm not sure if the following would do what you need?

var urlCount =
s.QueryOver<MyTable>()
.SelectList(list => list.SelectCountDistinct(ca => ca.MyUrl))
.SingleOrDefault<int>();

It doesn't generate the SQL you asked for, but I think it gives you the
result you wanted:

SELECT count(distinct this_.MyUrl) FROM [MyTable] this_

--
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.

[theco...@gmail.com]

Hi Richard,

Thanks so much for your reply.

You are correct but my problem is deeper than just that.

I did not want to confuse by the complexity of the query. My apologies

Here is the final query

select count(*) urlCount from (select distinct UserId
,convert(varchar,MyAccessDate,110) as MyAccessDate
, MyUrl
from MyTable where MyId=1
and cast(convert(varchar,MyAccessDate,110) as DateTime) >= '4/10/2011') myAlias

--convert(varchar,MyAccessDate,110) -- Simply removes the time formating from a date


Sorry for not being more specific

Thanks again for your help

On Apr 13, 2011 3:50am, "Richard Brown (gmail)" <fluk...@googlemail.com> wrote:
> I'm not sure if the following would do what you need?
>
>
>
>   var urlCount =
>

>       s.QueryOver()

>
>           .SelectList(list => list.SelectCountDistinct(ca => ca.MyUrl))
>

>           .SingleOrDefault();

> var query = Session.QueryOver()
>
>                   .Select(Projections.GroupMyTable>(ca =>
>
> ca.MyUrl))
>
>               //    .RowCount();
>
>                    .List();

>
> 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()
>
>                   .Select(Projections.GroupMyTable>(ca =>

>       public void Override(AutoMappingMyTableViewDto> mapping) {

>
>           mapping.Table("MyTableView");
>
>       }
>
>   }
>
> Finally I do this:
>

> var ret = Session.QueryOverMyTableViewDto>().RowCount();

[Richard Brown (gmail)]

Ah, ok.

 

I can’t think of a way to do that.  Sorry.

[theco...@gmail.com]

No worries friend, sorry I did not make it more clear

I think that I am just going to have to go with Session.CreateSQLQuery()

Thanks for looking anyway