Entity Framework 5 - Loads all rows on count

Question

I have a very big table with more then one million rows.

Simple:

public partial class Country
{
    public Country()
    {
        this.Streets = new HashSet<Streets>();
    }

    public int Id { get; set; }
    public virtual ICollection<Street> Streets { get; set; }
}

public partial class Street
{
    public int Id { get; set; }
    public int CountryId { get; set; }
    public virtual Country Country { get; set; }
}

If I want to get the total count of streets in a country I can do

context.CountryStreets.Count(v=>v.CountryId == X)

EF gives you an easy method to access the related table with lazy loading:

Country.Streets.Count()

This method is very nice and easy but... For some reason it's loading the total related table.

Why? And how can I change this behavior?

Thanks In Advance

score 0 · Answer 1 · edited May 23 '17 at 12:13

0

Take a look at these two links, they may help with your issue:

How to COUNT rows within EntityFramework without loading contents?

http://msdn.microsoft.com/en-us/data/jj574232.aspx

edited May 23 '17 at 12:13

Community

1
1

answered Jul 24 '13 at 12:47

Christian Phillips

18,399
8
53
82

1. This I showed in my post. 2. Really ugly - Entry(blog).Collection(b => b.Posts).Query().Count(); - Is there a nice way? – Yacov Jul 24 '13 at 12:54

score 0 · Accepted Answer · edited Jun 20 '20 at 09:12

Why?

Because it is not made for this purpose.
And how can I change this behavior?

You really can't - only if you start to mess around with the Expression Provider.

To Note:

If we retrieve 50 records and on each row we do 'count', we will have more than 50(!) requests to MSSQL , as a result - very bad performance.

The best solution is to do something like this:

ct.Country.Select(v => new { Country = v, TotalStreets = v.Streets.Count() });

Please not that there is only one request.

Entity Framework 5 - Loads all rows on count

2 Answers2