need help to write this query

[feelexit]

I have 3 tables.

Users table (Id, name, emal)
Reviews table (Id, title, body, postDate)
Following table (leader, Follower) : both leader and follower are
UserId, you can follow other users, you will are someone's follower,
you can see their last 3 month reviews.

I try to get all the reviews that your leaders posted in hte last 3
month.

var reviews = from user in _currentUser.Leaders
select user.Reviews;

I am getting back a list of Ilist<review>. it is definitely not
correct. please help me out, thank you.

[Joseph Daigle]

Change it to a "select many" instead of a "select".

[Patrick]

I'm not a LINQ expert but I've been messing with it a lot lately so I'll give it a shot.  What your current query says is, "for each user in _currentUser.Leaders (the outer IEnumerable), give me a list of their reviews (the inner IList)."  You just need to go down one more level.

var reviews = from user in _currentUser.Leaders

                   from review in user.Reviews
                   where review.postDate >= DateTime.Now.AddMonths(-3)
                   select review;

Joseph is right about SelectMany if you want to use the other syntax.  SelectMany will 'flatten' all the review collections into a single collection.

var reviews = _currentUser.Leaders.SelectMany(l => l.Reviews).Where(r => r.postDate >= DateTime.Now.AddMonths(-3));

I don't have Visual Studio in front of me right now so I can't check any of that, but it should be close.

-Patrick

[feelexit]

Joseph, Patrick, thank you so much guys. I really appreciate your
help. now I got it work. I still have one more question.


I tried both with selectmany and without selectmany, they both work.
however when I use nhibernate profiler to check the performance, they
both generate 7 select statements, I only have 2 reviews right now, if
leaders post alot reviews, just way too many queries.

here's my code.
1. without selectmany
------------------------------------------------------------------------------------------

var reviews = from user in _currentUser.Leaders
from review in user.Reviews

where review.SubmitDate >=
DateTime.Now.AddMonths(-1)
select new ReviewDetailViewModel
{
ReviewId = review.Id,
Title = review.Title,
Description = review.Description,
SubmitDate = review.SubmitDate,
Category = review.Category,
Age = review.Age,

UserId = review.SubmitBy.Id,
AccountType =
review.SubmitBy.AccountType,
Avatar = review.SubmitBy.Avatar,
NickName = review.SubmitBy.NickName,

FavoritesCount = review.Favorites.Count,
VoteCount = review.Votes.Count
};


2. with selectmany
------------------------------------------------------------------------------------------

var reviews = _currentUser.Leaders.SelectMany(l => l.Reviews)

.Where(r => r.SubmitDate >=
DateTime.Now.AddMonths(-1))
.Select(r => new
ReviewDetailViewModel
{

ReviewId = r.Id,

Title = r.Title,

Description = r.Description,

SubmitDate = r.SubmitDate,

Category = r.Category,

Age = r.Age,


UserId = r.SubmitBy.Id,

AccountType = r.SubmitBy.AccountType,

Avatar = r.SubmitBy.Avatar,

NickName = r.SubmitBy.NickName,


FavoritesCount = r.Favorites.Count,

VoteCount = r.Votes.Count
}).ToList();


they both generate 7 select statements. To keep this reply short, I
will post all 7 select statements in the next reply. I did some
research, the reason I have all those extra select statements, because
I also need total vote, and total number of favorites for each review,
that is extra 2 queries per review.


on the homepage, I have a similar query, I listed out the latest
reviews with total vote and total number of favorites. it only
generates one select statement, vote and favorites are subqueries. I
couldn't figure out why above queries not doing the same.

var reviews = from review in
_reviewRepository.GetAll().Where(m => m.Status ==
Status.Approved).OrderByDescending( m => m.SubmitDate)
select new ReviewDetailViewModel
{
ReviewId = review.Id,
Title = review.Title,
Description = review.Description,
SubmitDate = review.SubmitDate,
Category = review.Category,
Age = review.Age,

UserId = review.SubmitBy.Id,
AccountType =
review.SubmitBy.AccountType,
Avatar = review.SubmitBy.Avatar,
NickName = review.SubmitBy.NickName,

FavoritesCount =
review.Favorites.Count,
VoteCount = review.Votes.Count
};

here's the generated select statement, only one:

select review0_.Id as col_0_0_,
review0_.Title as col_1_0_,
review0_.Description as col_2_0_,
review0_.SubmitDate as col_3_0_,
review0_.Category as col_4_0_,
review0_.Age as col_5_0_,
user1_.Id as col_6_0_,
user1_.AccountType as col_7_0_,
user1_.Avatar as col_8_0_,
user1_.NickName as col_9_0_,
(select cast(count(*) as SIGNED)
from Favorite favorites2_
where review0_.Id = favorites2_.ReviewId) as col_10_0_,
(select cast(count(*) as SIGNED)
from Vote votes3_
where review0_.Id = votes3_.ReviewId) as col_11_0_
from Review review0_
left outer join User user1_
on review0_.SubmitBy = user1_.Id
where review0_.Status =1 /* ?p0 */
order by review0_.SubmitDate desc

[feelexit]

here's all 7 queries.

