complex LINQ with orderby and 3 List<T>

[dannystommen]

I have 3 List<T> with different kind of objects.

The objects are (not managed, 3th party webservices)

country
- long id
- string name

region
- long id
- string name
- long country_id

dma
- long id
- string name
- long country_id
- long[] region_ids (this array will always be length 0 or 1)


Now, what I want to achieve is to get all DMAs ordered by country name, region name, dma name.

Is this possible in 1 LINQ query?

I managed to create a solution that orders on country name, dma name. But can't seems to find to get region name as well in the sorted result.

List<dma> DMAs = GetDMAs();
List<country> Countries = GetCountries();
List<region> Regions= GetRegions();

var result = from d in DMAs
join c in Countries
on d.country_id equals c.id
orderby c.name, d.name
select d;



I did create an solution, but I think this solution is very long winded in my opinion (doing some loops).

var result = new List<dma>();

//get country ids
var country_ids = from c in Countries
where (from d in DMAs select d.country_id).Contains(c.id)
orderby c.name
select c.id;

foreach (var c_id in country_ids) {
//select DMAs of this country that have no regions
var country_dmas = from d in DMAs
where d.country_id == c_id && d.region_ids.Length == 0
orderby d.name
select d;
result.AddRange(country_dmas);

//select region ids of the country
var region_ids = from r in Regions
where r.country_id == c_id
orderby r.name
select r.id;

foreach (var r_id in region_ids) {
//select DMAs of this region
var dmas = from d in DMAs
where d.region_ids.Contains(r_id)
orderby d.name
select d;
result.AddRange(dmas);
}
}

return result;