Iqueryable.where.tolist VS iqueryable.tolist.where, which query is better in terms of performance?

Question

0.00/5 (No votes)

See more:

I'm having two queries, both are producing same result but I wanna know which one is more efficient. Below are the queries, I've written the query clauses only. The inner conditions are same for both the queries.

IQueryable().Where().ToList();
IQueryable().ToList().Where();

At my end I've tried below code and it's showing "IQueryable().ToList().Where();" is better. There are few questions that I don't understand: 1. Without seeing my below temporary code what which query is more efficient? 2. As fas as I know, IQueryable is good for querying remote data. So, should not be it better first to filter out the items and then use ToList so that we don't need to perform ToList function on non relevant items? (if this is the case then why below code is saying query 2 is more efficient?)

C#

Stopwatch st1 = new Stopwatch();
            Stopwatch st2 = new Stopwatch();
            int counter = 10000;
            IEnumerable<Employee> iEmp = null;
            IQueryable<Employee> qEmp = null;
            BindingList<Employee> bList = new BindingList<Employee>();
            for (int i = 1; i <= counter; ++i)
            {
                bList.Add(new Employee
                {
                    Department = $"Dept - {i}",
                    EmployeeID = i,
                    EmployeeName = $"Employee - {i}",
                    Salary = i + 10000
                });
            }

            iEmp = bList.AsEnumerable<Employee>();
            qEmp = bList.AsQueryable<Employee>();

            st1.Start();
            var t = qEmp.Where(x => x.EmployeeID % 2 == 0).ToList();
            st1.Stop();
            Console.WriteLine($"Queryable-Where-ToList: {st1.ElapsedTicks}");

            st2.Start();
            var t1 = qEmp.ToList().Where(x => x.EmployeeID % 2 == 0);
            st2.Stop();
            Console.WriteLine($"Queryable-ToList-Where: {st2.ElapsedTicks}");
            Console.ReadKey();

What I have tried:

I tried an example but I am not satisfied because it contradict with my theory and I am not able to justify the result.

Posted 17-Mar-17 5:00am

sunil kumar meena

Updated 17-Mar-17 5:53am

Add a Solution

2 solutions

Solution 2

IQueryable is only really intended for objects that actually use it. In your instance both your queries are really only using IEnumerable, however when you're using IQueryable you're simply wrapping IEnumerable in a different interface (IQueryable), your underlying BindingList doesn't support IQueryable.

If you were using a provider that supports IQueryable such as EntityFramework then your .ToList().Where() would instigate a

"select fields from table"

query (on ToList) and take all the results into memory and your Where would work on that in-memory result list.

Your .Where().ToList() however would get converted to a "select fields from table where..." statement so ToList would only load those records into memory.

So for EF .Where.ToList is better as it means only the relevant data is retrieved, whereas .ToList.Where gets all data and does an in-memory filter.

As you're not using a provider that supports IQueryable your IQueryable call is the same as your IEnumerable but with some additional overhead.

Posted 17-Mar-17 5:53am

F-ES Sitecore

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Richard Deeming · Accepted Answer · 2017-03-17T05:34:00

Firstly, your test is not querying remote data; you're querying in-memory data. There won't be much difference between the two when the data is already in memory.

Secondly, LINQ doesn't do any processing until you enumerate the sequence. In query 1, you do that by calling ToList; in query 2, you don't execute the sequence, so the filter is never applied.

Thirdly, the ToList method has an optimization for cases where the input sequence is something that implements ICollection<T> - it will pre-allocate the list's internal storage to the correct size. When the input doesn't implement that interface, the list will start with space for four items, and double the capacity each time it runs out of space. Query 1 passes in an iterator which doesn't implement that interface; Query 2 passes in a BindingList<T>, which does implement that interface.

And finally, evaluating the performance of code is a complicated topic. You need to "warm up" the code, allowing the JIT compiler to compile it; and you need to execute the code you're testing many times. Your current tests do neither of these things, so the results are worthless.

To answer your question, .Where(...).ToList() will be more efficient than .ToList().Where(...) for any real scenario. Particularly with remote data (eg: a database), where the filter can be evaluated at the source, limiting the number of objects returned.