Convert SQL query into Linq expression

Question

There are tables Subject, Student and SubjectEnrolled

Subject table have two columns SubjectId and SubjectName
SubjectEnrolled table also have two column StudentID(foreign key to StudentTable) and SubjectId(foreign key to Subject)

I want to convert this SQL query

SELECT SubjectName
FROM Subject
WHERE SubjectId IN 
(
    SELECT SubjectId
    FROM SubjectEnrolled
    WHERE StudentID=7
)

Into a Linq or Lamda expression

using (var db = new DbContext())
{
    var res = from  r in db.Subjects.....
}

Please check for this question: https://stackoverflow.com/questions/37324/what-is-the-syntax-for-an-inner-join-in-linq-to-sql — Pimenta, Jun 22 '20 at 11:44
Does this answer your question? [What is the syntax for an inner join in LINQ to SQL?](https://stackoverflow.com/questions/37324/what-is-the-syntax-for-an-inner-join-in-linq-to-sql) — Pimenta, Jun 22 '20 at 11:45

score 1 · Accepted Answer · answered Jun 22 '20 at 10:54

1 - SQL : use inner join instead IN :

SELECT SubjectName FROM Subject sub
INNER JOIN SubjectEnrolled subEn on sub.SubjectId = subEn.SubjectId
WHERE subEn.StudentID = 7

2 - Linq Query Join:

var res = (from sub in db.Subjects
          join subEn in db.SubjectEnrolleds on sub.SubjectId equals subEn.SubjectId
          where subEn.StudentID = 7).ToList();

I hope you find this helpful.

Convert SQL query into Linq expression

1 Answers1