I have an entity collection of Students. Each Student Entity can have multiple StudSchool collection. Both the Student and StudSchool have a SchlId field. I would like to filter the StudSchool collection the the SchlId in the associated Student entity.

Student entity: (student id:123456 schl:12) StudSchool entity: schl: 10 StudSchool entity: schl: 12 <--- I just want this one retrieved since the schl id's match StudSchool entity: schl: 14

How would i go about doing this in 2.6 adapter non-linq?

thanks,

fishy

You have to create a custom relation and pass it to the prefetchPath. See the example below. I'm trying to fetch one Customer (ALFKI) and its related Orders, additionally I want all prefetched orders filtered by the country the customer is. Here is the code:

CustomerEntity customer = new CustomerEntity("ALFKI");

// create the custom relation that will filter out the prefetched collection
IRelationCollection pathRelations = new RelationCollection(
    new EntityRelation(CustomerFields.Country,
        OrderFields.ShipCountry, RelationType.OneToMany));          

// define my prefetch with the custom relation
IPrefetchPath2 path = new PrefetchPath2((int) EntityType.CustomerEntity);
path.Add(CustomerEntity.PrefetchPathOrders, 0, null, pathRelations);

// fetch results
using (DataAccessAdapter adaper = new DataAccessAdapter())
{
    adaper.FetchEntity(customer, path);
}

This is the generated sql code for the prefetch collection:

SELECT DISTINCT 
    [Northwind].[dbo].[Orders].[OrderID] AS [OrderId], 
    [Northwind].[dbo].[Orders].[CustomerID] AS [CustomerId], 
    [Northwind].[dbo].[Orders].[EmployeeID] AS [EmployeeId], 
    [Northwind].[dbo].[Orders].[OrderDate], 
    [Northwind].[dbo].[Orders].[RequiredDate], 
    [Northwind].[dbo].[Orders].[ShippedDate], 
    [Northwind].[dbo].[Orders].[ShipVia], 
    [Northwind].[dbo].[Orders].[Freight], 
    [Northwind].[dbo].[Orders].[ShipName], 
    [Northwind].[dbo].[Orders].[ShipAddress], 
    [Northwind].[dbo].[Orders].[ShipCity], 
    [Northwind].[dbo].[Orders].[ShipRegion], 
    [Northwind].[dbo].[Orders].[ShipPostalCode], 
    [Northwind].[dbo].[Orders].[ShipCountry] 

FROM ( [Northwind].[dbo].[Customers]  INNER JOIN [Northwind].[dbo].[Orders]  
       ON  [Northwind].[dbo].[Customers].[Country]=[Northwind].[dbo].[Orders].[ShipCountry]) 

WHERE ( ( ( [Northwind].[dbo].[Orders].[CustomerID] = @CustomerId1)))
    Parameter: @CustomerId1 : StringFixedLength. Length: 5. Precision: 0. Scale: 0. Direction: Input. Value: "ALFKI".

... which is -I think- what you want.

Great. Thank you.