How can I speed up my LINQ query without using a table twice?

Question

So I am using a table twice in my query, and I dont know of another way to only use it once. Here is my query:

var result = (from t1 in (from t1 in db.Students.Where(en => en.Progress == MyEnum.Accepted).GroupBy(en => en.Class).AsEnumerable()
                          join t2 in dbOther.Classes on t1.Key equals t2.Class 
                          select t2)
              join t3 in (db.Students).AsEnumerable() on t1.Class equals t3.Class into t4
              select new
                          {
                              ClassNum = t1.Class,
                              StartDate = t1.StartDate,
                              Weeks = t1.Weeks,
                              Accepted = t4.Where(e => e.Progress == MyEnum.Accepted).Count(),
                              NotAccepted = t4.Where(e => e.Progress < MyEnum.Accepted).Count()
                          }).ToList();

I need to only get classes that have students in the accepted state. Then I want to get the classes and the count of its entire roster, even the students that are not accepted. Is there a better way to do this? It seems like reading from the same table twice is not the fastest way to do this.

Thank you for the help.

If you want your query to be faster then don't fetch the entire table's contents and perform your query in memory. Instead actually have the database do the query. That's what's the issue, not how many times you use the table in the query. — Servy, May 01 '18 at 18:01
Run a profiler and check the resulting query. You need to get your hands dirty to simplify things. — Harsh, May 01 '18 at 18:02
@Servy Is that possible while using two separate database contexts? How would I go about implementing that? Thank you for the quick reply btw. — BStill, May 01 '18 at 18:05
Honestly my suggestion would be to merge the DBs if possible. — juharr, May 01 '18 at 18:07
@BStill You do one query of one database to get all of the information you need from that database, and no more, then you do one query of the other database, get all of the information you need from that DB, and no more, and then, if needed, combine the results in memory. — Servy, May 01 '18 at 18:07
@juharr That would help with a lot of our queries if we did, but unfortunately we are not allowed to. — BStill, May 01 '18 at 18:08
@Servy Okay I get what you are saying. I am going to give it a try. — BStill, May 01 '18 at 18:09

gnud · Accepted Answer · 2018-05-01T20:05:56.117

1

Unless you're really simplifying the query here, you don't really need to hit dbOther to find out what classes has any students in the Accepted state. That information is in db.

var studentCountPerClass = (from s in db.Students
                            group s by s.Class into studentsByClass    
                            where studentsByClass.Any(x => x.Progress == MyEnum.Accepted)
                            select new
                            {
                                ClassNum = studentsByClass.Key,
                                Accepted = studentsByClass.Count(s => s.Progress == MyEnum.Accepted),
                                NotAccepted = studentsByClass.Count(s => s.Progress < MyEnum.Accepted),
                            })
                            .ToList();

var classCodes = studentCountPerClass.Select(x => x.ClassNum).ToList(); 
var classData = (from c in dbOther.Classes
                    where classCodes.Contains(c.Class)
                    select new {
                        c.Class,
                        // Any other data you want about the class
                    })

edited May 01 '18 at 20:05

answered May 01 '18 at 18:19

gnud

77,584
5
64
78

Yah sorry I simplified my query there is more data I need from `dbOther`. I like your solution, but how would I join the two lists? I need to join classData with the studentCountPerClass on ClassNum. Should I write a join inside that last query? – BStill May 01 '18 at 18:37
After that last query, you have all the data in memory in your application. Then you just combine them `from spc in studentCountPerClass join cd in classData on spc.ClassNum equals cd.Class select new { }` - this will be blazing fast unless you have more than a few hundred thousand classes. – gnud May 01 '18 at 18:44
Your answer is slightly faster than the other, so I am accepting yours. Thank you! – BStill May 01 '18 at 19:29
Actually this isn't getting the classes that only have students that are accepted. – BStill May 01 '18 at 19:57
1

You're right - missed a where. Was gonna put it in, but forgot. Fixed that now. – gnud May 01 '18 at 20:05

Sergey Kalinichenko · Answer 2 · 2018-05-01T18:45:10.860

First, fetch information about student enrollment from db.Students table:

var stats = db.Students
    .GroupBy(en => en.Class)
    .Select(g => new {
        Class = g.Key
    ,   Accepted = g.Count(en => en.Progress == MyEnum.Accepted)
    ,   NotAccepted = g.Count(en => en.Progress < MyEnum.Accepted)
    })
    .Where(g => g.Accepted != 0)
    .ToList();

Next, fetch classes from the other database:

var classIds = stats.Select(en => en.Class).ToList();
var classes = dbOther.Classes.Where(c => classIds.Contains(c.Class)).ToList();

Finally, join the two into the result:

var result = (from s in stats
    join c in classes on c.ClassId = s.ClassId
    select new {
        ClassNum = c.Class
    ,   c.StartDate
    ,   c.Weeks
    ,   s.Accepted
    ,   s.NotAccepted
    }).ToList();

@BStill That could be done with an in-memory join after fetching only the relevant classes from the other database. Please see the edit. — Sergey Kalinichenko, May 01 '18 at 18:46

score 0 · Answer 3 · answered May 01 '18 at 18:19

0

Instead of USING ". Enumerable()" over the student db object, use Querable. This will create an expression which gets evaluated at database level rather than fetching entire student table first and then doing the comparison.

Check this for details Returning IEnumerable<T> vs. IQueryable<T>

Just to try, set the profiler and see the generated queries for both Enumerable vs Queryable.. The difference will get illustrated there.

Entity framework is easiest showcased with differences in execution with both these methods. Also the simplest ORM to support these functions.

answered May 01 '18 at 18:19

NitinSingh

2,029
1
15
33

I did not know this information. This will definitely speed up some of our other queries too. Thank you for the article. – BStill May 01 '18 at 18:25
Well it doesn't help me or this question because I am using two different contexts. The reason I used Enumerable is because you cannot join tables from two different databases so I needed to store one in memory. – BStill May 03 '18 at 16:01
You can create a view to the second database and Query the view. However, you cannot update from the DataContext – Pleun May 07 '18 at 18:37

How can I speed up my LINQ query without using a table twice?

3 Answers3