I have this query which I retrieve from 6 tables which gets the dates, rated, rater, classname, scores, subject.
我有这个查询,我从6个表中检索,获取日期,评级,评分,类别,分数,主题。
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.
我必须将此查询转换为LINQ表达式。我很难将我的SQL查询转换为LINQ。任何帮助,将不胜感激。
2 个解决方案
#1
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
};
#2
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
选择不同的subcat作为CatID,CatName,CatID作为subcat,Subcategory from(选择a.CatName,b.CatName作为Subcategory,a.CatID作为subcat,b.CatID来自Iris_CategoryMaster,内部联接Iris_CategoryMaster b on a.CatID = b.ParentID其中b.Status = 1)为x
#1
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
};
#2
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
选择不同的subcat作为CatID,CatName,CatID作为subcat,Subcategory from(选择a.CatName,b.CatName作为Subcategory,a.CatID作为subcat,b.CatID来自Iris_CategoryMaster,内部联接Iris_CategoryMaster b on a.CatID = b.ParentID其中b.Status = 1)为x