Hi,

I am using the adapter template set, C# 2.0 and VS2005.

I have an interesting scenario to filter. I have a table for which the schema is provided below:

CREATE TABLE [dbo].[Destination](
    [DestinationID] [int] IDENTITY(1,1) NOT NULL,
    [Name] [varchar](50) NOT NULL,
    [IsActive] [bit] NOT NULL CONSTRAINT [DF_Destination_IsActive]  DEFAULT ((1)),
    [ParentID] [int] NULL,
    [SortOrder] [int] NOT NULL CONSTRAINT [DF_Destination_SortOrder]  DEFAULT ((1)),
    [VisibleLevel] [int] NOT NULL CONSTRAINT [DF_Destination_VisibleLevel]  DEFAULT ((1)),
    [GroupName] [varchar](50) NULL,
    [IsCountry] [bit] NOT NULL CONSTRAINT [DF_Destination_IsCountry]  DEFAULT ((0)),
    [IsResort] [bit] NOT NULL CONSTRAINT [DF_Destination_IsResort]  DEFAULT ((0)),
 CONSTRAINT [PK_Destination] PRIMARY KEY CLUSTERED 
(
    [DestinationID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

Each value is related to its parent through ParentId which is the destination Id (PK) of its parents value.

Following is a image of data in the table:

What I need to do is display all values in a combobox for which VisibleLevel == 1. Now when a user selects one of these options I need to display only the values where IsResort == true but is the child of the selected value.

Its somewhat difficult to explain but say you had the following values:

UK
        England
                   London  -- IsResort = true
        Scotland
                   Fort Williams  -- IsResort = true
Spain
        Canaries
                  Tenerife
                            Los Christos  -- IsResort = true
                            Costa Adeje  -- IsResort = true
                            Playa Paraiso  -- IsResort = true

Now I can get the filtering to work for the England or Scotland selection by getting the entity that is selecting and using the GetRelationInfo() to get the relations and using this to get the data from the database. Since this is only I relation deep.

But if I select UK, Spain or Canaries I don't know who to get the associated values by jumping the hierarchy.

I hope I have explained this well, any help would be greatly appreciated.

Hameed.

You could try something like this

int destinationId;
EntityCollection subDestinations = new EntityCollection();
if(Int32.Parse(ddlDestination.SelectedValue, out destinationId))
{
    EntityCollection<DestinationEntity> subDestinations = new EntityCollection<DestinationEntity>();
    using (DataAccessAdapter adapter = new DataAccessAdapter())
    {
        IRelationPredicateBucket filter = new RelationPredicateBucket();
        filter.PredicateExpression.Add(DestinationFields.ParentId == destinationId);
        filter.PredicateExpression.AddWithAnd(DestinationFields.IsResort == true);
        adapter.FetchEntityCollection(subDestinations, filter);
    }
}

Hi bclubb,

Thanks for your response but this will not resolve my problem. Let me explain my scenario. if for instance a user has the following hierarchy:

Spain
      Canaries
             Tenerife
                    Los Christianos
                    Costa Adeje
                    Playa Paraiso

In a table this will be represented like this.

Id    Name                  ParentId      IsResort   VisibleLevel
1      Spain                    1                 False        1   
2      Canaries            1                  false         1 
3      Tenerife             2                 false          1 
4      Los Christianos   3                true            2
5      Costa Adeje         3                 true             2 
6      Playa Paraiso         3               true              2
7      UK                         7              false            1
8      England                7              false            1
9      Scotland              7                false           1
10    London                  8               true             2
11     Fort Williams        9                  true            2
12     Wales                   7                  false            1

where Los Christianos, Costa Adeje and Playa Paraiso are all resorts. What I want to do is if a user clicks on tenerife to get all resorts under that. if a use clicks on canaries to only show all the resorts in the second combo and ofcourse if the user clicks on spain to again only show all the resorts in the second combo.

VisibleLevel desfines which combobox to bind data to.

I am using the following code the return the initial visiblelevel 1 data:

            using (DataAccessAdapter da = new DataAccessAdapter())
            {               
                IRelationPredicateBucket bucket = new RelationPredicateBucket();
                bucket.PredicateExpression.Add(DestinationFields.VisibleLevel == 1);
                ISortExpression sorter = new SortExpression(DestinationFields.SortOrder | SortOperator.Ascending);

                da.FetchEntityCollection(entityCollection1,bucket,0,sorter);                
            }

Now when the combobox value changes I use the following code to get the resorts within that hierarchy

            DestinationEntity d = (DestinationEntity)comboBox1.SelectedValue;
            
            if (d != null)
            {
                using (DataAccessAdapter da = new DataAccessAdapter())
                {
                    entityCollection2.Clear();

                    IRelationPredicateBucket b = new RelationPredicateBucket();                   b.PredicateExpression.Add(DestinationFields.ParentId == d.DestinationId);                 
                    b.PredicateExpression.Add(DestinationFields.IsResort == true);

                    ISortExpression sorter = new SortExpression(DestinationFields.SortOrder | SortOperator.Ascending);

                    da.FetchEntityCollection(entityCollection2, b, 0, sorter);
                    

                }
            }

But this obly gets it for one level deep (this is due to ParentId=DestinationId). But what I want, like I expalined above, is to get all resorts within that hierarchy irrespective which level of the hierarchy is selected.

Regards Hameed.

The approach I'd do for such a situation would be to load the entire table in an entityCollection or any other data structure.

Then do the recursive search for leaves (resorts) at the client side (not the database side). this would be the best performance approach I suppose.

Anyway please check the following threads: http://www.llblgen.com/TinyForum/Messages.aspx?ThreadID=5493 http://www.llblgen.com/TinyForum/Messages.aspx?ThreadID=5880