Hi all,

I'm just wondering what the best way is to do this:

I have 3 tables:

Country CountryID Country Name

Province ProvinceID CountryID ProvinceName

City CityID ProvinceID CityName

I like to have the ability in my CountryEntity to get a collection of all my CityEntities. Basically this query:

SELECT * FROM Countries INNER JOIN Provinces ON Countries.CountryID = Provinces.CountryID INNER JOIN Cities ON Provinces.ProvinceID = Cities.ProvinceID

I can't seem to add a relation to my CountryEntity to join via the Province table to get the cityEntitys.

Any ideas?

Thanks.

The following section in the referance manual should help you with this "Multi-entity filters". I have pasted the code help for you convinience from the manual

Dim customers As New EntityCollection(New CustomerEntityFactory())
Dim bucket As New RelationPredicateBucket()
bucket.Relations.Add(CustomerEntity.Relations.OrderEntityUsingCustomerID)
bucket.Relations.Add(OrderEntity.Relations.OrderDetailsEntityUsingOrderID)
bucket.Relations.Add(OrderDetailsEntity.Relations.ProductEntityUsingProductID)
bucket.Relations.Add(ProductEntity.Relations.SupplierEntityUsingSupplierID)
bucket.PredicateExpression.Add(PredicateFactory.CompareValue(SupplierFieldIndex.Country, ComparisonOperator.Equal, "France"))
Dim adapter As New DataAccessAdapter()
adapter.FetchEntityCollection(customers, bucket)

You code would probaly look some like the following

Dim Country As New EntityCollection(New CountryEntityFactory())
Dim bucket As New RelationPredicateBucket()
bucket.Relations.Add(CountryEntity.Relations.ProvinceEntityUsingCountryID)
bucket.Relations.Add(ProvinceEntity.Relations.CityEntityProvinceID)
Dim adapter As New DataAccessAdapter()
adapter.FetchEntityCollection(Country, bucket)

Hope this help

Ah, I thought there may have been a way to do this via the IDE.

I was going to resort to code anyways

You guys are great... thanks for your help!