Convert sql query with multiple inner joins to LINQ

Question

I have this query which I retrieve from 6 tables which gets the dates, rated, rater, classname, scores, subject.

SELECT r.date, u1.username as rated, u2.username as rater, c.name as classname, s.ratings, sbj.name
FROM Ratings r
 INNER JOIN Users u1 ON u1.userid = r.rated 
 INNER JOIN Users u2 ON u2.userid = r.rater 
 INNER JOIN ClassMembers cm ON u1.userid  = cm.userid
 INNER JOIN Class c ON cm.teamid = c.teamid
 INNER JOIN Scores s ON s.ratingsid = r.ratingsid 
 INNER JOIN Subjects sbj ON sbj.subjectid = s.subjectid

this results in

date         | rated | rater | teamname | score |  subject
10/12/2012    john     mike     teamA      9        Math
10/09/2012    john     mike     teamA      9        Science
10/09/2012    john     abra     teamA      5        Math
10/09/2012    john     abra     teamA      5        Science

I have to convert this query to a LINQ expression. I'm having a hard time converting my sql query into LINQ. Any help would be appreciated.

Reed Copsey · Accepted Answer · 2012-10-24 16:22:40Z

3

You could do this via join:

var results = from r in Ratings
              join u1 in Users on u1.userid = r.rated
              join u2 in Users on u2.userid = r.rater
              join cm in ClassMembers on cm.userid = r.rated
              join c in Class on cm.teamid = c.teamid
              join s in Scores on s.ratingsid = r.ratingsid
              join sbj in Subjects on sbj.subjectid = s.subjectid
              select new 
                     {
                        Date = r.date, 
                        Rated = u1.username,
                        Rater = u2.username,
                        ClassName = c.name,
                        Ratings = s.ratings,
                        Subject = sbj.name
                      };

answered Oct 24, 2012 at 16:22

Reed Copsey

567k80 gold badges1.2k silver badges1.4k bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

shinra tensei Over a year ago

Thanks! i tried something like that before i must've swapped the on equals. But this works perfect! You are the man!

shinra tensei Over a year ago

do you know how to group these by ClassName c.name?

user3297543 · Accepted Answer · 2014-03-20 06:01:09Z

0

select distinct subcat as CatID,CatName,CatID as subcat,Subcategory from (select a.CatName,b.CatName as Subcategory,a.CatID as subcat,b.CatID from Iris_CategoryMaster a inner join Iris_CategoryMaster b on a.CatID=b.ParentID where b.Status=1) as x

answered Mar 20, 2014 at 6:01

user3297543

1

Collectives™ on Stack Overflow

Convert sql query with multiple inner joins to LINQ

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related