my environment: llblgen 2.5 adapter asp.net 2 w/ c# Telerik RadGrid

I have an entity collection with prefetched entities that I am binding to a grid. I am trying to understand how best to sort by one of the fields in the prefetched entities, especially when that field name is the same as other fields on other entities.

For example, I have an order entity that prefetches two different PersonEntities, one is the person who made the order, the other is the person who last modified the order.

OrderEntity + PersonEntity (customer) + PersonEntity (last modified the order)

I want to allow dynamic server side sorting based on the last name of either the person who made the order, or the person who last modified the order. However, the field for both is "lastname" and llblgen returns and error that the field name is ambiguous.

Is there a way to create an alias for the field so that the llblgen datasource controls can pass the correct sorting field?

P.S. I read elsewhere that the solution is client-side sorting, but with the large number of orderEntities, server-side filtering is most convenient.

Thanks!

I think the following thread discussed this issue in details: http://www.llblgen.com/TinyForum/Messages.aspx?ThreadID=9288

Thank you for the prompt reply! The referenced link discusses searching against a field on a related (prefetched) entity. I am able to accomplish this using the IRelationPredicateBucket and define the relationship. The error occurs when two related entities have the same fieldname and I want to sort using the fieldname of one related entity instead of another.

For example, if I have an orderEntity collection with 1 personEntity related (person who made the order) and set the relation, then I can sort using the "lastname" field since it is only found once (in the PersonEntity) Yet, once I set a relation to another PersonEntity (person who modified the order) then the "lastname" field sort no longer works, returning the "ambigious" error.

Is there a way to provide a hint, like field name alias, to ensure sorts against the correct related entity? Like the OP in the referenced thread, I do not want to have to convert my whole entity collection into a typedList.

Thanks!

Is there a way to provide a hint, like field name alias, to ensure sorts against the correct related entity?

Sure, when you sort or filter on server-side using a related entity's field, you have to supply the relation to this related entity (JOIN), there you can define an alias for the joined entity.

RelationCollection relations = new RelationCollection();
            relations.Add(OrderEntity.Relations.PersonEntityUsingCreatedBy, "Owner");

Then you should use that same alias when supplying the sortExpression.

SortExpression sorter = new SortExpression(PersonFields.LastName.SetObjectAlias("Owner") | SortOperator.Descending);

Thank you again Walaa for the reply and the very useful information. One last question, is there a way I can use the alias to create a unique column name for a grid?

My preference would be that I could create the field alias and put that into the column header so that llblgen datasource can automatically build the ISortExpression in the Sorter collection w/ appropriate alias.

for example:

<asp:BoundField HeaderText="Person Who Made Order" SortExpression="ObjectAlias.Fieldname" />

Thanks!

You can add a "Field mapped on related field" to your entity in the designer. Then if you fetch your entites pre-fetched with the related entites this field will contain the value from the related entity. This can then be used in grids like any other field.

Matt

Thanks for the reply Matt. I did try the "Field mapped on related field" which allows for the column to have a unique name based on the alias defined in the llblgen designer, but the sort will still fail. Looking at the sql via profiler, I see that the initial entity is fetched (OrderEntity) and then on a separate call, the PersonEntity who last modified the order is called. However, when doing the sort (i.e., using field alias, "PersonWhoLastModifiedLastName") the sql sorts by this mapped field alias but does not use the same alias anywhere. (instead, it still references "lastname" in the PersonEntity sql)

In the end, the sort returns the error: "Invalid column name: SD.LLBLGen.Pro.ORMSupportClasses.ORMQueryExecutionException". Is there something else that I'm missing that allowed this to work for you?

Thanks again, and sorry for all the questions

luciusism wrote:

Looking at the sql via profiler, I see that the initial entity is fetched (OrderEntity) and then on a separate call, the PersonEntity who last modified the order is called. However, when doing the sort (i.e., using field alias, "PersonWhoLastModifiedLastName") the sql sorts by this mapped field alias but does not use the same alias anywhere. (instead, it still references "lastname" in the PersonEntity sql)

In the end, the sort returns the error: "Invalid column name: SD.LLBLGen.Pro.ORMSupportClasses.ORMQueryExecutionException". Is there something else that I'm missing that allowed this to work for you?

