Hello,

I have a problem while using Negative predicates. Two Tables (TblModule, TblModulPortMedikamente) have a 1:m relation. The relation is between the Fields TblModulPortMedikamenteFields.ModulIdDemand and TblModuleFields.ModulId.

I try to create a query which is the same to the sql statement:

SELECT * FROM        tblModule WHERE     (NOT EXISTS
                          (SELECT * FROM          tblmodulportmedikamente
                           WHERE      tblModule.modulid = tblmodulportmedikamente.modulID_demand))

Here my try with LLBLGEN PRO:

 public IList<ModulExchange> GetModuls()
        {
            EntityCollection<TblModuleEntity> moduls =
                    new EntityCollection<TblModuleEntity>(new TblModuleEntityFactory());

            RelationPredicateBucket bucket = new RelationPredicateBucket();

            bucket.Relations.Add(TblModuleEntity.Relations.TblModulPortMedikamenteEntityUsingModulIdDemand);
            
            bucket.PredicateExpression.Add( TblModulPortMedikamenteFields.ModulIdDemand != TblModuleFields.ModulId);

    
            ISortExpression sortExpression = new SortExpression(TblModuleFields.ModulId | SortOperator.Ascending);

            m_adapter.FetchEntityCollection(moduls, bucket, 0, sortExpression);
        
            return Converter.ConvertToIList(moduls);
        }

What is my fault?

your code generates something like this:

SELECT * FROM tblModule INNER JOIN tblmodulportmedikamente ON (tblModule.modulid = tblmodulportmedikamente.modulID_demand) WHERE tblmodulportmedikamente.modulID_demand <> tblModule.modulid ORDER BY
tblModule.modulid ASC

which kind of negates itself, so i guess you need to get your sql straight first.

Hello,

ok I am understanding. But how can I use the sql Statement NOT EXISTS with LLBLGEN?

Thanks

LordBauer

Yes you can. You should use _FieldCompareSetPredicate _. This is copied from manual (LLBLGenPro Help - using the generated code - Adapter - Filtering and sorting - The predicate system):

FieldCompareSetPredicates also allows you to define EXISTS () queries. It is then not necessary to specify an IEntityField2 object with the predicate's constructor (specify null / nothing) as it is ignored when building the SQL. Keep in mind that EXISTS() queries are semantically the same as IN queries and IN queries are often simpler to formulate.

A FieldCompareSetPredicate's constructor overload receives a _bool negate _parameter. Set it to true and you will obtain a "NOT EXISTS" clause.

Hello again,

I try this but the result is the same:

bucket.PredicateExpression.Add(new FieldCompareSetPredicate(
                  TblModuleFields.ModulId, null, TblModulPortMedikamenteFields.ModulIdDemand , null,
                  SetOperator.Exist, null, true));

Is there a chance to see the generate sql statement?

Thanks

LordBauer

You need to use something like:

PredicateExpression existsSubqueryFilter = new PredicateExpression(TblModuleFields.ModulId == TblModulPortMedikamenteFields.ModulIdDemand );

bucket.PredicateExpression.Add(
    new FieldCompareSetPredicate(
        null, null,
        TblModulPortMedikamenteFields.ModulIdDemand, null,
        SetOperator.Exist,
        existsSubqueryFilter,
        true)
    );

Ok now it works.

Thanks for all replys.

LordBauer