CODEWITHSUNDEEP
c#performanceentity-framework
Matthias Burger
Matthias Burger

Reputation: 5946

Entity Framework - Performance in count

I've a little question about performance with Entity Framework.

Something like 

using (MyContext context = new MyContext())
{
    Document DocObject = context.Document.Find(_id);
    int GroupCount = context.Document.Where(w=>w.Group == DocObject.Group).ToList().Count();
}

takes about 2 seconds in my database (about 30k datasets), while this one

using (MyContext context = new MyContext())
{
    Document DocObject = context.Document.Find(_id);
    int GroupCount = context.Document.Where(w=>w.Group == DocObject.Group).Count();
}

takes 0,02 seconds.

When my filter for 10 documents had 20 seconds to wait, I checked my code, and changed this to not use ToList() before Count().

Any ideas why it needs 2 seconds for this line with the ToList()?

Upvotes: 17

Views: 14542

Answers (5)

Jan 'splite' K.
Jan 'splite' K.

Reputation: 2104

Your first query isnt fully transalted to sql - when you call .ToList().Count(), you are basically saying "download all, materialize it to POCO and call extension method named Count()" which, of course, take some time.

Your second query is, however, transalted to something like select count(*) from Documents where GroupId = @DocObjectGroup which is much faster to execute and you arent materializing anything, just simple scalar.

Upvotes: 2

Jcl
Jcl

Reputation: 28272

Because ToList() will query the database for the whole objects (will do a SELECT * so to say), and then you'll use Count() on the list in memory with all the records, whereas if you use Count() on the IQueryable (and not on the List), EF will translate it to a simple SELECT COUNT(*) SQL query

Upvotes: 4

ken2k
ken2k

Reputation: 48975

Calling ToList() then Count() will:

  • execute the whole SELECT FROM WHERE against your database
  • then materialize all the resulting entities as .Net objects
  • create a new List<T> object containing all the results
  • return the result of the Count property of the .Net list you just created

Calling Count() against an IQueryable will:

  • execute SELECT COUNT FROM WHERE against your database
  • return an Int32 with the number of rows

Obviously, if you're only interested in the number of items (not the items themselves), then you shouldn't ever call ToList() first, as it will require a lot of resources for nothing.

Upvotes: 30

Marcus
Marcus

Reputation: 8649

Yes, ToList() will evaluate the results (retrieving the objects from database), if you do not use ToList(), the objects aren´t retrieved from the database.

Linq-To-Entities uses LazyLoading per default.

It works something like this; When you query your underlying DB connection using Linq-To-Entities you will get a proxy object back on which you can perform a number of operations (count being one). This means that you do not get all the data from the DB at once but rather the objects are retrieved from the DB at the time of evaluation. One way of evaluating the object is by using ToList().

Maybe you should read this.

Upvotes: 4

Salah Akbari
Salah Akbari

Reputation: 39946

Using the extension method Enumerable.ToList() will construct a new List object from the IEnumerable<T> source collection which means that there is an associated cost with doing ToList().

Upvotes: 1

Related Questions