Entity Framework How can I filter my results by a property of a navigation property?

Question

I have a legacy class database that is represented by the following model.

public class Course
{
    public int Id { get; set; }
    public string Title { get; set; }
    public string Description { get; set; }
    public CourseLevel Level { get; set; }
    public float FullPrice { get; set; }
    public Author Author { get; set; }

    public IList<Tag> Tags { get; set; }
    public IList<Attendee> Attendees { get; set; }
}

public class Attendee
{
    public int Id { get; set; }
    public int StudentId { get; set; }
    public decimal Tuition { get; set; }

    public Student Student { get; set; }
}

public class Student
{
    public int Id { get; set; }
    public string Name { get; set; }
    public string Address { get; set; }
}

public class Author
{
    public int Id { get; set; }
    public string Name { get; set; }
    public IList<Course> Courses { get; set; }
}

public class Tag
{
    public int Id { get; set; }
    public string Name { get; set; }
    public IList<Course> Courses { get; set; }
}

I need to get a list of classes that either a part of the course title or description or a part of a student's name matches my search string. The first part is easy.

List<Course> courses = db.Courses.Where(w => w.Title.IndexOf(searchString) > -1 || w.Description.IndexOf(searchString) > -1).ToList();

How do I now filter against w.Attendees.Student.Name?

I tried:

List<Course> courses = db.Courses
    .Where(w => w.Title.IndexOf(searchString) > -1 || 
           w.Description.IndexOf(searchString) > -1 ||
           w.Attendees.Any(a => a.Student.Name.IndexOf(searchString) > -1)).ToList();

And it just returns an empty list.

I'm still kind of new to Linq, I'm coming from Grails. Any help is appreciated.

Please read [ask] and provide a [mcve]. This code isn't going to compile, let alone return an empty list (`Where()` doesn't return a `List`). You simplified the example too much. The principle you show should work. — CodeCaster, Apr 11 '17 at 14:10
Did you try running only w.Attendees.Any(a => a.Student.Name.IndexOf(searchString) and debugging it? Are you sure it isn't empty? Your construct is correct, you are checking wheter any item in Attendees has a Student whose name contains a string. — victor, Apr 11 '17 at 14:14
@jdweng While Contains does make more sense since he doesn't need the index, the calls are equivalent if you compare the returned index against -1 — victor, Apr 11 '17 at 14:22
@victor That almost worked. I needed to add a couple of ToUpper() calls to make the search case insensitive. That also happens to be the first thing I tried yesterday when I started this whole mess. I also was using Contains(searchString) when this all started. I'm about to test the whole search, not just the navigation property. — , Apr 11 '17 at 14:28
You can use [this thread](http://stackoverflow.com/questions/444798/case-insensitive-containsstring) as reference regarding searching for a string in a string — default, Apr 11 '17 at 14:51
@victor if you enter an answer, I'll accept it. I ran into a null reference error, but I added checks for all of the search fields an it worked like a champ. Thanks to everyone who tried to help. — , Apr 11 '17 at 16:42

score 2 · Accepted Answer · answered Apr 11 '17 at 20:47

Try running only w.Attendees.Any(a => a.Student.Name.IndexOf(searchString) and debugging it because Attendees could be null or empty, and the same is true for the Student property.

Also, in the off chance that your database isn't case insensitive, you should consider changing your code to reflect that:

w.Attendees.Any(a => a.Student.Name.ToLowerInvariant().Contains(searchString.ToLowerInvariant())

The case sensitiveness could be the source of your problems too.

score -2 · Answer 2 · answered Apr 11 '17 at 14:33

-2

Try this:

List<Course> courses = db.Courses
    .Where(w => w.Title.Contains(searchString)|| 
           w.Description.Contains(searchString)  ||
           w.Attendees.Any(a => a.Student.Name.Contains(searchString))).ToList();

answered Apr 11 '17 at 14:33

AnjuRaj

298
1
10

Entity Framework How can I filter my results by a property of a navigation property?

2 Answers2