Entity Framework - Performance in count

c# entity-framework performance

Question

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()?

1
14
8/31/2015 9:59:04 AM

Accepted Answer

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.

24
8/31/2015 9:44:20 AM

Popular Answer

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



Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow