Linq can these sub queries be optimised?

This query takes a group of comments, then counts their upvotes and downvotes in the tblCommentVotes table.

At the moment, it counts these via the select开发者_JAVA技巧 new statement, in the form of a subquery. Would this be more efficient if it was in some sort of group by in the main query? Also if it would, could anyone show me how to do this, as I can't work out how you would do this.

// Get comments
var q = (
    from C in db.tblComments
    where
        C.CategoryID == Category &&
        C.IdentifierID == Identifier
    join A in db.tblForumAuthors on C.UserID equals A.Author_ID
    orderby C.PostDate descending
    select new
    {
        C,
        A.Username,
        UpVotes = (from V in db.tblCommentVotes where V.CommentID == C.ID && V.UpVote == true select new { V.ID }).Count(),
        DownVotes = (from V in db.tblCommentVotes where V.CommentID == C.ID && V.UpVote == false select new { V.ID }).Count()
    }
)
.Skip(ToSkip > 0 ? ToSkip : 0)
.Take(ToTake > 0 ? ToTake : int.MaxValue);

What you need to do is to do an left outer join of the db.tblCommentVotes in the query expression, cause probably there might be no commentVotes?

When you have that, you should be able to perform ONE query in order to get your result.

It might look like this:

var q = (
   from C in db.tblComments
   where
      C.CategoryID == Category &&
      C.IdentifierID == Identifier
   join A in db.tblForumAuthors on C.UserID equals A.Author_ID
   // the following two lines are the left outer join thing. 
   join voteTemp in db.tblCommentVotes on voteTemp.CommentID equals C.ID into voteJoin
   from vote in voteJoin.DefaultIfEmpty()
   orderby C.PostDate descending
   group C by new { Comment = C, Username = A.Username } into g
   select new
   {
      g.Key.Comment,
      g.Key.Username,
      UpVotes = g.Count(x => x.UpVote),
      DownVotes = g.Count(x => !x.UpVote)
   }
)
.Skip(ToSkip > 0 ? ToSkip : 0)
.Take(ToTake > 0 ? ToTake : int.MaxValue);

This is untested and might not even compile, but I think it should be something like this.

db.tblComments.Where(c => c.CategoryID == Category && c.IdentifierID == Identifier)
              .Join(db.tblForumAuthors, c => c.UserID, a => a.Author_ID,
                     (c, a) =>
                     new
                     {
                        CommentID = c,
                        AuthorName = a.UserName,
                        UpVotes = c.Join(db.tblCommentVotes, c => c.CommentID
                                                             v => v.CommentID,
                                                             (c, v) => v).Count(v => v.UpVote)
                        DownVotes = c.Join(db.tblCommentVotes, c => c.CommentID
                                                              v => v.CommentID,
                                                              (c, v) => v).Count(v => v.DownVote)
                     });

To optimise it's best first to measure.

• Try, using something like LinqPad to view the generated SQL
• Then use SQL Server Management Studio to see the query plan for that SQL

or:

• Try running the code and seeing what SQL trace tells you is happening

Without the DB, it's quite hard (but fun) to guess whether that Linq will result in a single query or in multiple queries for working out the UpVotes and DownVotes. My guess is that calculating the UpVotes and DownVotes this way could be quite expensive - it may result in 2 additional queries per comment.

http://www.thereforesystems.com/view-query-generate-by-linq-to-sql/

without analyzing whats being output this question is impossible to answer.. however the link provided above should give you the tools necessary to perform this analysis yourself.