Help Converting T-SQL Query to LINQ Query

Question

I am new to LINQ, and so am struggle over some queries that I'm sure are pretty simple. In any case, I have been hiting my head against this for a while, but I'm stumped.

Can anyone here help me convert this T-SQL query into a LINQ query? Once I see how it is done, I'm sure I'll have some question about the syntax:

SELECT BlogTitle
FROM Blogs b
JOIN BlogComments bc ON
  b.BlogID = bc.BlogID
WHERE b.Deleted = 0
  AND b.Draft = 0
  AND b.[Default] = 0
  AND bc.Deleted = 0
GROUP BY BlogTitle
ORDER BY MAX([bc].[Timestamp]) DESC

Just to show that I have tried to solve this on my own, here is what I've come up with so far, though it doesn't compile, let alone work ...

var iqueryable = 
    from blog in db.Blogs
    join blogComment in db.BlogComments on
        blog.BlogID equals blogComment.BlogID
    where blog.Deleted == false
        && blog.Draft == false
        && blog.Default == false
        && blogComment.Deleted == false
    group blogComment by blog.BlogID into blogGroup
    orderby blogGroup.Max(blogComment => blogComment.Timestamp)
    select blogGroup;

Answer 1

I think I worked it out, in case this will be helpful for anyone else ...

var iQueryable = 
 from blog in db.Blogs
 join blogComment in db.BlogComments on
     blog.BlogID equals blogComment.BlogID
 where blog.Deleted == false
     && blog.Draft == false
     && blog.Default == false
     && blogComment.Deleted == false
 group blogComment by blog.BlogTitle into blogGroup
 let maxTimeStamp = blogGroup.Max(blogComment => blogComment.Timestamp)
 let commentCount = blogGroup.Count()
 orderby maxTimeStamp descending
 select new RecentlyCommentedBlog() { BlogTitle = blogGroup.Key, CommentCount = commentCount };

I am trying to return the result of this query as an IQueryable object. I couldn't work out any way of doing that without creating a new class, just for this query, called RecentlyCommentedBlog. Here is the code for that class:

public class RecentlyCommentedBlog
{
    public string BlogTitle { get; set; }
    public int CommentCount { get; set; }

    public RecentlyCommentedBlog() { }

    public RecentlyCommentedBlog(string blogTitle, int commentCount)
    {
        BlogTitle = blogTitle;
        CommentCount = commentCount;
    }
}

Anyway, while this solution seems to be working, I can't help but think there is sure to be a better way to have accomplished this.

Answer 2

from b in db.Blogs
where !b.Deleted
   && !b.Draft
   && !b.Default
   && !b.Deleted
order by b.BlogComments.Max(bc => bc.Timestamp) descending
select new {Blog = b, Count = b.BlogComments.Count()}

If you want to return an IQueryable<T>, you'll have to project into a class such as your RecentlyCommentedBlog. An anonymous class (what my query shows), does not lend itself to being named as a return type from a method.

If you don't have a BlogComments property on your Blog class, create an association.