CODEWITHSUNDEEP
c#sql-serverlinqlambdalinq-to-sql
Rooter
Rooter

Reputation: 383

How can I convert SQL to lambda expressions

In my database, I created the tables structure as follows.

CREATE TABLE Course 
(
    Course_ID int IDENTITY(1,1) PRIMARY KEY,
    Name varchar(255) NOT NULL,
);

CREATE TABLE Student 
(
    Stu_ID int IDENTITY(1,1) PRIMARY KEY,
    Name varchar(255) NOT NULL,
    Mobile varchar(255),
    Age int,
    Course_ID int,
    FOREIGN KEY (Course_ID) REFERENCES Course(Course_ID)
);

CREATE TABLE Subject  
(
    Sub_ID int IDENTITY(1,1) PRIMARY KEY,
    Name varchar(255) NOT NULL,
);

CREATE TABLE Teacher 
(
    Teach_ID int IDENTITY(1,1) PRIMARY KEY,
    Name varchar(255) NOT NULL,
    Mobile varchar(255)
);

CREATE TABLE Course_Subject 
(
    CouSub_ID int IDENTITY(1,1) PRIMARY KEY,
    Course_ID int,
    Sub_ID int,
    FOREIGN KEY (Course_ID) REFERENCES Course(Course_ID),
    FOREIGN KEY (Sub_ID) REFERENCES Subject(Sub_ID)
);

CREATE TABLE Teacher_Subject 
(
    TeachSub_ID int IDENTITY(1,1) PRIMARY KEY,
    Teach_ID int,
    Sub_ID int,
    FOREIGN KEY (Teach_ID) REFERENCES Teacher(Teach_ID),
    FOREIGN KEY (Sub_ID) REFERENCES Subject(Sub_ID)
);

Now my problem is I need to retrieve students data who learned from some teacher, which means need to retrieve some teacher's students who learned from his/her. To accomplish my requirement. I write this SQL query. 

select 
    s.*
from 
    tbl_student s
inner join 
    Course_Subject tcs on s.Course_Id = tcs.Course_Id
inner join 
    Teacher_Subject tst on tst.Sub_ID = tcs.Sub_ID
inner join 
    Teacher t on t.Teach_ID = tst.Teach_ID
where 
    t.Teach_ID = @SomeTeacherId

Now I need to convert this query to a lambda expression or Linq. How can I do it? Please help me. Have any possible way to generate this using Visual Studio.

Upvotes: 1

Views: 7013

Answers (3)

Jitendra Gupta
Jitendra Gupta

Reputation: 824

Lambda:

Students
.Where(x=> x.Course.Course_Subjects
        .Any(y => y.Subject.Teacher_Subjects
                .Any(z => z.Teach_ID == someTeacherId)
        )
)
.Select(x => x)

Upvotes: 1

Vlad
Vlad

Reputation: 86

Well, you could use EF to generate object mapping to your tables. And use LINQ to rewrite your query with a slightly different syntax:

var result = from students in tbl_student
             join subjects in Course_Subject on students.Course_Id == subjects.Course_Id
             join ts in Teacher_Subject on subjects.Sub_ID == ts.Sub_ID
             join teachers in Teacher on teachers.Teach_ID == ts.Teach_ID
             where teachers.Teach_ID == "your_value"
             select students;

Not sure it's an absolutely correct query, but I hope you'll get the main idea.

Upvotes: 2

Anas Alweish
Anas Alweish

Reputation: 3026

Have any possible way to generate this using Visual Studio.?

Yes, you can do this using Linq-to-SQL

for your query, this might be appropriated

var students = from student in db.Students
            join tcs in db.CourseSubjects on student.CourseId equals tcs.CourseId
            join tst in db.TeacherSubjects on tcs.SubId equals tst.SubId
            join t in db.Teachers on tst.TeachId equals t.TeachId
            where t.TeachId == someTeacherId
            select student;

Upvotes: 2

Related Questions