LINQ to SQL Group by C# problem

Question

I recently started to use LINQ to SQL and i have a minor complex query i need help with. I've got a table in my database called MovieComment, with the following columns:

• CommentID
• UserID
• MovieID
• Comment
• Timestamp

So, what i wanna do is to group the comments on MovieID and save them into my object called Movie, where the MovieID is being saved in the MovieID post, and the Linq object is saved inside the ObservableCollection inside the Movie object.

public class Movie
{
    #region Member Variables

    public int MovieID { get; set; }
    public string Title { get; set; }
    public string Content { get; set; }
    public Uri Poster { get; set; }
    public double Rating { get; set; }
    public DateTime Timestamp { get; set; }

    public ObservableCollection<MovieComment> Comments { get; set; } // Linq object: MovieComment

    #endregion // Member Variables
}

I've come up with the following linq query where i get the MovieID, but i dont really know how i should proceed to get a hold of all the other data

public ObservableCollection<Movie> LoadMovieID(int _userID, int _limit)
{
    ObservableCollection<Movie> movies = new ObservableCollection<Movie>();

    var query = (from mc in db.MovieComment
            where mc.UserID == _userID
            orderby mc.Timestamp descending
            group mc by mc.MovieID into movie
            select new
            {
                MovieID = movie.Key,
            }).Take(_limit);

    foreach (var row in query)
    {
        Movie movie = new Movie();
        movie.MovieID = row.MovieID;

        // I want to get the following:
        // movie.MovieComment = MovieComment-objects with the MovieID == row.MovieID

        movies.Add(movie);
    }

    return movies;
}

Is this even possible in a single query? Thankful for all the help i can get

Answer 1

Well, you could try this:

var query = (from mc in db.MovieComment
        where mc.UserID == _userID
        orderby mc.Timestamp descending
        group mc by mc.MovieID).Take(_limit);

That will give you a IGrouping<MovieComment, string> (or whatever your types are) which should let you get at all the comments without any extra work. It's certainly okay in terms of LINQ itself, but whether it will do what you want within LINQ to SQL, I'm not sure.