convert sql to linq help please

Question

select m.messageID,m.[addeddate],m.message,count(*) as Comments 
from intranet.dbo.Blog_Messages AS m inner join 
intranet.dbo.Blog_Comments AS c on c.messageid = m.messageid
group by m.messageID,m.[addeddate],m.message

need help converting this to linq to sql

Answer 1

from m in context.Blog_Messages
select new
{
   MessageId = m.MessageID,
   AddedDate = m.AddedDate,
   Message = m.Message,
   Comments = m.Blog_Comments.Count()
}

It isn't a direct conversion, but I think that is what you are after.

Answer 2

    Dim Msg = From m In db2.Blog_Messages _
        Group Join c In db2.Blog_Comments On m.MessageID Equals c.MessageID Into Comments = Group _
        Join u In db2.users On m.userID Equals u.userid _
        Select New With {
           m.Title, m.Message, m.MessageID, 
          .CommentCount = Comments.Count(), 
          .date1 = m.AddedDate.ToShortDateString, 
          .Time = m.AddedDate.ToShortTimeString(), 
          .fullName = u.fname & " " & u.lname
        }

this is what I came up with..

thanks, chris

Answer 3

from m in db.Blog_Messages
join c in db.Blog_Comments on m.MessageID equals c.MessageID
group m by new {
  m.MessageID,
  m.AddedDate,
  m.Message
} into g
select new {
  MessageID = g.Key.MessageID,
  AddedDate = g.Key.AddedDate,
  g.Key.Message,
  Comments = g.Count()
}