searching in children model using ANY is so slow in EFRSS

• • Reply
  hocamahdi99

  Member

  6 Points

  69 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

  Member

  650 Points

  249 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

  MSDN Community Support
  Please remember to click "Mark as Answer" the responses that resolved your issue.
  If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com