Using llblgen 2.0 demo, self servicing, .net 1.1

I have the following method that includes a custom filter as you can see.

public void AddFilter(ref PredicateExpression predicate, ref RelationCollection relations, IInputObject input, bool negativeFilter,bool source)
        {
            try
            {
                if (source)
                {
                    //calculate the ltv
                    double ltv=(input.LoanSize/input.Valuation)*100;
                    //Flat
                    if(input.PropertyType[0]==Enums.PropertyTypeStringConstants.Flat)
                    {
                    
                        PredicateExpression pe =new PredicateExpression();
                        pe.Add(MaxltvforflatFields.Storeys >= input.FloorNumber);
                    
                        relations.Add(ProductEntity.Relations.Lender2EntityUsingLender2Id);
                        relations.Add(Lender2Entity.Relations.MaxltvforflatEntityUsingLender2Id).CustomFilter=pe;
                    
                        predicate.AddWithAnd(MaxltvforflatFields.MaxLtv >= ltv);
                    }
                }
            }
            catch(Exception ex)
            {throw ex;}
        }

The custom filter returns all MaxLtvForFlat entities where the value of 'Storeys' is >= the input.Floornumber. I actually only want it to return the first entity that passes the filter when sorted by 'Tier' (Each MaxLtvForFlat entity contains a 'Tier' integer)

Is there a way of achieving this using a custom filter? The reason being that I only want 'MaxltvforflatFields.MaxLtv >= ltv' to filter against the first entity that passes the 'MaxltvforflatFields.Storeys >= input.FloorNumber'

Would be really grateful for any help....

Please follow the following guidelines: http://www.llblgen.com/tinyforum/Messages.aspx?ThreadID=7725

Most important is to post the desired SQL query, with tables structure. Also post the complete code snippet (the fetch part was not available.)

Sorry for the lack of information previously - I hope this is better...

As before - using llblgen 2.0, .Net 1.1, Self Servicing

I am performing a GetMulti on a ProductCollection object, passing in PredicateExpression and RelationCollection that have been built by calling AddFilter methods on various objects.

public void AddFilter(ref PredicateExpression predicate, ref RelationCollection relations, IInputObject input, bool negativeFilter,bool source)
        {
            try
            {
                if (source)
                {
                    //calculate the ltv
                    double ltv=(input.LoanSize/input.Valuation)*100;
                    //Flat
                    if(input.PropertyType[0]==Enums.PropertyTypeStringConstants.Flat)
                    {
                    
                        PredicateExpression pe =new PredicateExpression();
                        pe.Add(MaxltvforflatFields.Storeys >= input.FloorNumber);
                    
                        relations.Add(ProductEntity.Relations.Lender2EntityUsingLender2Id);
                        relations.Add(Lender2Entity.Relations.MaxltvforflatEntityUsingLender2Id).CustomFilter=pe;
                    
                        predicate.AddWithAnd(MaxltvforflatFields.MaxLtv >= ltv);
                    }
                }
            }
            catch(Exception ex)
            {throw ex;}
        }

The code that calls this is similar to:

ProductCollection products=new ProductCollection();

PredicateExpression pe=new PredicateExpression();
RelationCollection relations=new RelationCollection();

//  
//Code to call filter objects passing refs to pe and relations
//
    
products.GetMulti(pe,relations);

The table structure is as follows:

LENDER2 (Lender2Id(pk), ...) PRODUCT (ProductID(pk), Lender2ID(fk for LENDER2), ...) MAXLTVFORFLAT(FlatId(pk), Lender2ID(fk for LENDER2), Tier, Storeys, MaxLtv)

The SQL required to perform the filter would be similar to this...

Select top 1 MAXLTVFORFLAT.MaxLTV from MAXLTVFORFLAT where 
MAXLTVFORFLAT.lender2id =
(Select LENDER2.lender2id From LENDER2 Where LENDER2.Lender2Id in 
(Select PRODUCT.Lender2Id from PRODUCT where PRODUCT.ProductId='x')) AND MAXLTVFORFLAT.MaxLTV > 'y'
order by Tier

... where 'y' is the 'ltv' variable in my code below and 'x' represents the 'ProductId' for the current ProductEntity

Basically, my current code is missing the 'Top 1' and 'order by Tier' parts of the SQL so returns multiple rows where I only want one - but I can't see any way of adding this to the current code. I could just add another field to the table if necessary so I can specify a min and max number for 'Storeys' and change the custom filter predicate to a FieldBetweenPredicate but I was hoping not to.

Is there a reason you don't create a SortExpression and call GetMulti as this.

products.GetMulti(pe, 1, sorter, relations);

The reason for not doing as you suggest is that I will have anywhere up to 30 different filter objects that can be called, each adding predicates to the predicate expression. I only wanted to call GetMulti on the ProductCollection once.

CustomFilter is a property which is used to add a filter to the ON clause of a join.

So if you specify a relation, say Customer - Order, you normally get an ON clause which simply compares the PK-FK combination. If you want to add an additional filter to that, you can set the CustomFilter property of a relation.

Limitations of resultsets are done on the methodcall code. So you're using selfservicing, you can specify a maxNumberOfItemsToReturn value, which is the # of items to return or 0 if you want to fetch all (default).

So if you want just 1, simply specify 1 as maxNumberOfItemsToReturn (use the proper overload of GetMulti of course).

Thanks for your help - I see that I need to change my approach to this.