score:0

Accepted answer

Maybe you’re looking for the let keyword?

var Persons = 
    from _person in people
    let placeID = 
    (
        from _peopleplaces in peoplePlace
        where _person.Id == _peopleplaces.PersonId 
        select _peopleplaces.PlaceId
    ).FirstOrDefault()
    let placeName =
    (
        from _places in places
        where _places.Id == placeID 
        select _places.Name
    ).FirstOrDefault()
    select new
    {
        ID = _person.Id,
        Name = _person.Name,
        Nationality = _person.Nationality,
        Age = _person.Age,
        PlaceID = placeID,
        PlaceName = placeName,
    };

Edit: As @sambomartin correctly mentioned below, you need to consider what would happen if there is no entry in peoplePlace for a specific person. Under the above query, such persons are still returned in the final result, with a PlaceID and PlaceName of null.

C# allows equality comparison between an int and a int?; if the latter is null, the comparison would always evaluate to false. Thus, when placeID is null, no entry from places would satisfy the _places.Id == placeID condition (assuming the Id field is not nullable), and placeName would evaluate to null as well.

If you do not want such persons in your final result, you could amend your query by adding where clauses:

var Persons = 
    from _person in people
    let placeID = 
    (
        from _peopleplaces in peoplePlace
        where _person.Id == _peopleplaces.PersonId 
        select _peopleplaces.PlaceId
    ).FirstOrDefault()
    where placeID != null   // ensure that the person has an associated place
    let placeName =
    (
        from _places in places
        where _places.Id == placeID 
        select _places.Name
    ).FirstOrDefault()
    where placeName != null   // ensure that the place appears in the primary table
    select new
    {
        ID = _person.Id,
        Name = _person.Name,
        Nationality = _person.Nationality,
        Age = _person.Age,
        PlaceID = placeID,
        PlaceName = placeName,
    };

score:3

You cant use the PlaceId until it's been calculated, I.e. when its enumerated.

There's nothing to stop you querying the three objects in a single expression, e.g.

var people = from p in Persons
             from ppl in PersonPlaces
             From pl in Places
             where p.Id == ppl.PersonId
             && ppl.PlaceId == pl.Id
             select new { Name=p.Name, PlaceName=pl.Name}

Sorry for formatting, it's difficult on iPad.

Hth

Sam


Related Articles