The best way to use LINQ for objects with Collection property

Question

Let's say I have the following model:

As you can see, each city has one or more 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 about LINQ 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; } } 

Question: Can I also return a collection of station names in the same LINQ query to add 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; } } 

Another problem with Accession above is that it will return the same city name several times (for each of its railway stations), and I only need one copy of CityDTO for the city.

Is this best done with two LINQ statements? One to get a new list of cities, and one to get a list of 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 a Select projector, I get the following runtime error

So, Select expects the projected type to be IEnumerable, so I could do this, and everything 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 great:

  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, and IEnumerable.Select is in the System.Collections namespace. This is important, because IQueryable.Select is optimized for execution with all filters on the server and returns only the result to the client, whereas IEnumerable.Select loads the objects into the client first before filtering.

IEnumerable Vs IQueryable

So the result of this is

 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 on the 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 receive 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 uses Select() to project the station list into the string list based on the Name property.

See MSDN

Answer 3

Use AutoMapper for the project collection property in IList.

 Queryable.ProjectTo<UserListDto>().ToArray(); 

An object

 public class User { public string Name { get; set; } public virtual ICollection<UserRole> Roles { get; set; } } 

Dto

 [AutoMapFrom(typeof(User))] public class UserListDto { public string Name { get; set; } public IList<UserRoleOutput> Roles { get; set; } [AutoMapFrom(typeof(UserRole))] public class UserRoleOutput { public int RoleId { get; set; } } }