Best way to use LINQ to Entities with a Collection Property

c# entity-framework linq

Question

Say I have the model as follows:

Cities and Trainstations

As you can see, every city has a train station or stations.

I also possess the following type:

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

I can now query the Entity Framework using LINQ, and it will return a new collection of my type, which is cool:

    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;
        }
    }

Can I use the same LINQ query to return a list of railway station names 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;
        }
    }

I only want one instance of CityDTO per city, thus the Join above has the additional issue of returning the same city name several times (for each of its train stations).

Should we use two LINQ statements to accomplish this? one to obtain a fresh list of cities, and the second to obtain a list of each city's train stations?

1
7
3/15/2015 2:28:01 PM

Popular Answer

@haim770, Your response guided me in the proper direction. Just for your curiosity, I had to make two more changes.

The following run time problem occurs when I simply add the Select projector.

enter image description here

So the Select anticipates an IEnumerable projected type, so I could have done this and everything would have been fine:

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

This is now operational:

    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;
        }
    }

But after reading further, I made the decision to use IQueryable, which also offers a Select function.

IQueryable is to blame for this. System includes Select. While IEnumerable, Linq. The System contains Select. Namespace for collections. As an IQueryable, this is significant. While IEnumerable, Select is streamlined to run with all server-side filters and return only the result to the client. Prior to filtering, Select loads the items to the client first.

IQueryable Versus IEnumerable

Consequently, we get 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;
        }
    }

I will now apply filters server-side as I add them.

Queryable.Select on MSDN

Enumerable.Select on MSDN

5
3/17/2015 6:39:01 PM


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow