searching in children model using ANY is so slow in EF

Question

User2131089582 posted

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

public virtual Teacher Teacher { get; set; }
}

public class Nim
{
public int Id{ get; set; }
public int NIM { get; set; }
public bool Valid { get; set; }
}

public class Teacher
{
public int Id { get; set; }
public string Name { get; set; }

public int NimId { get; set; } //this is fk
public virtual ICollection<Student> Students { get; set; }
}

i want to get list of NIM on Teacher by finding with name of teacher here is my first code,
i can do that using this code and the performance is good and fast

string keyword = "John";
using (TestingEntities1 db = new TestingEntities1())
{
var models = db.Teachers.Where(a => a.Name.Equals(keyword)).
GroupBy(x => x.NimId).Select(i => new { i.FirstOrDefault().NimId });
}

then i add feature to also enable get the list of NIM from child name which is Student,
I have tried using "Any" keyword, but the performance is very slow load the result (i have many data in my database)

below is my code, i used "Any"
string keyword = "John";
using (TestingEntities1 db = new TestingEntities1())
{
var models = db.Teachers.Where(a => a.Name.Equals(keyword) || a.Students.Any(x=>x.Name.Equals(keyword))).
GroupBy(x => x.NimId).Select(i => new { i.FirstOrDefault().NimId });

}

Can you give me a proper way to do that and also good sped load result with my case ?

Answer 1

User-17257777 posted

Hi hocamahdi99,

From the code you provide, It seems the query should select DISTINCT NimId, while the "GroupBy(x => x.NimId).Select(i => new { i.FirstOrDefault().NimId }); " will make the query more complex and inefficient here. You can try as below:

var models = db.Teachers.Where(a => a.Name.Equals(keyword) || a.Students.Any(x => x.Name.Equals(keyword)))
                .Select(i => i.NimId).Distinct();

Best Regards,

Jiadong Meng