Prefetching and aliasing/sorting are two different things. If you prefetch (fetch related objects and plug them to the main object) and additional you want to sort on a related field, you have to specify the appropriate relations.

In other words,adding a "field mapped on related fields" (at Designer) doesn't mean that you can sort on that related field without specify the corresponding relation.

If you need further help, please post the code snippet you have so far.

Thank you daelmo for your response. Looks like I'm getting help from the whole support team

Below is the relevant code:

ASPX Code: (abbreviated)

<llblgenpro:LLBLGenProDataSource2 
    ID="LLBLGenProDataSource2_1" 
    runat="server" 
    AdapterTypeName="XXXX.DAL.DatabaseSpecific.DataAccessAdapter, XXXX.CRM.DALDBSpecific"  
    EnableViewState="false" 
    EnablePaging=true 
    LivePersistence=false 
    SortingMode=ServerSide
    OnPerformGetDbCount="Llblgen_PerfomGetDbCount" 
    OnPerformSelect="Llblgen_PerformSelect"
/>

asp:GridView runat="server" ID="Grid1">
        <Columns>
            <asp:BoundField HeaderText="Order Id" SortExpression="id" />
            <asp:BoundField HeaderText="Order By Person" DataField="orderedByLastname" SortExpression="orderedByLastname" />
            <asp:BoundField HeaderText="Last Modified By Person" DataField="lastModifiedByLastname" SortExpression="lastModifiedByLastname" />
        </Columns>
    </asp:GridView>

C#

public EntityCollection<OrderEntity> GetMany(int pageNumber, int pageSize, ISortExpression sort)
{
    EntityCollection<OrderEntity> orders = new EntityCollection<OrderEntity>(new OrderEntityFactory());

    // relations
    IRelationPredicateBucket b = new RelationPredicateBucket();
    b.Relations.Add(OrderEntity.Relations.PersonEntityUsingOrderedBy, "orderedBy");
    b.Relations.Add(OrderEntity.Relations.PersonEntityUsingLastModifiedBy, "lastModifiedBy");

    // prefetch
    IPrefetchPath2 pp = new PrefetchPath2((int)DAL.EntityType.OrderEntity);
    pp.Add(OrderEntity.PrefetchPathOrderdBy);
    pp.Add(OrderEntity.PrefetchPathLastModifiedBy);

    // fetch
    using (DataAccessAdapter a = new DataAccessAdapter())
    {
        a.FetchEntityCollection(orders, b, 0, sort, pp, pageNumber, pageSize);
    }
    return orders;
}


protected void Llblgen_PerformSelect(object sender, PerformSelectEventArgs2 e)
{
    LLBLGenProDataSource2_1.EntityCollection = GetMany(e.PageNumber, 100, e.Sorter);
}

Based on the psudo code above, I'm trying to figure out how to sort based on the "lastname" field yet distinguish between the lastname of a person who made an order (prefetched/related PersonEntity), and a person who last modified an order (another prefetched/related PersonEntity). Using the llblgen designer to create a "field mapped on related field" helps in setting a unique dataField name for the column, but when the PerformSelectEventArgs2.Sorter uses the same "field mapped on related field" that the dataField uses, the resulting sql is invalid.

Thanks again,

As I said before:

Walaa wrote:

When you sort or filter on server-side using a related entity's field, you have to supply the relation to this related entity (JOIN), there you can define an alias for the joined entity.

Then you should use that same alias when supplying the sortExpression

Having the alias in the column header to be used in the sort expression won't let avoid doing the manual work, as you'll have to supply the relation when sorting on this field, so at the same time you should supply the propper aliasing as well.

What you can do is to handle the PerformSelect event of the LLBLGenProDataSource and then supply the correct sortExpression and relation when sorting on a related field.

Having the alias in the column header to be used in the sort expression won't let avoid doing the manual work

Thanks Walaa for the reply. I guess, ultimately that's what I want to do, avoid the manual work and have my unique column headers understood by the PerformSelect just as they are understood on databinding. At this point, I'd like to turn this into a feature request. (Along with llblgen dynamically generated enum of custom fields created in the fields on related entities w/in llblgen designer. This way a VS design time error is thrown if I change a field name in the llblgen designer)

Thanks again to everyone who contributed!