Hello All,

I am trying to create a query in llblgen code side which is listed below as sql :

SELECT 
   DISTINCT "public"."Document"."Id" 
FROM 
( "public"."Document"  INNER JOIN "public"."PropertyValues"  ON ( "public"."PropertyValues"."OwnerId" = "public"."Document"."Id")) 
WHERE 
( 
    ( 
    "public"."Document"."Id" IN 
        (
            SELECT "OwnerId" FROM 
            (
                SELECT "OwnerId", COUNT("public"."PropertyValues"."Id") AS "Count" FROM "public"."PropertyValues" 
                                WHERE 
                                ( 
                                    -- ( Conditions here )
                                ) 
                                GROUP BY "public"."PropertyValues"."OwnerId"
            ) "countQuery"
                    Where  "countQuery"."Count"= -- ( Number of Conditions here )               
        ) 
    )
)

The code that I created is as :

 using (var adapter = new DataAccessAdapter())
                {

                    var filter = new RelationPredicateBucket();
                    var propertyKeywordExpression = new PredicateExpression();

// Here the predicate expression ( conditions ) are created
                    foreach (var searchCriteria in propertySearchParameters)
                    {
                        var filterSearchCriteria = "%" + searchCriteria.value.ToUpper(new CultureInfo("tr-TR")) + "%";
                        var nameField = 
PropertyValuesFields.Value.SetExpression(new DbFunctionCall("TUPPER", new object[] { PropertyValuesFields.Value }));
                        var namePredicate = new FieldLikePredicate(nameField, null, filterSearchCriteria);
                        namePredicate.CaseSensitiveCollation = true;
                        var propertyExpression = new PredicateExpression();

                        propertyExpression.AddWithAnd(namePredicate);
                        propertyExpression.AddWithAnd(PropertyValuesFields.PropertyId == searchCriteria.propertyId);

                        propertyKeywordExpression.AddWithOr(propertyExpression);
                    }


                    if (propertySearchParameters.Count != 0)
                    {
                        var x = (new EntityField2("Rows",
                                              new ScalarQueryExpression(PropertyValuesFields.Id.SetAggregateFunction(AggregateFunction.Count),
                                                  (propertyKeywordExpression),null,null,new GroupByCollection(PropertyValuesFields.OwnerId))));
                        
                
                        filter.PredicateExpression.AddWithAnd(new FieldCompareSetPredicate(DocumentFields.Id, null,
                                                                                    PropertyValuesFields.OwnerId, null,
                                                                                    SetOperator.In,
                                                                                    new PredicateExpression(x == propertySearchParameters.Count)));

                        DynamicRelation relation = new DynamicRelation(EntityType.DocumentEntity, JoinHint.Inner,
                                                                    EntityType.PropertyValuesEntity, "", "", new PredicateExpression(PropertyValuesFields.OwnerId == DocumentFields.Id));

                        filter.Relations.Add(relation);
                    }

                

                    var entityCollection = new EntityCollection<DocumentEntity>();
                
                    adapter.FetchEntityCollection(entityCollection, filter, 0, null, null);

The problem is I am always receiving "more than one rows returned from field expression" error.

How should I write such code to create the desired SQL?

King Regards,

You can create a DynamicRelation, but, just curious, could your query be written like this?:

SELECT 
    DISTINCT "public"."Document"."Id" 

FROM 
    "public"."Document" 
    INNER JOIN "public"."PropertyValues" 
        ON  "public"."PropertyValues"."OwnerId" = "public"."Document"."Id"

WHERE 
    "public"."Document"."Id" IN 
    (   
        SELECT "OwnerId", COUNT("public"."PropertyValues"."Id") AS "Count" FROM "public"."PropertyValues" 
        WHERE 
        ( 
            -- ( Conditions here )
        ) 
        GROUP BY "public"."PropertyValues"."OwnerId"
        HAVING COUNT("public"."PropertyValues"."Id") = -- ( Number of Conditions here )                 
    )

In such case you only need one FieldCompareSetPredicate without relations, just a groupBy with a having filter.