Data paging and sorting on related entity field

Posts   
 
    
chrishilton avatar
Posts: 49
Joined: 26-Jun-2007
# Posted on: 15-May-2008 10:33:51   

I'm using LLBLGen Pro 2.5 Final with adapter and .NET 2.0/c# connecting to both SQL Server 2000 and 2005 dBs.

When data paging I need to sort by a related entity field to enable the correct grouping of records in my front-end. So I am calling FetchEntityCollection with my collection, pageNumber, pageSize etc and a sorter which generates a SQL query (that I've simplified) like this:


SELECT [dbo].[Activity].[recID] AS [RecId], 
[dbo].[Activity].[subject] AS [Subject],
...
FROM [dbo].[Activity]  INNER JOIN [dbo].[ActivityType]  ON [dbo].[ActivityType].[recID]=[dbo].[Activity].[typeID]) 

ORDER BY [dbo].[ActivityType].[sortOrder] ASC,
[dbo].[Activity].[subject] ASC

When executing this fetch no data paging is occuring and I suspect this is because LLBLGen wants to include a DISTINCT statement as I have included a join and can't because "ORDER BY items must appear in the select list if SELECT DISTINCT is specified".

My question is when using FetchEntityCollection is there any way to include my related field "[ActivityType].[sortOrder]" in the select statement? I have tried adding this to an include list of fields using a ExcludeIncludeFieldsList but the related field is still not appearing in the select list.

Walaa avatar
Walaa
Support Team
Posts: 14946
Joined: 21-Aug-2005
# Posted on: 15-May-2008 11:32:32   

A simple solution is to fetch the fields you need using a DynamicList into a dataReader that could be projected into an entityCollection.

chrishilton avatar
Posts: 49
Joined: 26-Jun-2007
# Posted on: 15-May-2008 11:39:41   

Thanks for your response Walaa.

I understand that your suggested approach may provide a solution to a number of the paging issues that I've encountered and my only reservation is the amount of rework required to my business manager classes.

Could you confirm that it isn't possible to include the related field in my select list when using FetchEntityCollection?

And if I do go down the route of using a DynamicList, dataReader and projection could you tell me if this is compatible with the IPrefetchPath2 that I'm using for the FetchEntityCollection?

Walaa avatar
Walaa
Support Team
Posts: 14946
Joined: 21-Aug-2005
# Posted on: 15-May-2008 15:54:23   

Could you confirm that it isn't possible to include the related field in my select list when using FetchEntityCollection?

You can extend the entityFactory, please check the following article, look for "Extending the CustomerEntityFactory", inside "Step 6": http://weblogs.asp.net/fbouma/archive/2006/06/09/LLBLGen-Pro-v2.0-with-ASP.NET-2.0.aspx

And if I do go down the route of using a DynamicList, dataReader and projection could you tell me if this is compatible with the IPrefetchPath2 that I'm using for the FetchEntityCollection?

Actually I don't see a good fit here. So maybe if take this path, you'd have to do it in 2 steps. step 1: fetch the paged dynamicList (includins the PK & the extraField only). step 2: Use the fetched PKs as a filter to fetch the entityCollection along with the PrefetchPaths.

chrishilton avatar
Posts: 49
Joined: 26-Jun-2007
# Posted on: 15-May-2008 19:04:01   

Thanks again Walaa, the extended factories seems to be working for me. Will try and remember to post my code for reference once I've fully tested all my paging & sorting scenarios.

chrishilton avatar
Posts: 49
Joined: 26-Jun-2007
# Posted on: 22-May-2008 11:37:21   

As promised here's my code for anyone interested or suffering from insomnia wink

ActivityManager class method:


public static void FetchCollection(IEntityCollection2 collection, IRelationPredicateBucket filter, int numberOfRecordsToReturn, ISortExpression sorter, IPrefetchPath2 prefetchPath, int pageNumber, int pageSize)
{
    // fields that need to be excluded if LLBL Gen is going to use a distinct statement
    ExcludeIncludeFieldsList excludeFields = GenericManager.DistinctExcludeFields(collection, filter, pageSize);

    // save original factory so this can be reverted back so that subsequent calls use the original
    IEntityFactory2 originalFactory = collection.EntityFactoryToUse;

    // add related entity fields from the sort expression to the select list if LLBL Gen is going to use a distinct statement
    GenericManager.SetEntityFactoryToUse(collection, filter, sorter, pageSize);

    using (DataAccessAdapter adapter = new DataAccessAdapter(Config.MainConnectionString))
    {
        // fetch the collection of entities
        adapter.FetchEntityCollection(collection, filter, numberOfRecordsToReturn, sorter, prefetchPath, excludeFields, pageNumber, pageSize);

        // now fetch any excluded fields
        if (excludeFields.Count > 0) adapter.FetchExcludedFields(collection, excludeFields);

        // revert factory back to original so that subsequent calls use the original
        collection.EntityFactoryToUse = originalFactory;
    }
}

The above method calls this method:


