I have a question for the use of PrefetchPath and filters. We use version 1.0.2005.1 Final from July 6th, 2006 with adapter templates.

Our datamodel contains the following entities []=PK, {}=FK :

Client                  :  [ClientId] ....
Compound            :  [ClientId], [CompoundId], ... 
Function                :  [ClientId], [FunctionId], {CompoundId}, ...
Location                :  [ClientId], [LocationId], {CompoundId}, ...
FunctionLocation :  [ClientId], [FunctionId], [LocationId], ...

In our application frequently relation data are indicated. In addition we must be able to filter both on “ClientId” and on “CompoundId”. The “CompoundId” is however not contained in the relation tables.

The code section is to show what we would like to do, but the generated sql commadn isn't valid (see above).

What do I have to make, so that the filter is possible to use columns from refrenced tables?

            string _client = "CLIENT1";
            string _compound = "COMP2";

            DataAccessAdapter _daa = new DataAccessAdapter();
            EntityCollection _flc = new EntityCollection(new FunctionLocationEntityFactory());

            IPrefetchPath2 _pp = new PrefetchPath2((int)EntityType.FunctionLocationEntity);
            _pp.Add(FunctionLocationEntity.PrefetchPathFunction);
            _pp.Add(FunctionLocationEntity.PrefetchPathLocation);

            IRelationPredicateBucket _filter = new RelationPredicateBucket();
            _filter.PredicateExpression.Add(
                PredicateFactory.CompareValue( 
                    FunctionLocationFieldIndex.ClientId, ComparisonOperator.Equal, _client));
            
            // Filter on Function.CompoundId 
            //_filter.PredicateExpression.Add(
            //  PredicateFactory.CompareValue(
            //      FunctionFieldIndex.CompoundId, ComparisonOperator.Equal, _compound));

            try
            {
                _daa.FetchEntityCollection(_flc, _filter, _pp);
                dataGridView1.DataSource = _flc;
            }
            catch (Exception exp)
            {
                MessageBox.Show(exp.Message);
            }

The generated SQL instruction is incorrect, since no reference on “Function” exists

SELECT   "XENCOS"."FunctionLocation"."ClientId" AS "ClientId",
         "XENCOS"."FunctionLocation"."FunctionId" AS "FunctionId",
         "XENCOS"."FunctionLocation"."LocationId" AS "LocationId",
         "XENCOS"."FunctionLocation"."Status",
         "XENCOS"."FunctionLocation"."ChangeUserid",
         "XENCOS"."FunctionLocation"."ChangeTimestamp"
FROM     "XENCOS"."FunctionLocation"
WHERE   ( ( "XENCOS"."FunctionLocation"."ClientId" =  'CLIENT1'
        AND "XENCOS"."Function"."CompoundId" =  'COMP2'))           

Hello,

if you want to add a predicate on a field wich is not the root element, you need to add the relation to the table in your relation predicate bucket. The prefethc path is only used to fill the entity and there is no link with the filter (except if you use a filter on your prefetch path directly).

So you have to add : _filter.relations.add(functionlocationentity.relation.function.....)

Thanks for the fast assistance, it brings the solution