Pawel Pawel - 2 months ago 7
SQL Question

Selecting all x which are in y table

I have table of users and cities. I want to get list of all cities that are in users collection so that I can acces to all cities id;

public class City
{
public int ID { get; set; }
public string Name { get; set; }

public virtual ICollection<ApplicationUser> AppUserID { get; set; }
public virtual ICollection<Measurement> Measurements { get; set; }
}
public class ApplicationUser : IdentityUser
{
public override string Id { get; set; }
public virtual ICollection<City> Cities { get; set; }
}


I have already tried

var cities = db.Cities.Where(c => c.AppUserID != null ); //and with .ToList()

var cities = from city in db.Cities
join user in db.Users
on city.AppUserID equals user.Cities
select city.ID;


but it doesn't work.
Any solutions?
Thanks in advance.

Answer

I want to get list of all cities that are in users collection so that I can acces to all cities id;

Flatten the city collection from the user list

IEnumerable<City> = db.Users.SelectMany(u => u.Cities.Select(c => c));

This will provide you list of all the cities in the users collection, which you can use to fetch all the user specific city data.

More filtering operations can be run to further trim down the list of cities as per requirement

Comments