Compare Array of IDs Entity Framework

Question

I have a simple scenario for which i want to write LINQ query, But i am unable to get it right. Here is the scenario:

I have 3 Tables:

STUDENT:
    --------------------------
    SID   Name
    ---------------------
    1     Jhon
    2     Mishi
    3     Cook
    4     Steven

COURSE:
    -------------------
    CID     Name
    -------------------
    1       Maths
    2       Physics
    3       Bio
    4       CS

STUDENTCOURSE:
    ---------------------------
    SCID   SID   CID
    -----------------------
    1       1     1
    2       1     2
    3       1     4
    4       2     1
    5       2     2
    6       2     3
    7       3     1
    8       3     4
    10      4     2

For this case i want to pass array of course ids to query and return all student those have all the these courses registered against them. What i tried:

 int[] cIds = {1,2,4 };
 var result = from s in context.Students
              where s.StudentCourses.Any(sc=> cIds.Contains(sc.CID))
              select s;

But this returns students those registered either of the course id 1,2,4. Hope you understand my problem.

Thanks for the help.

If I understand correctly, your current query gives you results for any student that has registered for courses 1 OR 2 OR 4. You would prefer to have results for students that have registered for all three courses? — Sam Axe
– Sam Axe, Commented Aug 13, 2015 at 9:02
I'll leave it to someone else to come up with the statement (I could do it easily in SQL, but my linq grouping is rusty). Select the SIDs from StudentCourse by grouping by SID, counting CID where in cIds, and having the count equal to the length of cIds. — Andy Nichols
– Andy Nichols, Commented Aug 13, 2015 at 9:14
@AndyNichols: sounds like a better solution than mine. :) Good job. — Sam Axe
– Sam Axe, Commented Aug 13, 2015 at 9:17
@T.Rahgooy I think my comment still stands. The "counting CID where in cIds" part means that any courses not in the cIds will be ignored. — Andy Nichols
– Andy Nichols, Commented Aug 13, 2015 at 10:23

Patrick · Accepted Answer · 2015-08-13 10:42:17Z

1

Try the following:

int[] cIds = {1,2,4 };
var result = from s in context.Students
             where cIds.All(id => s.StudentCourses.Any(sc=> sc.CID == id))
             select s;

answered Aug 13, 2015 at 10:42

Patrick

6684 silver badges12 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Patrick Over a year ago

It works perfectly if your model has either a navigation from Student to Course entity (navigate from Student to Courses instead of Student to StudentCourses) or a navigation from Student to StudentCourse with foreign keys included. Regarding your code, I assumed the last one.

Irfan TahirKheli Over a year ago

forgien key relation exists between student and studentcourses. so there is no issue with s.StudentCourses . but when i try it . it returns 0 results

Patrick Over a year ago

I receive 1 result (student "Jhon"). Did you exactly copy my where clause into your code? And is the array generated (if it's not hard coded) before executing the query?

Community · Accepted Answer · 2017-05-23 11:58:00Z

1

Use this:

int[] cIds = {1,2,4 };
var q = context.StudentCourses.Join(context.Students, 
                                    x => x.SId, 
                                    x => x.Id, 
                                    (sc, s) => new { Student = s, CourseId = sc.CId })
        .GroupBy(x => x.Student.Id)
        .Where(sc => cIds.All(cid => sc.Any(y => y.CourseId == cid)))
        .Select(x => x.FirstOrDefault().Student)
        .ToList();

Or if you prefer linq query:

int[] cIds = {1,2,4 };
var q2 = (from s in context.Students
          join sc in context.StudentCourses on s.Id equals sc.SId into sCources
          where cIds.All(id => sCources.Any(y => y.CId == id))
          select s).ToList();

Here is a fiddle for it, using linq-to-objects.

Edit:
I didn't notice that in your model there is a navigation property from Student to StudentCourse, in this case the query will be much simpler and does not need join, and Patrick's answer works perfectly.

edited May 23, 2017 at 11:58

CommunityBot

11 silver badge

answered Aug 13, 2015 at 9:38

Taher Rahgooy

6,7163 gold badges22 silver badges31 bronze badges

1 Comment

Irfan TahirKheli Over a year ago

Appreciated. but i didnt give it a try. Let me give it a try in while then i will give a feedback on it. thanks

Collectives™ on Stack Overflow

Compare Array of IDs Entity Framework

2 Answers 2

3 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related