Question 86494 in ASP.NET Question posted in
The official documentation can be found here.

Searching, return a distinct list of classes that the students are in – Asp.net

I am building an ASP.NET MVC app using C#. I have two Azure SQL tables, Students and Classes.

I have built the search functionality to search classes and return the results.

Now I need to through students into the mix. I would like to search students using .Contains and return a distinct list of classes that the students are in.

For example:

STUDENTS

Students Table Screenshot

CLASSES

Classes Table Screenshot

  • If I search for "Tom", then Computer Engineering and Data Science are returned.
  • If I search for "Chris", then Computer Engineering and Data Science are returned.
  • If I search for "Bill", then Computer Engineering is returned.

No duplicate class names should be returned.

Here is the code snippet in my controller as it stands.

public async Task<IActionResult> Index(string searchString)
{
    var classes = from m in _context.ClassesClass
                  select m;

    if (!String.IsNullOrEmpty(searchString))
    {
        classes = classes.Where(s => s.CLASS_NAME.Contains(searchString));
    }

    return View(await classes.ToListAsync());
}

How do I approach this?

Tags:

2

Answers


  1. 0

    If your entities are setup correctly with the relationship, your query should look something like:

    var classes = await _context.Students.Where(s => s.Contains(searchString)).Select(s => s.Class.CLASS_NAME).Distinct().ToListAsync();

    Your table structure isn’t nice as each student should have one record and the joining of students and classes should be in a separate joining table, e.g.:

    STUDENTCLASSES
| StudentID  | ClassID |
| ---------- | ------- |
| 1          | 1       |
| 1          | 2       |
| 2          | 1       |
    Login or Signup to reply.
  2. 0

    To avoid the result returning a duplicate of the class, you need to group by expression.

    var students = _context.Students
    .AsQueryable();

if (!String.IsNullOrEmpty(searchString))
{
    students = students.Where(s => s.StudentName.Contains(searchString));
}

var classes = (from a in students
        join b in _context.ClassesClass on a.ClassId equals b.ClassId
        group b by b.ClassId into g
        select g.Select(x => x).FirstOrDefault()
    ).ToList();

    Demo @ .NET Fiddle

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search