CODEWITHSUNDEEP
c#sqlentity-frameworklinqlinq-to-sql
Wai Yan Maung Oo
Wai Yan Maung Oo

Reputation: 31

How to retrieve a single record using a foreign key column in LINQ?

I have List "StudentList" (have more than one records) I have another list "CourseList" (contain more than one records) StudentList (1) to (*) CourseList [Relationship]

I want to retrieve a record with courseID (column from CourseList) = 1000;

What I have Tried but unable to execute.

        var viewmodel = StudentList.Select(x => x.CourseList.Where(y => y.courseID == "1000"));

Thanks in advance!

Upvotes: 0

Views: 144

Answers (2)

BDB
BDB

Reputation: 459

Here is the answer you were looking for,

var viewmodel = StudentList.SelectMany(x => x.CourseList.Where(y => y.courseID == "1000")).FirstOrDefault();

Since you showed interest on how it's working and what selectMany does, I tied explaining a bit further though I have already answered your question in comments section,

  1. First thing to notice, x.CourseList.Where(y => y.courseID == "1000") section will return list of courses whose id is 1000. Even if there is only one course, it will be list.

  2. If we are getting list of courses whose id is 1000 for each individual student, we will end up getting List of List of courses(while using select).

  3. So, here selectMany flattens the 'List of List of courses' to 'List of courses' by merging all inner courses.

  4. At last, FirstOrDefault() will make sure to return the single course object whose id is 100 and which is the required result for you.

Note: though I have used the term List, you may be dealing with IEnumerable

Hope you and anybody else landing here find this helpful.

Upvotes: 3

ichPotatodeCXY
ichPotatodeCXY

Reputation: 30

you can try this method

var result = students.Where(x=>isExistCourse(x));
public static bool isExistCourse(Student student)
    {
        foreach(var item in student.Courses)
        {
            if(item.Id==1000)
                return true;
        }
        return false;
    }

if you only want to get one record, you can replace where to FirstOrDefault

Upvotes: 0

Related Questions