How convert sql query to linq-to-sql

Question

I have Sql query:

SELECT News.NewsId, News.Subject, Cm.Cnt FROM dbo.News
LEFT JOIN 
(SELECT Comments.OwnerId, COUNT(Comments.OwnerId) as Cnt 
 FROM Comments 
 WHERE Comments.CommentType = 'News' 
 Group By Comments.OwnerId) Cm
ON Cm.OwnerId = News.NewsId

But I want linq-to-sql query, how I can convert this to linq?

Answer 1

You might find it helpful to download LinqPad, which will make it extremely easy to write LINQ and test it. It will also help you learn the syntax. Awesome tool. And it's free.

Answer 2

how I can convert this to linq?

Practice! :)

from news in News
let count = news.Comments
  .Where(comment => comment.CommentType == "News")
  .Count()
select new {news.NewsId, news.Subject, Count = count};