CODEWITHSUNDEEP
c#linqentity-framework-core
noblerare
noblerare

Reputation: 11903

LINQ - How do I guarantee only one matching entry returned satisfying a certain criteria?

Let's say I have a SQL table that represents college courses:

|--------|------------------|------------------|
|  Id    |     StudentId    |     CourseName   |
|--------|------------------|------------------|
|  1     |        15        |    Biology 101   |
|--------|------------------|------------------|
|  2     |        21        |    English 201   |
|--------|------------------|------------------|
|  3     |        38        |    History 301   |
|--------|------------------|------------------|
|  4     |        41        | Anthropology 401 |
|--------|------------------|------------------|
|  5     |        15        |   Graphics 210   |
|--------|------------------|------------------|
|  6     |        21        |  Physics Lab B   |

It has a student Id that represents that this student is taking this college course. As you can see, a student can take multiple courses.

Now, let's say that I have a list of student Ids like so: [15, 21]

I want to write a LINQ statement that will give me only one course (It can be the first but it doesn't have to be) for each student id in my list.

So, in my example, I will get courses 1 and 2 back. But it could be courses 2 and 5 or courses 5 and 6. It doesn't matter. I just need to guarantee that one and only one course for each student id in my list is returned. I am also guaranteed that a course will exist for student id.

Short of writing direct SQL, how would I do this in Entity Framework/LINQ?

Upvotes: 0

Views: 48

Answers (1)

jdweng
jdweng

Reputation: 34421

Try following :

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace ConsoleApplication1
{
    class Program
    {
        static void Main(string[] args)
        {
            DataBase db = new DataBase()
            {
                students = new List<Students>() {
                    new Students() { Id = 1, StudentId = 15, CourseName = "Biology 101"},
                    new Students() { Id = 2, StudentId = 21, CourseName = "English 201"},
                    new Students() { Id = 3, StudentId = 38, CourseName = "History 301"},
                    new Students() { Id = 4, StudentId = 41, CourseName = "Anthropology 401"},
                    new Students() { Id = 5, StudentId = 15, CourseName = "Graphics 210"},
                    new Students() { Id = 6, StudentId = 21, CourseName = "Physics Lab B"}
                }
            };

            List<int> searchIds = new List<int>() { 15, 21 };

            List<Students> results = db.students.Where(x =>  searchIds.Contains(x.StudentId))
                .GroupBy(x => x.StudentId)
                .Select(x => x.FirstOrDefault())
                .ToList();

        }
    }
    public class DataBase
    {
        public List<Students> students { get;set;}
    }
    public class Students
    {
        public int Id { get; set; }
        public int StudentId { get; set; }
        public string CourseName { get; set; }
    }

}

Upvotes: 1

Related Questions