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