Solved LINQ Query with Property of an Object in a Linked List as Criteria (1 Viewer)

[sonic8]

I’ve got two classes (Course, Person). The students enrolled in a course are stored in a List(of Person) of the Course class. Now I want to use LINQ to query a List(of Course) to retrieve only courses in which a Person of a certain name (e.g. “Jane”) is enrolled.

How to write a LINQ query to achieve this?

The following query should illustrate my intention, but obviously it does not work that way.

Dim selectedCourses As List(Of Course) =
            (From sc As Course In AllCourses
             Where sc.Students.Name = "Jane").ToList()

In this simplified example, it would be an alternative to just store the person name in a list of Strings and use Contains() to check for a certain String in the list. – This is not an option in the real application.

Below is the full code for a working (except for the LINQ query) VB.Net console application that reproduces the scenario described above.

Public Class Person
    Public Property PersonName As String
End Class
Public Class Course

    Public Property CourseName As String
    Public Property Students As New List(Of Person)
    Public Property Level As Integer

End Class

Module Module1

    Sub Main()

        Dim mike As New Person With {.PersonName = "Mike"}
        Dim jane As New Person With {.PersonName = "Jane"}
        Dim joe As New Person With {.PersonName = "Joe"}


        Dim AllCourses As New List(Of Course)
        Dim c As Course
        c = New Course With {.CourseName = "Math", .Level = 2}
        c.Students.AddRange({mike, jane})
        AllCourses.Add(c)

        c = New Course With {.CourseName = "Biology", .Level = 1}
        c.Students.AddRange({mike, jane, joe})
        AllCourses.Add(c)

        c = New Course With {.CourseName = "Chemistry", .Level = 2}
        c.Students.AddRange({mike, joe})
        AllCourses.Add(c)


        Dim selectedCourses As List(Of Course) =
            (From sc As Course In AllCourses
             Where sc.Students.Name = "Jane").ToList()

        For Each c In selectedCourses
            Console.WriteLine(c.CourseName)
        Next

        Console.WriteLine("Press ENTER to exit")
        Console.ReadLine()

    End Sub

End Module

 

[sonic8]

On Friday I had an meeting with mates from the Access-Profi-Pool (German language) and there 3 guys with hardly any LINQ knowledge but solid SQL knowledge guided me to a solution.

We came up with this as working solution:

Dim selectedCourses As List(Of Course) = _
            AllCourses.Where(Function(c1) c1.Students.Exists(Function(p) p.PersonName = "Jane")).ToList()

The core approach in the above can be rewritten to:

Dim selectedCourses As List(Of Course) =
            (From sc As Course In AllCourses
             Where sc.Students.Exists(Function(p) p.PersonName = "Jane")
            ).ToList()

This is pretty close to my original non-working approach and much more resembles a classic SQL query. So I like this much better.

 

[NauticalGent]

Good afternoon Philipp, until you posted this, I had never heard of LINQ query. Can you, if convenient for you, explain what you were doing what you were working with?

 

[sonic8]

Ok, on public demand of @NauticalGent, a little background information... ;-)

LINQ is short for Language INtegrated Query, a feature of the Microsoft .Net Framework. It allows to use a SQL-like syntax to query various types of lists and collections to retrieve either single objects or subsets of the original list. - For more info see the official Introduction to LINQ in Visual Basic.

To give you a clearer idea, why LINQ is very helpful for writing code for the .Net platform, lets take a closer look at my problem described in this thread.
Without using LINQ the solution to my problem, as described in the first post of this thread, would look like this:

        Dim selectedCourses As New List(Of Course)
        For Each sc As Course In AllCourses
            For Each p As Person In sc.Students
                If p.PersonName = "Jane" Then
                    selectedCourses.Add(sc)
                    Exit For
                End If
            Next
        Next

For comparison, here is the LINQ solution again:

    Dim selectedCourses As List(Of Course) =
            (From sc As Course In AllCourses
             Where sc.Students.Exists(Function(p) p.PersonName = "Jane")
            ).ToList()

I like the LINQ solution much better, because I can understand the intention of the code much quicker. However, the difference is not that huge.

Let's make the problem more complex by inverting my original question: I now want to list all courses in which NO student named "Jane" is enrolled in.
Let's look at the LINQ solution first:

Dim selectedCourses As List(Of Course) =
            (From sc As Course In AllCourses
             Where Not sc.Students.Exists(Function(p) p.PersonName = "Jane")
            ).ToList()

That was trivial. I just added the Not keyword to invert the criterion.

Now, for comparison the classic, iterative approach:

        Dim selectedCourses As New List(Of Course)
        Dim AddToSelection As Boolean
        For Each sc As Course In AllCourses
            AddToSelection = True
            For Each p As Person In sc.Students
                If p.PersonName = "Jane" Then
                    AddToSelection = False
                    Exit For
                End If
            Next
            If AddToSelection Then
                selectedCourses.Add(sc)
            End If
        Next

This is already pretty nasty, even though the problem to solve was still fairly simple.
EDIT
Yes, I could have copied the original list to the selection and then remove unwanted items from the list. This would look very similar to the previous solution. However, this stops to be an alternative, once we have a combination of match and not-mach criteria.
/EDIT
In general, the more complex the "query" is, the shorter and more readable/understandable the LINQ alternative usually gets compared to an iterative approach.


Finally, a little background information on the project I use this in. I'm implementing a calculation engine for some financial calculations which do not lend themselves to be easily implemented in T-SQL. For each scenario I need about 1000 to 25000 records from a SQL Server database. This selection of records will be used over and over and over again during the calculation. So, instead of hitting the database thousands of times to query the values, I load all those records into memory in List(of MyDataType) objects just once and then query those lists. After the calculation is complete, I write the ~100 resulting records back to the database.
My calculation engine will initially be hosted in the CLR in SQL Server, but if demand arises, I can easily move it out of the SQL Server process, even to different computers (plural!) and then could spin up hundreds of instances of the calculation engine without putting much load on the database sever.