-- statement #1
SELECT leaders0_.Follower as Follower1_,
leaders0_.Leader as Leader1_,
user1_.Id as Id1_0_,
user1_.Email as Email1_0_,
user1_.Password as Password1_0_,
user1_.CreateDate as CreateDate1_0_,
user1_.Avatar as Avatar1_0_,
user1_.AccountType as AccountT6_1_0_,
user1_.OtherID as OtherID1_0_,
user1_.NickName as NickName1_0_,
user1_.ParentType as ParentType1_0_,
user1_.Status as Status1_0_,
user1_.Roles as Roles1_0_,
user1_.Point as Point1_0_,
user1_.Ip as Ip1_0_,
user1_.FirstName as FirstName1_0_,
user1_.MiddleName as MiddleName1_0_,
user1_.LastName as LastName1_0_
FROM Following leaders0_

left outer join User user1_

on leaders0_.Leader = user1_.Id
WHERE leaders0_.Follower =44 /* ?p0 */

-- statement #2
SELECT reviews0_.SubmitBy as SubmitBy1_,
reviews0_.Id as Id1_,
reviews0_.Id as Id3_0_,
reviews0_.Title as Title3_0_,
reviews0_.Description as Descript3_3_0_,
reviews0_.SubmitBy as SubmitBy3_0_,
reviews0_.SubmitDate as SubmitDate3_0_,
reviews0_.Category as Category3_0_,
reviews0_.Link as Link3_0_,
reviews0_.Status as Status3_0_,
reviews0_.Age as Age3_0_
FROM Review reviews0_
WHERE reviews0_.SubmitBy =55 /* ?p0 */

-- statement #3
SELECT favorites0_.ReviewId as ReviewId1_,
favorites0_.Id as Id1_,
favorites0_.Id as Id4_0_,
favorites0_.ReviewId as ReviewId4_0_,
favorites0_.PostBy as PostBy4_0_,
favorites0_.PostDate as PostDate4_0_
FROM Favorite favorites0_
WHERE favorites0_.ReviewId =45 /* ?p0 */

-- statement #4
SELECT votes0_.ReviewId as ReviewId1_,
votes0_.Id as Id1_,
votes0_.Id as Id0_0_,
votes0_.VoteBy as VoteBy0_0_,
votes0_.VoteDate as VoteDate0_0_,
votes0_.Score as Score0_0_,
votes0_.ReviewId as ReviewId0_0_
FROM Vote votes0_
WHERE votes0_.ReviewId =45 /* ?p0 */

-- statement #5
SELECT reviews0_.SubmitBy as SubmitBy1_,
reviews0_.Id as Id1_,
reviews0_.Id as Id3_0_,
reviews0_.Title as Title3_0_,
reviews0_.Description as Descript3_3_0_,
reviews0_.SubmitBy as SubmitBy3_0_,
reviews0_.SubmitDate as SubmitDate3_0_,
reviews0_.Category as Category3_0_,
reviews0_.Link as Link3_0_,
reviews0_.Status as Status3_0_,
reviews0_.Age as Age3_0_
FROM Review reviews0_
WHERE reviews0_.SubmitBy =45 /* ?p0 */

-- statement #6
SELECT favorites0_.ReviewId as ReviewId1_,
favorites0_.Id as Id1_,
favorites0_.Id as Id4_0_,
favorites0_.ReviewId as ReviewId4_0_,
favorites0_.PostBy as PostBy4_0_,
favorites0_.PostDate as PostDate4_0_
FROM Favorite favorites0_
WHERE favorites0_.ReviewId =44 /* ?p0 */

-- statement #7
SELECT votes0_.ReviewId as ReviewId1_,
votes0_.Id as Id1_,
votes0_.Id as Id0_0_,
votes0_.VoteBy as VoteBy0_0_,
votes0_.VoteDate as VoteDate0_0_,
votes0_.Score as Score0_0_,
votes0_.ReviewId as ReviewId0_0_
FROM Vote votes0_
WHERE votes0_.ReviewId =44 /* ?p0 */

[Patrick]

So, I was hoping somebody more knowledgeable would chime in with some insight but I'll take a stab.  Most of the data it is getting is necessary but I'm guessing the abundance of queries is just a shortcoming in the LINQ to nHibernate provider.  Your original query doesn't go very deep; you start with reviews and just go one level deep to get the Count and SubmitBy info.  I'm guessing since your other query is navigating down through follower->leader->review it just can't make the same intelligent decisions.  The difference in the way it handles Count points to that for me, but I don't really know what I'm talking about :)

One thing that might help, if I understand your query correctly, is it appears 'leader' is the same thing as 'review.SubmitBy.'  This would allow you to change those couple of parameters:

  UserId = leader.Id
  AccountType = leader.AccountType, 
  Avatar = leader.Avatar, 
  NickName = leader.NickName,

and might knock a level of queries off.  I've noticed in my app that a couple of operations execute a fair number of queries but since it hasn't adversely affected performance I haven't worried too much about it.  I would advise the same thing.  If this really is going to cause issues, you might be better off getting into HQL, QueryOver, etc. or some custom stored procedure and a Dto and AliasToBean, which I've used in a couple of situations.  

Thanks,

Patrick