CODEWITHSUNDEEP
c#linq
Mikolaj
Mikolaj

Reputation: 3

How to get proper data using LINQ Lambda expression with many to many relation

Looks like a very simple case, but I can't find a proper lambda expression for it in my head or in stackoverflow :( I appreciate all help.

Case looks simple. I have two classes and relation many to many between them.

public class Student
    {
        public int Id { get; set; }
        public string Name { get; set; }

        public List<Document> Documents { get; set; }
    }

    public class Document
    {
        public int Id { get; set; }
        public string Name { get; set; }

        public List<Student> Students { get; set; }
    }

Then I initiate some values:

        Student st1 = new Student { Id = 1, Name = "Student 1" };
        Student st2 = new Student { Id = 2, Name = "Student 2" };
        Student st3 = new Student { Id = 3, Name = "Student 3" };

        List<Student> listStudent12 = new List<Student>(); 
        listStudent12.Add(st1); 
        listStudent12.Add(st2);

        List<Student> listStudent23 = new List<Student>();
        listStudent23.Add(st2); 
        listStudent23.Add(st3);


        Document doc1 = new Document { Id = 1, Name = "doc 1", Students = listStudent12 };
        Document doc2 = new Document { Id = 2, Name = "doc 2", Students = listStudent23 };

        List<Document> listDocs = new List<Document>();
        listDocs.Add(doc1);
        listDocs.Add(doc2);

Now I would like to get a list of documents using linq lambda expression, which are related to "Student 3" (id: 3).

I tried like this:

var result = listDocs.Where(d => d.Students.FirstOrDefault().Id == 3).ToList();

but it returns null (I guess I know why - the first returned student is not equal to 3).

I expect the result to contain all documents which has in list of students student with Id = 3. I stucked here and need a help. Thank you in advance for any.

On the SQL level I would go:

SELECT Document.* from Document, DocumentStudent WHERE Document.Id = DocumentStudent.DocumentId AND DocumentStudent.StudentId = 3

Upvotes: 0

Views: 335

Answers (2)

mwilczynski
mwilczynski

Reputation: 3082

What you did in your example was finding any Document that has first Student with Id: 3. That ends up with none.

What you want to do is:

var result = listDocs.Where(doc => doc.Students.Any(st => st.Id == 3).ToList();

It will evaluate to any Document that has at least one Student with Id: 3.

Upvotes: 1

adeel41
adeel41

Reputation: 3321

I think it will look simpler if you use the following syntax

var result = (from doc in listDocs
              from student in doc.Students
              where student.Id == 3
              select doc).ToList();

Upvotes: 0

Related Questions