We have the following situation:

IRelationPredicateBucket filter = new RelationPredicateBucket();
IPredicate filterComponent = PredicateFactory.CompareRange(
    ChildTableFieldIndex.Id, childIdList.ToArray());

filter.PredicateExpression.Add(filterComponent);

filter.Relations.Add(ChildTableEntity.Relations.MasterTableEntityUsingMaster_Id);

EntityCollection masterCollection = new EntityCollection (new MasterTableEntityFactory());

_adapter.FetchEntityCollection(masterCollection, filter);

fetch returns multiple identical rows from MasterTable:

SELECT Id, Value
FROM  MasterTable
INNER JOIN ChildTable ON  MasterTable.Id = ChildTable.Master_id
WHERE ChildTable.Id IN (9, 13, 10)) --(array of ChildTable.Id's)

We woluld like to know how to create the same sql statement, but with DISTINCT keyword!

SELECT DISTINCT Id, Value
FROM  MasterTable
INNER JOIN ChildTable ON  MasterTable.Id = ChildTable.Master_id
WHERE ChildTable.Id IN (9, 13, 10)) --(array of ChildTable.Id's)

Can this be done and how?

An object fetch always filters out duplicates. It uses DISTINCT if possible, but if not (because it would violate the SQL correctness), it will do it on the client.

So you fetch entities and there are duplicates in the collection? Or you examined the query and you missed DISTINCT?

The DISTINCT key word will not violate SQL correctness in this case.

There are no duplicate rows in the collection. The idea is to reduce load on the SQL server and to lower traffic between the SQL server and the client. In our case there is a significant amount of childTable Id's that will ultimately fetch only a couple of MasterTable's records. Is there a way to force the DISTINCT key word in the SQL statement?

It should add the distinct keyword in the query iwth the code you provided.

Note: this is true for v1.0.2004.1 of the runtime library. Version 1.0.2003.3 (older versions) don't have this feature.

Yes, we use v1.0.2004.1, but the generated sql statement doesn't have the DISTINCT keyword. We used SQLProfiler to capture generated statement, and added the DISTINCT keyword ourselves and statement worked OK!

SaX wrote:

Yes, we use v1.0.2004.1, but the generated sql statement doesn't have the DISTINCT keyword. We used SQLProfiler to capture generated statement, and added the DISTINCT keyword ourselves and statement worked OK!

Well, the codesnippet you posted above should give distinct: fetchentitycollection checks if there are relations specified, which there are, and then opts for no duplicates. The query generator checks if there are distinct violating types in the select list. (i.e. the fields in the master table). If that's not the case, it will emit DISTINCT (you don't supply a sortexpression, so there are no restrictions applied).

So why distinct isn't popping up in your query is beyond me...

If there are just a couple of master objects and a lot of child objects, you could also use a FieldCompareSetPredicate, which effectively creates a subquery for the child filter, which is in this case much more efficient.