CODEWITHSUNDEEP
c#linqgroup-by
Ariel
Ariel

Reputation: 916

C# Linq multiple GroupBy and Select

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 ?

Upvotes: 1

Views: 187

Answers (2)

Jerry Federspiel
Jerry Federspiel

Reputation: 1524

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).

Upvotes: 1

Kapol
Kapol

Reputation: 6463

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

Upvotes: 1

Related Questions