C# Linq multiple GroupBy and Select

Question

I have two objects that are linked, States and Cities, so each State has his Cities and each Citie is linked to an State. I also have some Units that have stateID and citieID but they are not linked since i have them only in Json.

What i need is to get only the States and Cities that have Units. I managed to get the first two but was wondering if there was any faster way to do it since i will have to make an update on those datas everyday:

//unitsData have a List of Units objects, this only have stateID, citieID and the unit data var unitsData = objUnidade.BuscaUnidades(); //unitsState have all units grouped by State, here i also only have stateID and citieID, same data as above var unitsState = unitsData.GroupBy(x => x.codigoEstado); //Here is where i make my search inside the unidadesEstados and select only the Estados that i need var activeStates = unitsState.Select(est => db.States.FirstOrDefault(x => x.ID == est.Key)).Where(state => state != null).ToList();

To do the Cities search i'm doing the same but using an extra foreach, is there a way to make this better ?

Just a note: It would be easier for others if you named the variables in english. — bytecode77
– bytecode77, Commented Jul 22, 2015 at 20:41

Jerry Federspiel · Accepted Answer · 2015-07-22 21:31:58Z

You are querying the database multiple times. It's better to use a SELECT ... IN query, which in LINQ looks like:

var units = objUnidad.BuscaUnidades(); var stateIds = units.Select(u => u.codigoEstado).ToList(); var activeStates = db.States.Where(s => stateIds.Contains(s.Id)).ToList();

EDIT: you asked about cities as well. It's more of the same:

var cityIds = units.Select(u => u.codigoCuidad).ToList() var activeCities = db.Cities.Where(c => cityIds.Contains(c.Id)).ToList();

This solution gives you every city whose ID is referred to by a unit. @StriplingWarrior 's solution will give you every city in (the states that have a unit).

Great, and how could i do that getting also the cities of each selected state ?
@Terkhos: var cities = db.Cities.Where(c => stateIds.Contains(c.StateId)).ToList()
Edited to add city solution. Now I'm not sure whether you're looking for every city in a state that is selected, or just cities whose ID is specified by a unit; if the former, use @StriplingWarrior's query; if the latter, use mine.
@JerryFederspiel: Instead of Contains(s), you need Contains(s.Id) or something like that.
Thanks Jerry and Stripling, it worked as i wanted, about your question Jerry i wanted all the cities of a selected state.

Kapol · Accepted Answer · 2015-07-22 20:53:55Z

If db.States queries the database, then for each group in unitsState the query will get executed. If the number of states isn't extremely large, you can store them in a list.

var dbStates = db.States.ToList(); var activeStates = unitsState.Select(est => dbStates.FirstOrDefault(x => x.ID == est.Key)).Where(state => state != null).ToList();

Yes, db.States queries the database, it isn't, i have only 27 states. How could i do that getting also the cities of each selected state ?
I guess you can just select all cities from the database, store them in a list as well and group them by state afterwards.

Collectives™ on Stack Overflow

C# Linq multiple GroupBy and Select

2 Answers 2

5 Comments

2 Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

2 Comments

Related