2

I have three entities as shown below.

Student { StudentID, Name, Age } 

Parent { ParentID, Name, Age } 

StudentParent { StudentParentID, StudentID, ParentID }

I need to get an IQueryable list of students that are a certain age and have no parents. I am currently using the following code which does work.

IQueryable<Student> Student s = from s in db.Students
                                 where s.Age == 18
                                 where !(from sp in db.StudentParent where sp.StudentID == s.StudentID select sp.StudentID).Contains(s.StudentID)
                                 select s;

I would just like help converting this to a Lambda expression.

Lion
  • 18,729
  • 22
  • 80
  • 110
Chad Myers
  • 106
  • 1
  • 7
  • There was a similar question that was asked a bit ago: http://stackoverflow.com/questions/3739246/linq-to-sql-not-contains-or-not-in. The answer is a bit more concise than the answers so far. – Tieson T. Dec 18 '11 at 02:54

2 Answers2

4

You should create an association in your entity model.

You can then write

db.Students.Where(s => s.Age == 18 && s.Parents.Count == 0)

You should never need to explicitly query a join table (such as your StudentParent) when using an ORM.

SLaks
  • 868,454
  • 176
  • 1,908
  • 1,964
2

This should work:

db.Students.Where( s =>
    s.Age == 18 &&  db.StudentParent.All(sp => sp.StudentID != s.StudentID)
);

Edit: This assumes that you do not have a link from students to parents; if you do, use it instead of a join for better readability.

Sergey Kalinichenko
  • 714,442
  • 84
  • 1,110
  • 1,523
  • this worked and I only made on small change Thanks again db.Students.Where( s => s.Age == 18 && s.StudentParent.All(sp => sp.StudentID != s.StudentID) ); – Chad Myers Dec 18 '11 at 06:03