LINQ to Entites: Doing a count over one-to-many relationships

Question

I have a couple of tables where there are one to many relationships. Let's say I have a Country table, a State table with a FK to Country, and a City table with a FK to State.

I'd like to be able to create a count of all Cities for a given country, and also a count of cities based on a filter - something like:

foreach( var country in Model.Country ) {
    total = country.State.All().City.All().Count() ;
    filtered = country.State.All().City.Any(c=>c.field == value).Count();
}

Obviously, this doesn't work - is there any way to do this?

Update:

I can iterate thru the objects:

    foreach (var item in Model ) {

      ... other stuff in here ...

      int tot = 0;
      int filtered = 0;
      foreach (var state in item.State)
      {
        foreach (var city in state.City)
        {
          tot++;
          if (city.Field == somevalue)
            filtered ++;
        }
      }

      ... other stuff in here ...

    }

but that doesn't seem very elegant.

Update: @AD has a couple of suggestions, but what worked to solve the problem was:

int tot = item.States.Sum(s=>s.City.Count);
int filtered = item.States.Sum(s=>s.City.Where(c=>c.Field == somevalue).Count());

Answer 1

You have to explicitly load children in the Entity Framework. If you load all the children then you can get counts just fine.

IEnumberable<Country> countries = Model.Country.Include("State");
total = countries[i].State.Count();

Assuming of course that the iteration through all countries is important. Otherwise why not just query against City filtered by State and Country?

In your state foreach you should just be able to do

tot += state.City.Where(x=> x.Field == value).Count();

Answer 2

Why dont you reverse it?

foreach( var country in Model.Country ) {
    var city  = Model.State.Where(x=>x.StateID==country.State.StateID).Select(x=>City)
    total  = city.Count();
    filtered = city.All(c=>c.field == value).Count();
}

Answer 3

You can try, assuming you already have the givenCountry and value variable populated:

int total = EntityModel.CitySet.Where( it => it.State.Country.ID == givenCountry.ID ).Count();

Above, you take your entire set of cities (EntityMode.CitySet). This set contains all the cities in all the states in all the countries. The problem becomes: what subset of those cities are in country 'givenCountry'? To figure it out, you apply the Where() to the entire set and you compare the countries id to see if they are the same. However, since the city only knows which state it is in (and not the country) you first have to reference its state (it.State). it.State references the state object and that object has a Country property that will reference the country. Then it.State.Country references the country 'it' is in and 'it' is the city, creating a link between the city and the country.

Note that you could have done this is reverse as well with

int total = givenCountry.Sum( c => c.States.Sum( s.Cities.Count() ) )

However, here you will have to make sure that givenCountry has its States collection loaded in memory and also that each State has its Cities collection loaded. That is because you are using Linq-to-Entities on a loaded object and not on an Entity Framework instance object has was the case in the first example. There is a way to craft the last query to use the entity framework object however:

int total = EntityModel.CountrySet.Where( c => c.ID == givenCountry.ID ).Sum( c => c.States.Sum( s.Cities.Count() ) )

As for the number of cities with a specific field, you take a similar approach with a Where() call:

int filtered = EntityModel.CitySet.Where( it => it.field == value ).Count();