locked
searching in children model using ANY is so slow in EF RRS feed

  • 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 ?

    Friday, October 4, 2019 10:23 AM

All replies

  • 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

    Monday, October 7, 2019 10:38 AM