SELECT SERI_ID, PROSES_KODU FROM LY_IS_EMRI_PARCA P3, LY_IE_GERCEKLESEN_ISLEM IE3 WHERE EXISTS ( SELECT DISTINCT P1.SERI_ID FROM LY_IS_EMRI_PARCA P1, LY_IE_GERCEKLESEN_ISLEM IE1 WHERE EXISTS ( SELECT DISTINCT P.SERI_ID FROM LY_IS_EMRI_PARCA P, LY_IE_GERCEKLESEN_ISLEM IE WHERE IE.IS_EMRI_NO = P.IS_EMRI_NO AND IE.SIRA_NO = P.SIRA_NO AND IE.PARCA_NO = P.PARCA_NO AND P.SERI_ID = P1.SERI_ID AND IE.PROSES_KODU = 1 AND IE.DURUMU = 2) AND IE1.IS_EMRI_NO = P1.IS_EMRI_NO AND IE1.SIRA_NO = P1.SIRA_NO AND IE1.PARCA_NO = P1.PARCA_NO AND IE1.PROSES_KODU = 26 AND IE1.DURUMU = 2 AND P3.SERI_ID = P1.SERI_ID) AND IE3.IS_EMRI_NO = P3.IS_EMRI_NO AND IE3.SIRA_NO = P3.SIRA_NO AND IE3.PARCA_NO = P3.PARCA_NO AND IE3.PROSES_KODU IN (1,26) AND IE3.DURUMU = 2 ORDER BY SERI_ID

i am trying to write a query consists of many exists recursively on the same tables.

How can it be coded?

Probably the easiest way to do this is to create a view or stored procedure in the db that performs the query, and us a TypedView or RetrievalStoredProcedure call to get the results.

Otherwise, you would need to use the FieldCompareSetPredicate

Matt

i could not create a view for the query because there are many combinations of parameters. i write a function that generates a Predicate Recursively

private void GeneratePredicate(PredicateExpression filter, decimal[] dProsesKoduList, int iHizmet) { if (iHizmet == dProsesKoduList.Length) return; string sObjectAlias = "P" + iHizmet.ToString(); decimal dProsesKodu = dProsesKoduList[iHizmet];

        PredicateExpression filterHizmetDetay = new PredicateExpression();
        filterHizmetDetay.Add(LyIeGerceklesenIslemFields.ProsesKodu == dProsesKodu);
        filterHizmetDetay.Add(LyIeGerceklesenIslemFields.IsEmriNo == LyIsEmriParcaFields.IsEmriNo);
        filterHizmetDetay.Add(LyIeGerceklesenIslemFields.SiraNo == LyIsEmriParcaFields.SiraNo);
        filterHizmetDetay.Add(LyIeGerceklesenIslemFields.ParcaNo == LyIsEmriParcaFields.ParcaNo);
        GeneratePredicate(filterHizmetDetay, dProsesKoduList, iHizmet + 1);
        filterHizmetDetay.ObjectAlias = sObjectAlias;
        RelationCollection relCol = new RelationCollection();
        relCol.Add(LyIeGerceklesenIslemEntity.Relations.LyIsEmriParcaEntityUsingIsEmriNoSiraNoParcaNo,
            sObjectAlias);
        filter.Add(new FieldCompareSetPredicate(
               LyIsEmriParcaFields.SeriId, null, LyIsEmriParcaFields.SeriId,
               null, SetOperator.Exist, filterHizmetDetay, relCol, sObjectAlias, 0, null, false, null));
    }

But it does not the bind the entities that has the same name.

Can you give an example about generating exist predicates on the same entity by alias.

The given examples are very simple and not useful for writing complex queries.

Please keep in mind that EXISTS() queries are semantically the same as IN queries and IN queries are often simpler to formulate.

Anyway here is an example: The following query fetches all Employees who have sub-ordinates.

SELECT * FROM Employee e1
WHERE EXISTS (SELECT e2.employeeId FROM Employee e2 WHERE e2.ManagerId = e1.employeeId)

var managers = new EntityCollection<EmployeeEntity>();

var pred = new FieldCompareSetPredicate(null, null, EmployeeFields.EmployeeId.SetObjectAlias("e2"), null, SetOperator.Exist, (EmployeeFields.ManagerId.SetObjectAlias("e2") == EmployeeFields.EmployeeId));

var filter = new RelationPredicateBucket();
filter.PredicateExpression.Add(pred);

using(var adapter = new DataAccessAdapter())
{
    adapter.FetchEntityCollection(managers, filter);
}