Hi

I have seen this problem posted elsewhere - but there are only hints at the answer and not an idiot proof one for the likes of myself!

Here's the SQL LLBL is generating for me:

SELECT DISTINCT [rsenergy_master].[dbo].[PageSection].[PageSectionID] AS [PageSectionId], [rsenergy_master].[dbo].[PageSection].[Name] FROM ( [rsenergy_master].[dbo].[PageSection]  INNER JOIN [rsenergy_master].[dbo].[PageSection]  ON  [rsenergy_master].[dbo].[PageSection].[PageSectionID]=[rsenergy_master].[dbo].[PageSection].[ParentSectionID]) WHERE ( ( [rsenergy_master].[dbo].[PageSection].[ParentSectionID] = @ParentSectionId1)) ORDER BY [rsenergy_master].[dbo].[PageSection].[Name] ASC

And here's my C#

using (DataAccessAdapter adapter = new DataAccessAdapter())
            {
                ResultsetFields fields = new ResultsetFields(2);
                // simply set the fields in the indexes, which will use the field name for the column name
                fields[0] = PageSectionFields.PageSectionId;
                fields[1] = PageSectionFields.Name;

                RelationPredicateBucket filter = new RelationPredicateBucket(PageSectionFields.ParentSectionId == m_iParentSectionID);
                filter.Relations.Add(PageSectionEntity.Relations.PageSectionEntityUsingParentSectionId);

                ISortExpression sorter = new SortExpression();
                sorter.Add(new SortClause(PageSectionFields.Name, null, SortOperator.Ascending));

                IDataReader reader = adapter.FetchDataReader(fields, filter, CommandBehavior.CloseConnection, 0, sorter, false);

                while (reader.Read())
                {
                    Response.Write(reader.GetString(1));
                }

                reader.Close();
            }

I'm pretty much finding my way around LLBL by chopping things here and pasting things there after reading the manual, so a softly softly approach would be appreciated

Also, I would like to know how to specify in my filter that not only should PageSectionFields.ParentSectionId == m_iParentSectionID but PageSectionFields.SiteID == m_iSiteID too - would I have to add two filter.Relations.Add calls like this:

                RelationPredicateBucket filter = new RelationPredicateBucket(PageSectionFields.ParentSectionId == m_iParentSectionID && PageSectionFields.SiteId = m_iSiteID);
                filter.Relations.Add(PageSectionEntity.Relations.PageSectionEntityUsingParentSectionId);
                filter.Relations.Add(PageSectionEntity.Relations.SiteEntityUsingSiteId);

Any help on both problems would be of outstanding help - thanks very much.

Darren

Hi Darren,

using (DataAccessAdapter adapter = new DataAccessAdapter())
            {
                ResultsetFields fields = new ResultsetFields(2);
                // simply set the fields in the indexes, which will use the field name for the column name
                fields[0] = PageSectionFields.PageSectionId;
                fields[1] = PageSectionFields.Name;

                RelationPredicateBucket filter = new RelationPredicateBucket(PageSectionFields.ParentSectionId == m_iParentSectionID);
                filter.Relations.Add(PageSectionEntity.Relations.PageSectionEntityUsingParentSectionId);

                ISortExpression sorter = new SortExpression();
                sorter.Add(new SortClause(PageSectionFields.Name, null, SortOperator.Ascending));

                IDataReader reader = adapter.FetchDataReader(fields, filter, CommandBehavior.CloseConnection, 0, sorter, false);

                while (reader.Read())
                {
                    Response.Write(reader.GetString(1));
                }

                reader.Close();
            }

I'm pretty much finding my way around LLBL by chopping things here and pasting things there after reading the manual, so a softly softly approach would be appreciated Wink

As there are two tables with same names involved, you need to use Aliases. You can do this as follow:

1. Use this IRelationCollection.Add method overload:

Overloads Public Overridable Function Add( _
   ByVal relationToAdd As IEntityRelation, _
   ByVal aliasRelationStartEntity As String, _
   ByVal aliasRelationEndEntity As String, _
   ByVal hint As JoinHint _
) As IEntityRelation

so your code would look like:

filter.Relations.Add(PageSectionEntity.Relations.PageSectionEntityUsingParentSectionId, "A", "B", JoinHint.Inner);

1. Set the alias at the fields you should to. You can achieve this using IEntityField2.SetObjectAlias method. So your code would look like:

// I don't know if here you must use "A" or "B"... Here you also can use fields.DefineField(...).
fields[0] = PageSectionFields.PageSectionId.SetObjectAlias("A");
fields[1] = PageSectionFields.Name.SetObjectAlias("A");

// I don't know if here you must use "A" or "B"...
RelationPredicateBucket filter = new RelationPredicateBucket(PageSectionFields.ParentSectionId.SetObjectAlias("A") == m_iParentSectionID);
                
...
// I don't know if here you must use "A" or "B"...
ISortExpression sorter = new SortExpression();
sorter.Add(new SortClause(PageSectionFields.Name.SetObjectAlias("A"), null, SortOperator.Ascending));

Also, I would like to know how to specify in my filter that not only should PageSectionFields.ParentSectionId == m_iParentSectionID but PageSectionFields.SiteID == m_iSiteID too - would I have to add two filter.Relations.Add calls like this:

RelationPredicateBucket filter = new RelationPredicateBucket(PageSectionFields.ParentSectionId == m_iParentSectionID && PageSectionFields.SiteId = m_iSiteID);
                filter.Relations.Add(PageSectionEntity.Relations.PageSectionEntityUsingParentSectionId);
filter.Relations.Add(PageSectionEntity.Relations.SiteEntityUsingSiteId);

As your are referencing fields only of PageSectionEntity, you don't need to add the other relation . If you would refer SiteEntity fields then you should add such relation.