Hi,

I have used LLBLGen for about 4 months now and like it. I am though struggling with some of the more complicated sql queries. I would like to use the predicate system instead of running to view's or stored procedures which I have used heavily in the past.

Here is one query I have not been able to re-write with the LLBLGEN system:

select p.* from postur p where (p.efni like '%skeyti%' or p.texti like '%skeyti%' or p.skradAfNetfang like '%skeyti%') and p.senda = 1 and ( p.id in( select p.id from postur p, hopur_felagi hf, felagi f where f.netfang = 'email@demo.com' and f.kennitala = hf.fk_felagi and p.laesing_hopid = hf.fk_hopur) or p.id in( select p.id from postur p where p.laesing_hopid is null))

order by efni

As you can see, I need all items from the "postur" table that apply to the search conditions and then which have the comlumn laesing_hopurid as defined by the two subqueries.

I have studied the documentation and seen that I can define a simple subquery like: Customer.CustomerID IN (SELECT CustomerID FROM Orders WHERE Employee=2) but I have not seen an example where the subquery has joins.

Agust

Customer.CustomerID IN (SELECT CustomerID FROM Orders WHERE Employee=2)

To implment the above you need to use FieldCompareSetPredicate. And its CTor has overloads that accepts an IRelationCollection parameter which should be used to define Relations/Joins.

Thank you much,

Your comment points me in the right way. I actually realized that my sql query was overly complicated, this is my new version:

select p.* from postur p where (p.efni like '%skeyti%' or p.texti like '%skeyti%' or p.skradAfNetfang like '%skeyti%') and p.senda = 1 and ( p.id in( select p.id from postur p, hopur_felagi hf, felagi f where f.netfang = 'email@demo.com' and f.kennitala = hf.fk_felagi and p.laesing_hopid = hf.fk_hopur)

     or p.laesing_hopid is null)

order by p.efni

As I know myself, when reading forums, how good it can be to see the end results - here is mine after I got it to work:

public static List<Postur> FinnaSkeyti(string p_sLeit, StadaSkeytis pStada, string p_NetfangNotanda) { p_sLeit = p_sLeit.Trim(); p_sLeit = "%" + p_sLeit + "%"; EntityCollection<PosturEntity> safnP = new EntityCollection<PosturEntity>(); /* select p.*--query A from postur p where (p.efni like '%skeyti%' or --preLeit p.texti like '%skeyti%' or p.skradAfNetfang like '%skeyti%') and p.senda = 1 --preSenda and ( p.id in( select p.id --query B from postur p, hopur_felagi hf, felagi f where f.netfang = 'email@demo.com' and f.kennitala = hf.fk_felagi and p.laesing_hopid = hf.fk_hopur)

            or p.laesing_hopid is null) --preLaesing
            order by p.efni
            */   

// To implment the above you need to use FieldCompareSetPredicate. //And its CTor has overloads that accepts an IRelationCollection parameter which should be used to define Relations/Joins. RelationPredicateBucket queryA = new RelationPredicateBucket(); IPredicateExpression preLeit = new PredicateExpression(); IPredicateExpression preSenda = new PredicateExpression(); IPredicateExpression preLaesing = new PredicateExpression(PosturFields.LaesingHopId == DBNull.Value); preLeit.Add(new FieldLikePredicate(PosturFields.Efni, null, p_sLeit)); preLeit.AddWithOr(new FieldLikePredicate(PosturFields.SkradAfNetfang, null, p_sLeit)); preLeit.AddWithOr(new FieldLikePredicate(PosturFields.Texti, null, p_sLeit));

        if (pStada == StadaSkeytis.Uppkast)
        {
            preSenda.Add(PosturFields.Senda == false);
        }
        else if (pStada == StadaSkeytis.Senda)
        {
            preSenda.Add(PosturFields.Senda == true);
        }
        else
        {
            preSenda.Add(PosturFields.Senda == true | PosturFields.Senda == false);
        }

        IRelationCollection relCol = new RelationCollection();
        relCol.Add(PosturEntity.Relations.HopurEntityUsingLaesingHopId);
        relCol.Add(HopurEntity.Relations.HopurFelagiEntityUsingFkHopur);
        relCol.Add(HopurFelagiEntity.Relations.FelagiEntityUsingFkFelagi);          

        PredicateExpression preSeinniHluti = new PredicateExpression();
        FieldCompareSetPredicate setPred = new FieldCompareSetPredicate(
            PosturFields.Id, null, PosturFields.Id, null, SetOperator.In, (FelagiFields.Netfang == p_NetfangNotanda), relCol, false);

        preSeinniHluti.Add(setPred);
        preSeinniHluti.AddWithOr(preLaesing);

        queryA.PredicateExpression.Add(preLeit);
        queryA.PredicateExpression.AddWithAnd(preSenda);
        queryA.PredicateExpression.AddWithAnd(preSeinniHluti);

        using (DataAccessAdapter da = new DataAccessAdapter())
        {
            da.FetchEntityCollection(safnP, queryA, -1,
                new SortExpression(PosturFields.Efni | SortOperator.Ascending));
        }
        int length = safnP.Count;
        List<Postur> safnPTilBaka = new List<Postur>(length);
        for (int i = 0; i < length; i++)
        {
            safnPTilBaka.Add(new Postur(safnP[i]));
        }
        return safnPTilBaka;

}

Thanks for your help - Agust

Thanks for the feedback.