/// <summary>
/// Returns a list of fields that are not compatible with the Distinct statement
/// to exclude from a FetchCollection when a Distinct statement 
/// is required by LLBL Gen as a join is included and performing data paging
/// </summary>
public static ExcludeIncludeFieldsList DistinctExcludeFields(IEntityCollection2 collection, IRelationPredicateBucket filter, int pageSize)
{
    ExcludeIncludeFieldsList excludeFields = new ExcludeIncludeFieldsList();

    if (filter != null && filter.Relations.Count > 0 && pageSize > 0)
    {
        using (DataAccessAdapter adapter = new DataAccessAdapter())
        {
            IEntityFields2 fields = collection.EntityFactoryToUse.CreateFields();
            foreach (IEntityField2 field in fields)
            {
                IFieldPersistenceInfo fieldInfo = adapter.GetFieldInfo(field);
                if (fieldInfo != null)
                {
                    switch (fieldInfo.SourceColumnDbType)
                    {
                        case (int)SqlDbType.Image:
                        case (int)SqlDbType.NText:
                        case (int)SqlDbType.Text:
                            excludeFields.Add(field);
                            break;
                    }
                }
            }
        }
    }

    return excludeFields;
}

and this method:


/// <summary>
/// Set the collection's EntityFactoryToUse to include any related entity fields that exist 
/// in the sort expression and are therefore required in the select list when a Distinct statement 
/// is required by LLBL Gen as a join is included and performing data paging
/// </summary>
public static void SetEntityFactoryToUse(IEntityCollection2 collection, IRelationPredicateBucket filter, ISortExpression sorter, int pageSize)
{
    string strCollectionObjectName = collection.EntityFactoryToUse.CreateFields()[0].ContainingObjectName;
    IEntityFields2 additionalFields = new EntityFields2(0);

    if (sorter != null && filter != null && filter.Relations.Count > 0 && pageSize > 0)
    {
        for (int intIndex = 0; intIndex < sorter.Count; intIndex++)
        {
            if (sorter[intIndex].FieldToSortCore.ContainingObjectName != strCollectionObjectName)
            {
                // add sort field or expression to select list
                additionalFields.Expand(1);
                additionalFields.DefineField((IEntityField2)sorter[intIndex].FieldToSortCore, additionalFields.Count - 1);
            }
        }

        if (additionalFields.Count > 0)
        {
            switch (strCollectionObjectName)
            {
                case "AccountEntity":
                    collection.EntityFactoryToUse = new AccountEntityFactoryExtended(additionalFields);
                    break;
                case "ActivityEntity":
                    collection.EntityFactoryToUse = new ActivityEntityFactoryExtended(additionalFields);
                    break;
                case "AttributeEntity":
                    collection.EntityFactoryToUse = new AttributeEntityFactoryExtended(additionalFields);
                    break;
                case "ContactEntity":
                    collection.EntityFactoryToUse = new ContactEntityFactoryExtended(additionalFields);
                    break;
                case "ProspectEntity":
                    collection.EntityFactoryToUse = new ProspectEntityFactoryExtended(additionalFields);
                    break;
                case "ResourceEntity":
                    collection.EntityFactoryToUse = new ResourceEntityFactoryExtended(additionalFields);
                    break;
                case "ViewEntity":
                    collection.EntityFactoryToUse = new ViewEntityFactoryExtended(additionalFields);
                    break;
            }
        }
    }
}

The above method references extended factory classes such as:


public class ActivityEntityFactoryExtended : ActivityEntityFactory
{
    private IEntityFields2 m_AdditionalFields;

    public ActivityEntityFactoryExtended(IEntityFields2 additionalFields) : base()
    {
        m_AdditionalFields = additionalFields;
    }

    /// <summary>
    /// Create the fields for the entity and adjust the fields collection
    /// to include the additional fields passed to the cTor
    /// </summary>
    /// <returns></returns>
    public override IEntityFields2 CreateFields()
    {
        IEntityFields2 toReturn = base.CreateFields();

        if (m_AdditionalFields != null)
        {
            foreach (IEntityField2 field in m_AdditionalFields)
            {
                toReturn.Expand(1);
                toReturn.DefineField(field, toReturn.Count - 1);
            }
        }

        return toReturn;
    }
}

I also had some fun with the definition of the related entity sort fields so here's a snippet from the method that manages these:


