CODEWITHSUNDEEP
linqc#-4.0asp.net-mvc-4asp.net-web-api
Ayman H
Ayman H

Reputation: 185

LINQ query JOIN two tables for Web API controller method

I am really poor at LINQ and can't figure out a simple problem. I have a MVC Web API that has a controller. I have a method inside the controller to return back data for comments entered by user for an item.

The data structure is simple - Comments and User tables with UserID column acting as the foreign key

To solve the problem, I have the following method, which has a LINQ query to do a join between Comments and User tables and return back an object in a new extended object that combines the Comments and User details. I cant seem to grab data from the User table. Can someone please help?

public IQueryable<CommentsWithUserDetails> GetReviewsWithUserByItem(int ID)
    {
        var query = from x in db.Comments
                    join y in db.Users on x.CommentsUserID equals y.UserID into z
                    where x.CommentsItemID.Equals(ID)
                    select new CommentsWithUserDetails
                    {
                        CommentsUserID = x.CommentsUserID,
                        CommentsText = x.CommentsText,
                        CommentsRating = x.CommentsRating,
                        CommentsDate = x.CommentsDate,
                        UserFirstName = y.FirstName,
                        UserLastName = y.LastName,
                        UserPictureURL = y.PictureURL
                    };

        return query;
    }

Upvotes: 2

Views: 9571

Answers (1)

Ayman H
Ayman H

Reputation: 185

The solution is just to remove the 'into z' part from the query, yes as simple as that!

As pointed by @Nilesh and @Gert Arnold

Upvotes: 4

Related Questions