searching in children model using ANY is so slow in EF RSS

• • Reply
  hocamahdi99

  Member

  8 Points

  85 Posts

  searching in children model using ANY is so slow in EF

  Oct 04, 2019 10:23 AM|hocamahdi99|LINK

  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 ?

• • Reply
  jiadongm

  Participant

  1320 Points

  491 Posts

  Re: searching in children model using ANY is so slow in EF

  Oct 07, 2019 10:38 AM|jiadongm|LINK

  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

  .NET forums are moving to a new home on Microsoft Q&A, we encourage you to go to Microsoft Q&A for .NET for posting new questions and get involved today.