CODEWITHSUNDEEP
c#linq
Bohms27
Bohms27

Reputation: 407

LINQ Query a value down the object tree needs matched

Hello and thank you in advance for any help.

I have a PostalCode object, which has a List of Cities associated with it, a city has a state object as a property, and the state object has a country object as a property.

I need to run a linq query on a list of postal codes that will return cities, but I need it to be where the country code is equal to "US".

In SQL it would be a bunch of inner joins on the relationships:

SELECT *
FROM PostalCodes pc
INNER JOIN CityPostalCode cpc
ON cpc.PostalCodeId = pc.Id
INNER JOIN Cities c 
ON cpc.CityId = c.Id
INNER JOIN States s
ON s.Id = c.StateId
INNER JOIN Country c
ON s.CountryId = c.Id
WHERE pc.Code = '12345' AND c.Code = 'US'

How can I do this on my object in LINQ? 

public class PostalCode
{

    public int Id { get; set; }
    public string Code { get; set; }

    public virtual List<City> Cities { get; set; } 
}

public class City
{
    public int Id { get; set; }
    public string Name { get; set; }
    public int StateId { get; set; }
    public virtual State State { get; set; }
    public virtual List<PostalCode> PostalCodes { get; set; }
}
public class State
{
    public int Id {get; set;}
    public string Code {get; set;}
    public string Name {get; set;}
    public int CountryId {get; set;}
    public Country Country {get; set;}
}
public class Country
{

    public int Id { get; set; }
    public string Code { get; set; }
    public string Name { get; set; }
    public virtual List<State> States { get; set; }
}

Upvotes: 2

Views: 63

Answers (3)

Riad Baghbanli
Riad Baghbanli

Reputation: 3319

See code below:

postalCodes.Where(p => p.Code == "12345" &&
   p.Cities.Where(c => c.State.Country.Code == "US").Any());

Upvotes: 1

Dan Dumitru
Dan Dumitru

Reputation: 5423

If you want to return a list of cities, where a city contains that Postal Code, then you can do:

var cities = context.Cities
    .Where(city => city.State.Country.Code == "US" &&
                   city.PostalCodes.Any(pc => pc.Code == "12345"))
    .ToList();

Upvotes: 1

Yacoub Massad
Yacoub Massad

Reputation: 27861

Here is how you can do it:

var result =
    listOfPostalCodes
    .Where(pc => pc.Code == "12345") //Filter postal codes
    .SelectMany(pc => pc.Cities) //Select all cities
    .Where(c => c.Country.Code == "US") //Of which country code is equal to US
    .ToList();

Upvotes: 1

Related Questions