/// <summary>
/// Return a sort field object for related entity fields
/// to be used in a QualifiedSortExpression in order to facilitate any 
/// Case expressions required for those fields derived from the fields 
/// of multiple related entities, such as Activity.RegardingName which can be 
/// derived from Account.Name, Contact.Name, Prospect.Name or can be blank.
/// Used in conjunction with SortRelations method.
/// </summary>
/// <param name="entityType"></param>
/// <param name="fieldName"></param>
/// <returns></returns>
public static EntityField2 SortField(EntityType entityType, string fieldName)
{
    // When data paging, LLBL Gen may insist upon using a Distinct statement
    // in which case the sort field must appear in the select list.
    // We can include such additional related entity fields in the select list
    // using the factory extended classes (see SetEntityFactoryToUse),
    // however, including a related entity field (eg. Name) that has the same name as 
    // one of the entity's fields (even with an alias assigned) results in the return
    // of a collection of objects with the field values set to the related entity field value!
    // eg. account "bob" with primary contact "sid" is returned with an account name of "sid"
    // To work around this issue this method uses DbFunctionCall CASE expressions
    // (as pioneered for the Activity's RegardingName sort) so that the same field object
    // can be used ion the sort expression and added to the select list without causing anu issues.
    // Having compared the actual execution plans for both the standard sort and case based sort
    // there appears to be no adverse affect on performance.
    //
    // It is also possible for the FetchCollection's sort expression to incorporate two 
    // related entity SortOrder fields so its necessary to set an Alias for these sort fields
    // to avoid "an item with the same key has already been added" exception in the
    // factory extended classes when the fields are added to the select list 
    // eg. for an Activity its possible to group on Status SortOrder and sort on Priority SortOrder

    EntityField2 sortField = null;

    switch (entityType)
    {
        #region Account
        case EntityType.AccountEntity:

            switch (fieldName)
            {
                case "CountryName":
                    sortField = CountryFields.SortOrder;
                    sortField.Alias = fieldName;
                    break;

                case "IndustryName":
                    sortField = AccountIndustryFields.SortOrder;
                    sortField.Alias = fieldName;
                    break;

                case "ParentAccountName":
                    // see comments above (previously using sortField = AccountFields.Name)
                    sortField = new EntityField2(fieldName,
                        new DbFunctionCall("CASE WHEN {0} IS NOT NULL THEN {1} ELSE '' END",
                        new object[] {
                            AccountFields.ParentAccountId,
                            AccountFields.Name }));
                    break;

                case "PrimaryContactName":
                    // see comments above (previously using sortField = ContactFields.Name)
                    sortField = new EntityField2(fieldName,
                        new DbFunctionCall("CASE WHEN {0} IS NOT NULL THEN {1} ELSE '' END",
                        new object[] {
                            AccountFields.PrimaryContactId,
                            ContactFields.Name }));
                    break;

                case "StatusName":
                    sortField = AccountStatusFields.SortOrder;
                    sortField.Alias = fieldName;
                    break;

                case "TypeName":
                    sortField = AccountTypeFields.SortOrder;
                    sortField.Alias = fieldName;
                    break;

            }
            break;

        #endregion

        #region Activity
        case EntityType.ActivityEntity:

            switch (fieldName)
            {
                case "DirectionName":
                    sortField = ActivityDirectionFields.SortOrder;
                    sortField.Alias = fieldName;
                    break;

                case "PriorityName":
                    sortField = ActivityPriorityFields.SortOrder;
                    sortField.Alias = fieldName;
                    break;

                case "RegardingName":

                    // set object alias to match instance added by views (ViewEntryManager.FilterBucket)
                    string strAccountAlias = ViewEntryManager.AliasName("Account", "Regarding");
                    string strContactAlias = ViewEntryManager.AliasName("Contact", "Regarding");
                    string strProspectAlias = ViewEntryManager.AliasName("Prospect", "Regarding");

                    sortField = new EntityField2(fieldName,
                        new DbFunctionCall("CASE WHEN {0} IS NOT NULL THEN {1} WHEN {2} IS NOT NULL THEN {3} WHEN {4} IS NOT NULL THEN {5} ELSE '' END",
                        new object[] {
                            ActivityFields.RegardingAccountId,
                            AccountFields.Name.SetObjectAlias(strAccountAlias),
                            ActivityFields.RegardingContactId,
                            ContactFields.Name.SetObjectAlias(strContactAlias),
                            ActivityFields.RegardingProspectId,
                            ProspectFields.Name.SetObjectAlias(strProspectAlias) }));

                    break;

                case "StateName":
                    sortField = ActivityStateFields.SortOrder;
                    sortField.Alias = fieldName;
                    break;

                case "StatusName":
                    sortField = ActivityStatusFields.SortOrder;
                    sortField.Alias = fieldName;
                    break;

                case "TypeName":
                    sortField = ActivityTypeFields.SortOrder;
                    sortField.Alias = fieldName;
                    break;
            }
            break;

        #endregion
    }

    return sortField;
}

And the derived sort class that I'm using so that I can create sort expressions using the Case statement:


/// <summary>
/// Produces a fully qualified sort expression without using aliases
/// </summary>
[Serializable]
public class QualifiedSortExpression : SortExpression
{
    public QualifiedSortExpression()
    {
    }

    public QualifiedSortExpression(ISortClause sortClauseToAdd) : base(sortClauseToAdd)
    {
    }

    public override string ToQueryText(ref int uniqueMarker, bool aliasesForExpressionsAggregates)
    {
        return base.ToQueryText(ref uniqueMarker, false);
    }
}