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;