CODEWITHSUNDEEP
c#entity-frameworklinq
Lucy
Lucy

Reputation: 253

Linq: How to make an object from linq result

I have 3 tables Continent, Country and City. I need to make an object of Continent type that has its countries and countries have their cities.

Continent -->Country--> City like this. Europe --> Germany --> Frankfurt, Berlin,etc..

My code:

public partial class Continent
{
    public Continent()
    {
        this.Countries = new HashSet<Country>();
    }

    public int ContinentId { get; set; }
    public string ContinentName { get; set; }

    public virtual ICollection<Country> Countries { get; set; }
}

public partial class Country
{
    public Country()
    {
        this.Cities = new HashSet<City>();
    }

    public int CountryId { get; set; }
    public string CountryName { get; set; }
    public Nullable<int> ContinentId { get; set; }

    public virtual ICollection<City> Cities { get; set; }
    public virtual Continent Continent { get; set; }
}

public partial class City
{
    public int CityId { get; set; }
    public string CityName { get; set; }
    public int CountryId { get; set; }

    public virtual Country Country { get; set; }
}

var Result = (from a in db.Continents
              join b in db.Countries
                on a.ContinentId equals b.ContinentId
              join c in db.Cities on b.CountryId equals c.CountryId
              where a.ContinentId == 1
              select new
                     {
                         ContinentName = a.ContinentName,
                         CountryName = b.CountryName,
                         CityName = c.CityName
                    });

This is the result:

enter image description here

I need to make an object of type Continent which includes its own counties and cities

Upvotes: 0

Views: 178

Answers (2)

Dave Cousineau
Dave Cousineau

Reputation: 13148

To get the results you indicated you would do something like:

using (var context = new MyContext()) {
   var data =
      context
      .Continents
      .SelectMany(continent =>
         continent
         .Countries
         .SelectMany(country =>
            country
            .Cities
            .Select(city =>
               new {
                  ContinentName = continent.Name,
                  CountryName = country.Name,
                  CityName = city.Name
               }
            )
         )
      ).ToList();

   // do something with data
}

Upvotes: 0

Developer
Developer

Reputation: 6430

Use the namespace System.Entity.Data;

var continent = db.Continents
                  .Include(c => c.Countries.Select(cn => cn.Cities))
                  .FirstOrDefault(c => c.ContinentId == 1);

Upvotes: 3

Related Questions