LINQ statement for joining multiple tables (Entity Framework 6)

Question

I am new with LINQ / Entity Framework and I am struggling how to join my tables.

I have following classes:

public class PhotoPlace
{
    #region attributes
    [Key]
    public long Id { get; set; }
    public List<ParkingLocation> ParkingLocations { get; set; }
    public SubjectLocation PlaceSubjectLocation { get; set; }
    #endregion

    #region constructors   
    public PhotoPlace()
    { 
    }
    #endregion
}

public class ParkingLocation : LocationBase
{
    #region attributes
    public PhotoPlace PhotoPlace { get; set; }
    public List<ShootingLocation> ShootingLocations { get; set; }
    #endregion

    public ParkingLocation()
    {
    }
}

public class ShootingLocation : LocationBase
{
    #region attributes
    public ParkingLocation ParkingLocation { get; set; }      
    public List<Photo> Photos { get; set; }
    #endregion

    #region constructors
    public ShootingLocation()
    { 
    }
    #endregion
}

public class Photo
{
    #region attributes
    public long Id { get; set; }
    public byte[] ImageBytes { get; set; }

    public ShootingLocation ShootingLocation { get; set; }
    #endregion

    #region constructors
    public Photo()
    {
    }
    #endregion
}

So a PhotoPlace has multiple ParkingLocations, a ParkingLocation has multiple ShootingLocations, a ShootingLocation has multiple Photos.

I now want to read a PhotoPlace with all dependent objects: Before I added the Photos, everything was fine with the following statement:

using (var db = new LocationScoutContext())
{
    photoPlacesFound = db.PhotoPlaces.Include(pp => pp.PlaceSubjectLocation)
       .Include(pp => pp.ParkingLocations.Select(pl => pl.ShootingLocations))
       .Include(pp => pp.PlaceSubjectLocation.SubjectCountry)
       .Include(pp => pp.PlaceSubjectLocation.SubjectArea)
       .Include(pp => pp.PlaceSubjectLocation.SubjectSubArea).ToList();
 }

The other classes should not matter. I tried to extend the

.Include(pp => pp.ParkingLocations.Select(pl => pl.ShootingLocations))

statement with another "Select" but that does not work out. Any help is very welcome.

Answer 1

Try the following using thenInclude

using (var db = new LocationScoutContext())
{
   photoPlacesFound = db.PhotoPlaces.Include(pp => pp.ParkingLocations)
   .ThenInclude(pp => pp.ShootingLocations)
   .ThenInclude(pp => pp.Photos)
   .ToList();
}

Answer 2

One of the slower parts of a database query is the transport of the selected data from your database management query to your local process. Hence it is wise to select only the values you actually plan to use.

For example, every PhotoPlace has zero or more ParkingLocations, every ParkingLocaction belongs to exactly one PhotoPlace, using a foreign key. A fairly straightforward one-to-many relation.

So if PhotoPlace 4 has 1000 ParkingLocations, then every ParkingLocation would have a foreign key to the PhotoPlace that it belongs to. As expected, the value of this foreign key would be 4.

When you use Include to fetch the PhotoPlaces with its ParkingLocations you also select the foreign keys. What a waste to send 1000 times the value 4, while you already know this value.

When using entity framework, always use Select instead of Include. Only use Include if you plan to update the fetched data.

The following query would be much more efficient as it only selects the properties you actually plan to use:

var photoPlacesFound = db.PhotoPlaces
    .Where(photoPlace => ...)          // if you don't want all PhotoPlaces
    .Select(photoPlace => new
    {
         // Select only the PhotoPlace properties you actually plan to use:
         Id = photoPlace.Id,
         ...

         ParkingLocations = PhotoPlace.ParkingLocations
             .Select(parkingLocation => new
             {
                  // again: select only the ParkingLocation properties you plan to use
                  Id = parkingLocation.Id,
                  Name = parkingLocation.Name,

                  // not needed: you already know the value:
                  // PhotoPlaceId = parkingLocation.PhotoPlaceId, // foreign key

                  ShootingLocations = parkingLocations.ShootingLocations
                      .Select(shootingLocation => new
                      {
                           // you know the drill by now: only select the ...
                      })
                      .ToList(),

             })
             .ToList(),
    });

Some improvements

I noticed that you declared your one-to-many relationships as Lists instead of ICollections. Are you sure that PhotoPlace.ParkingLocation[4] has a proper meaning?

I also noticed that you didn't declare the relations between the tables virtual

In entity framework, the non-virtual properties represent the columns of the tables. The relations between the tables (one-to-many, many-to-many, ...) are represented by virtual properties.

As long as you follow the entity framework code first conventions, you won't need attributes or fluent API for most elements, thus keeping your code compacter and easier to read:

public class PhotoPlace
{
    public long Id { get; set; }
    ... // other properties

    // every PhotoPlace has zero or more ParkingLocations (one-to-many)
    public virtual ICollection<ParkingLocation> ParkingLocations { get; set; }
}

public class ParkingLocation
{
    public long Id {get; set;}
    ...

    // every ParkingLocation belongs to exactly one PhotoPlace using foreign key
    public long PhotoPlaceId {get; set;}
    public virtual PhotoPlace PhotoPlace {get; set;}

    // every ParkingLocation has zero or more ShootingLocations (one-to-many)
    public virtual ICollection<ShootingLocation> ShootingLocations {get; set;}
}

Because I followed the conventions, the ModelBuilder is able to detect the names of the tables and the columns. It can detect the primary keys. Because of the virtual keyword it knows the relation between the tables and the foreign keys used in these relations.

Because I use ICollection<...> your compiler won't accept undefined functionality like List[4]

Finally: because the constructors don't do anything I removed them