Entity Framework Takes Too Much Time where Adding "WHERE Clause"

Question

I'm working on Schools System, and the System have many Users(teachers And Managers).

(Tools)

Asp.net 5 (MVC With C#) VS 2015
SQL SERVER (Azure)
Code-First Approach

Each user Assigned to One Or More Of Classes.

(The User Should only See His Classes And Students inside of Classes)

in the other side

to catch Each User Classes

I've write this Method

public static IEnumerable<Class> GetClasses()
{
    string UserId = HttpContext.Current.User.Identity.GetUserId();

    ApplicationDbContext db = new ApplicationDbContext();
    var Job = db.Jobs.Where(j => j.UserId == UserId).FirstOrDefault();
    if (Job.EntityLevelID == 1)
    {
        var x = db.Classes.Where(a => a.Levels.SupervisionCenter.Organization.Id == Job.EntityID) as IEnumerable<Class>;
        return x;
    }
    return null;
}

in The Students List Controller I just want the User to get his Students which are Only in his Classes

to get all the Students i write this Code

var items = db.Students.AsEnumerable().Where(o => o.Name.ToLower().Contains(search)).AsQueryable();

This Code will Give all the Students, it takes around 10 Sec to load and finish all the data (But that's is not needed Bcs, i need to Filter on ClassID Field).

when i need to filter on user Permissions based-classes

I've edit the code to be

    var items = db.Students.AsEnumerable().Where(o => o.Name.ToLower().Contains(search))
        .Where(s => UserDB.GetClasses().Select(c => c.Id).Contains(s.ClassID.Value))
        .AsQueryable();

in the Previous case, when i add the where query it takes more than 80 Sec

EDIT (1) The Organization Chart Will Be

Organizations
SuperVisionCenter
Schools
Classes

So I don't know what is the Problem I've made it here, can you please advise me for this

Thanks And Regards.

Assuming you have Server Management Studio available, I suggest that you run SQL Profiler while you're making these queries. Should be pretty obvious why your queries are so slow. — Tieson T., Dec 13 '16 at 07:15
Using `AsEnumerable()` will load the entire table from the db. And I assume that `GetClasses()` will also load complete data from db — Stephan Bauer, Dec 13 '16 at 07:17
@TiesonT. i have run the Profiler, i show that a round 1000 statements executed . — Loai, Dec 13 '16 at 22:05
@StephanBauer But isn't mandatory ? Bcs i have remore `AsEnumerable()` the code throw me an error — Loai, Dec 13 '16 at 22:06

score 0 · Answer 1 · edited May 23 '17 at 11:45

0

The very first thing that is slowing you down is AsEnumerable()

See here the effects of using AsEnumerable()

Basically you are getting all students in memory then running your where clause in app memory instead of just getting selected students.

Edit 1:

o.Name.ToLower().Contains(search)

I dont think you need to do ToLower() as MSSQL is case insensitive by default. This should remove the need for db.Students.AsEnumerable()

Edit 2:

Why don't you use join ? That would be a good optimisation to begin with.

something like

List<Student> data = (from student in db.Students
join clss in db.Classes on student.Class equals clss
where student.Name.Contains(search)).ToList()

edited May 23 '17 at 11:45

Community

1
1

answered Dec 13 '16 at 07:16

Bilal Fazlani

6,727
9
44
90

I try to remore IEnumrable() it gives me this error 'LINQ to Entities does not recognize the method 'System.Collections.Generic.IEnumerable`1 GetClasses()' method, and this method cannot be translated into a store expression' – Loai Dec 13 '16 at 07:23
yes. that's correct. What does GetClasses() method do ? – Bilal Fazlani Dec 13 '16 at 07:25
it retrieves the user classes, I have Included in the Thread BTW. – Loai Dec 13 '16 at 07:27
I cannot used it as you proposed Bcs, I need one Static Class to do this Task, Bcs this Query Will Execute it in more that one Page or Controller – Loai Dec 13 '16 at 07:32
wow yeah you need to get rid of GetClasses(), i think include your DB structure.. and a better approach may be suggested. What is the reason you cant use navigation props and lazy loading or eager loading... that would be way better than what GetClasses is doing – Seabizkit Dec 13 '16 at 07:51
@Seabizkit can you please give me more details about that, and how i can get rid of `GetClasses()` , How i can get the user Classes to get his Students inside these classes – Loai Dec 13 '16 at 09:47
like i said... show the db structure... and other will be able to assist. maybe research EF navigation properties and relationships – Seabizkit Dec 13 '16 at 10:04

score 0 · Answer 2 · answered Dec 13 '16 at 22:49

This solved My Problem, and it takes around 3 to 4 seconds only.

    int[] Classes = UserDB.GetClasses().Select(c => c.Id).ToArray();

    var items = db.Students.Where(o => o.Name.ToLower().Contains(search))
        .Where(r => Classes.Contains(r.ClassID.Value)).AsQueryable();

Entity Framework Takes Too Much Time where Adding "WHERE Clause"

2 Answers2