something like
var results = from post in session.Query<Post>()
select new { post.Title, UsersQty =
post.Comments.Select(c => c.User).Distinct().Count() }
?
2010/12/3 W3Max <[email protected]>
> How would you do this query with the new Linq-to-NHibernate Provider
> (3.x)
>
> To the question in a more appropriate format :
>
> http://stackoverflow.com/questions/4341994/count-distinct-inner-join-group-by-with-the-new-linq-to-nhibernate-provider
>
> SELECT Post.Title, COUNT(DISTINCT Comment.UserId)
> FROM Post
> INNER JOIN Comment ON Post.Id = Comment.PostId
> GROUP BY Post.Title
>
> Here is some SQL if you want to make some 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 INTO @Post(Title)
> VALUES ('Test')
>
> SELECT @PostId = SCOPE_IDENTITY()
>
> INSERT INTO @Comment(PostId, Comment, UserId)
> VALUES (@PostId, 'Test Comment', 1)
>
> INSERT INTO @Comment(PostId, Comment, UserId)
> VALUES (@PostId, 'Test Comment 2', 1)
>
> INSERT INTO @Comment(PostId, Comment, UserId)
> VALUES (@PostId, 'Test Comment 3', 2)
>
> INSERT INTO @Post(Title)
> VALUES ('Test 2')
>
> SELECT @PostId = SCOPE_IDENTITY()
>
> INSERT INTO @Comment(PostId, Comment, UserId)
> VALUES (@PostId, 'Test Comment', 1)
>
> INSERT INTO @Comment(PostId, Comment, UserId)
> VALUES (@PostId, 'Test Comment 2', 2)
>
> INSERT INTO @Comment(PostId, Comment, UserId)
> VALUES (@PostId, 'Test Comment 3', 3)
>
>
> SELECT Post.Title, COUNT(DISTINCT Comment.UserId)
> FROM @Post Post
> INNER JOIN @Comment Comment ON Post.Id = Comment.PostId
> GROUP BY Post.Title
>
> --
> You received this message because you are subscribed to the Google Groups
> "nhusers" group.
> To post to this group, send email to [email protected].
> To unsubscribe from this group, send email to
> [email protected]<nhusers%[email protected]>
> .
> For more options, visit this group at
> http://groups.google.com/group/nhusers?hl=en.
>
>
--
You received this message because you are subscribed to the Google Groups
"nhusers" group.
To post to this group, send email to [email protected].
To unsubscribe from this group, send email to
[email protected].
For more options, visit this group at
http://groups.google.com/group/nhusers?hl=en.
