sql - Count Distinct - Inner Join - Group By / With the new Linq-to-NHibernate Provider -

how write exact sql query new linq-to-nhibernate provider (3.x)

select post.title, count(distinct comment.userid) post inner join comment on post.id = comment.postid group post.title 

here sql if want make tests

declare @post table(id int identity(1,1), title varchar(200)) declare @comment table(id int identity(1,1), postid int, comment varchar(200), userid int)  declare @postid int  insert @post(title) values ('test')  select @postid = scope_identity()  insert @comment(postid, comment, userid) values (@postid, 'test comment', 1)  insert @comment(postid, comment, userid) values (@postid, 'test comment 2', 1)  insert @comment(postid, comment, userid) values (@postid, 'test comment 3', 2)  insert @post(title) values ('test 2')  select @postid = scope_identity()  insert @comment(postid, comment, userid) values (@postid, 'test comment', 1)  insert @comment(postid, comment, userid) values (@postid, 'test comment 2', 2)  insert @comment(postid, comment, userid) values (@postid, 'test comment 3', 3)   select post.title, count(distinct comment.userid) @post post inner join @comment comment on post.id = comment.postid group post.title 

i don't think it's possible count(distinct x) part.

this closest got:

from comment in session.query<comment>() group comment comment.post.title       g       select new              {                  title = g.key,                  count = g.select(x => x.userid).distinct().count()              }; 

but produces same sql as:

from comment in session.query<comment>() group comment comment.post.title       g       select new              {                  title = g.key,                  count = g.count()              }; 

which is:

select post.title, count(*) comment left join post on post.id = comment.postid group post.title 

you should post issue http://jira.nhforge.org. there's lot of work going on linq provider , there's chance construct supported in near future.