Best way to use LINQ to Entities with a Collection Property

Question

Let's say I have the following model:

As you can see each city has one or more train stations.

And I have the following type:

public class CityDTO
{
    public string CityName { get; set; }
    public string StateName { get; set; }
    public List<string> Trainstations { get; set; }
}

Now the cool thing about LINQ is that I can query the Entity Framework and return a new collection of my type:

    public List<CityDTO> GetCities()
    {
        using (var db = new CityDataContext())
        {

            var cities = db.Cities;
            var trainstations = db.TrainStations;

            var query =
            (from city in cities
             select new CityDTO
             {
                 CityName = city.Name,
                 StateName = city.State

             }).ToList();

            return query;
        }
    }

The question is: can I also return a collection of train station names in the same LINQ query to add to my CityDTO class?

   public List<CityDTO> GetCities()
    {
        using (var db = new CityDataContext())
        {

            var cities = db.Cities;
            var trainstations = db.TrainStations;

            var query =
            (from city in cities
             join trainstation in trainstations
             on city.CityId
             equals trainstation.CityId into orderGroup
             select new CityDTO
             {
                 CityName = city.Name,
                 StateName = city.State
                 //assign List<string> of trainstation names to CityDTO.Trainstations

             }).ToList();

            return query;
        }
    }

The other problem with the Join above is that it will return the same city name multiple times (for each of its train stations), and I only want one instance of CityDTO per city.

Is this best done with two LINQ statements? One to get a new list of cities, and the second to get the list of train stations for each?

Answer 1

@haim770, your answer helped me on the right track. Just as a matter of interest, I had to change two more things.

When I just add the Select projector, I get the following run time error

So the Select expects the projected type to be IEnumerable, so I could have done this and all would be cool:

public class CityDTO
{
    public string CityName { get; set; }
    public string StateName { get; set; }
    public IEnumerable<string> Trainstations { get; set; } //changed from List<> to IEnumerable<> 
}

Now this works fine:

    public List<CityDTO> GetCities()
    {
        using (var db = new CitysDataEntities())
        {
            var cities = db.Cities;

            var query =
            (from city in cities
             select new CityDTO
                 {
                    CityName = city.Name,
                    Trainstations = city.TrainStations.Select(ts => ts.Name)  //now this works

                 }).ToList();

            return query;
        }
    }

However, after some further reading, I decided to use IQueryable which also has a Select method.

The reason for this is that IQueryable.Select is under System.Linq, while IEnumerable.Select is in the System.Collections Namespace. This is important as IQueryable.Select is optimized to execute with all filters on the server and return only the result to the client, while IEnumerable.Select loads the objects to the client first before filtering.

IEnumerable Vs IQueryable

So the result is this

public class CityDTO
{
    public string CityName { get; set; }
    public string StateName { get; set; }
    public IQueryable<string> Trainstations { get; set; } // changed to IQueryable
}

and

    public List<CityDTO> GetCities()
    {
        using (var db = new CitysDataEntities())
        {
            var cities = db.Cities;

            var query =
            (from city in cities
             select new CityDTO
                 {
                    CityName = city.Name,
                     Trainstations = city.TrainStations.Select(ts => ts.Name).AsQueryable() // return AsQueryable

                 }).ToList();

            return query;
        }
    }

Now when I add filters they will be applied server-side.

MSDN - Queryable.Select

MSDN - Enumerable.Select

Answer 2

The Join operation here is implicit because there is a navigation-property from City to TrainStations so EF will be able to automatically retrieve the stations for you :

(from city in cities
 select new CityDTO
     {
     CityName = city.Name,
     StateName = city.State,
     Trainstations = city.TrainStations.Select(ts => ts.Name).ToList()
     })

The above is using Select() to project the list of train stations into a list of string based on the Name property.

See MSDN