I have three tables forming a classic m:n relation between clients and their (dog walking) service providers:

Client --> ClientServiceProvider --> ServiceProvider

In the ClientServiceProvider table, there is a field called "IsPrimary" to designate which service provider is the primary and which are the backups.

In the ServiceProvider table, there is a field called "IsActive" to designate which service providers are still with the company.

I am having difficulty creating the correct prefetch path on Client so that I get back only active ServiceProviders and so that I get the IsPrimary flag on the relationship - I've tried several different ways.

Can anyone help with this?

Does the designer allow me to pull IsPrimary over to the ServiceProviderEntity?

Thanks,

Jay

Hi Jay,

You should filter on the SubPath.Add method overload. This would limit the related ClientServiceProvider related entities. So, try this:

// the path
IPrefetchPath2 path= new PrefetchPath2((int)EntityType.ClientEntity);

// you need to filter the subpath
IPredicateExpression serviceProvPathFilter = new PredicateExpression(ClientServiceProviderFields.IsActive == true 
     & ClientServiceProviderFields.IsPrimary == true);

path.Add(ClientEntity.PrefetchPathClientServiceProvider, 0, serviceProvPathFilter)
     .SubPath.Add(ClientServiceProviderEntity.PrefetchPathService);


// fetch
EntityCollection<ClientEntity> clients = new EntityCollection<ClientEntity>();
using (DataAccessAdapter adapter = new DataAccessAdapter())
{
    adapter.FetchEntityCollection(clients, null, path);
}

Thanks David,

IsActive is in the ServiceProvider table not the link table ClientServiceProvider and I think that's where I'm having trouble. These two flags are in different tables.

I tried filtering on IsActive in the overload and it worked but it gave me all ClientServiceProvider entities but if the ServiceProvider entity was not active I didn't get it (so the filter worked) and I got a null reference exception on clientServiceProvider.ServiceProvider.

What I need is ONLY the ClientServiceProvider entities for which the ServiceProvider entity IsActive. So:

Client.ClientServiceProvider.ServiceProvider.IsActive == true

But I also need the ClientServiceProvider so I can tell if the provider IsPrimary so I have to use ClientEntity.PrefetchPathServiceProviderCollectionViaClientServiceProvider rather than just ClientEntity.PrefetchPathClientServiceProvider.

Maybe ClientEntity.PrefetchPathClientServiceProvider.SubPath.Add(ClientServiceProviderEntity.PrefetchPathServiceProvider) with an appropriate filter somewhere?

Any more help you could provide would be appreciated.

Thanks,

Jay

I got this to work.

The key was in the relations. It helps to RTFM (and adapt the correct example).

                    RelationCollection clientServiceProviderRelations = new RelationCollection();
                    IPredicateExpression clientServiceProviderFilter = new PredicateExpression();

                    // Get only active service providers
                    clientServiceProviderRelations.Add(
                        ClientServiceProviderEntity.Relations.ServiceProviderEntityUsingServiceProviderID);
                    clientServiceProviderFilter.Add(ServiceProviderFields.IsActive == true);

                    // Sort service providers by name
                    serviceProviderListSorter = new SortExpression(
                        ServiceProviderFields.LastName | SortOperator.Ascending);
                    serviceProviderListSorter.Add(
                        ServiceProviderFields.FirstName | SortOperator.Ascending);

                    // Add service provider information to the prefetch path element
                    prefetchPath.Add(
                        ClientEntity.PrefetchPathClientServiceProvider,
                        0,
                        clientServiceProviderFilter,
                        clientServiceProviderRelations,
                        serviceProviderListSorter).SubPath.Add(
                            ClientServiceProviderEntity.PrefetchPathServiceProvider);