This question has been discussed before, however I couldn't quite pin down my exact issue through my searches:

I have "Clients" and "CountryCodes" tables (in page load):

PrefetchPath2 pp = new PrefetchPath2((int)EntityType.ClientsEntity);
pp.Add(ClientsEntity.PrefetchPathCountryCodes);

RelationPredicateBucket bucket = new RelationPredicateBucket();
bucket.Relations.Add(ClientsEntity.Relations.CountryCodesEntityUsingCountryId);
bucket.PredicateExpression.Add(new FieldLikePredicate(CountryCodesFields.CountryName, null, "")); // I know this is silly, but testing LLBLGEN concepts at the moment

LLBLGenProDataSource2_1.FilterToUse = bucket;
LLBLGenProDataSource2_1.PrefetchPathToUse = pp;

Grid HTML:

<Columns>

<asp:BoundField DataField="ClientId" HeaderText="ClientId" ReadOnly="True" SortExpression="ClientId" />

<asp:BoundField DataField="CountryId" HeaderText="CountryId" SortExpression="CountryId" />

<asp:TemplateField HeaderText="Country Name" SortExpression="CountryName">
<ItemTemplate><%# ((CountryCodesEntity)Eval("CountryCodes")).CountryName %></ItemTemplate>
</asp:TemplateField>

</Columns>

• ClientId and CountryId will sort. CountryName will not sort.

• I don't want to use the "field mapped feature" in the editor or typed lists (would prefer not to manage extra relationships / configs outside of the app at this time)

• How do I make SortExpression work for the CountryName field.

• Please provide an Eval snippet showing how I would reference a related entity that is 2+ relations deep. for example:

Clients -> Groups -> Roles (Roles.Name, how is this accessed in Grid HTML with Eval ?)

Early testing looking good. It appears you have done excellent work.

Thank you.

Do you want to sort ClientSide or ServerSide?

Did you read these threads? http://www.llblgen.com/tinyforum/Messages.aspx?ThreadID=8513 http://www.llblgen.com/tinyforum/Messages.aspx?ThreadID=8363 http://www.llblgen.com/tinyforum/Messages.aspx?ThreadID=8219

Yes, I have read all 3 of those threads and then some

I am trying to do serverside sorting at the moment.

• I would like to not use "fields mapped" feature
• I would like to not use "typed list" feature
• How can we sort on a related table with the above requirements (code snippets provided previously above)
• How can we reference field realtions that are 2 or more related tables away from the root? This is a simple syntax issue that I am not clear about "Clients -> Groups -> Roles (Roles.Name, how is Roles.Name accessed in the <columns> when the Clients data is being displayed? I have already done prefetch paths in the page load as explained previously).

Applying a Server-Side Sort, you can do the following:

PrefetchPath2 pp = new PrefetchPath2((int)EntityType.ClientsEntity);
pp.Add(ClientsEntity.PrefetchPathCountryCodes);

RelationPredicateBucket bucket = new RelationPredicateBucket();
bucket.Relations.Add(ClientsEntity.Relations.CountryCodesEntityUsingCountryId);
bucket.PredicateExpression.Add(new FieldLikePredicate(CountryCodesFields.CountryName, null, "")); // I know this is silly, but testing LLBLGEN concepts at the moment

LLBLGenProDataSource2_1.FilterToUse = bucket;
LLBLGenProDataSource2_1.PrefetchPathToUse = pp;
LLBLGenProDataSource2_1.SorterToUse = new SortExpression(CountryCodesFields.CountryName | SortOperator.Ascending);

You can sort on a field in a related entity, as long as the relation to that entitiy is added. Which is already done in the above example "bucket.Relations.Add(ClientsEntity.Relations.CountryCodesEntityUsingCountryId);"

To reference a field on a related entity, try the following:

<%# ((MyMainEntity)Eval("MyRelatedEntity")).MyFieldName%>

Thank you for the reply!

3 questions:

1.

Same problem:

The code you provided is identical to what we were running. If I set:

LLBLGenProDataSource2_1.SorterToUse = new SortExpression(CountryCodesFields.CountryName | SortOperator.Ascending);

Then the grid will initially load with CountryName sorted in ascending manner, however will revert to descending on any subsequent page load. I apologize if we are missing something obvious outside the scope of LLBLGEN, however we don't think so, so far.

I am assuming we do not have to handle the "ascending" "descending" toggle, and that the LLBLGenProDataSource2 class will manage this.

Perhaps this is related to our main problem, you wrote this:

<%# ((MyMainEntity)Eval("MyRelatedEntity")).MyFieldName%>

This would imply by your naming:

<%# ((ClientsEntity)Eval("CountryCodesEntity")).CountryName%>

However this will give compile time error obviously because I am saying "ClientsEntity".CountryName (ClientsEntity has the CountryId and CountryCodes collection properties)

This is what we use:

<%# ((CountryCodesEntity)Eval("CountryCodes")).CountryName %>

Perhaps this is what you were saying and I mis-read it. I just wanted to double check. If this is ok, then we are using the very same code you provided. Back to question #1 then.

3.

Say I wanted to reference another related table from CountryCodes, "CountryContinents" - field: "ContinentName". How would I reference this in the GridView that is displaying "Clients"? (assume I have done prefetch paths). Is this correct:

<%#  (CountryContinentsEntity((CountryCodesEntity)Eval("CountryCodes")).CountryContinents).ContinentName %>

Is that right? Is this how we reference related tables n levels deep?

One more thing, in the GridView HTML I have this:

<asp:TemplateField HeaderText="Country Name" SortExpression="CountryName" ><ItemTemplate>
<%# ((CountryCodesEntity)Eval("CountryCodes")).CountryName %>
</ItemTemplate></asp:TemplateField>

Is SortExpression correct? I have also tried "CountryCodes.CountryName" and "CountryCodesEntity.CountryName"

The heirachy is:

ClientsEntity -> CountryCodes (collection) -> CountryName (EntityField)

Can I get little more clarification, the last post wasn't what I was expecting.

What fields are you showing on the grid? Not all necessarily, just enough to get an idea how the entities and their related entities are being combined and displayed.

So you are binding a collection of clients. Then each client has a collection of countries. So a client could be listed multiple times, once for each country?

Normally the situation that someone would have when sorting on related tables would be a join to a primary key. So it could be Client(N) -> Country(1) -> State(1) -> City(1). If any of these are collections I believe this complicates sorting.

The last line in the last reply was a typo, it is not a collection.

The relation is as you thought: Client (N) -> Country (1)

ClientsEntity has a property "CountryCodes", which is a reference to the CountryCodes entity.

Clients - ClientName - CountryId

CountryCodes - CountryId - CountryName

GridView code:

<asp:TemplateField HeaderText="Country Name" SortExpression="CountryCodesEntity.CountryName" ><ItemTemplate>
<%#  ((CountryCodesEntity)Eval("CountryCodes")).CountryName %>
</ItemTemplate></asp:TemplateField>

I have also tried SortExpression = "CountryName"

First of all, the best practice for binding data from different entities is to use a TypedList or a Dynamiclist. Our second recommendation is to use "fields mapped on related fields".

If you don't want to use any of the above, we will try to help you.

1.

Same problem: The code you provided is identical to what we were running. If I set: Code: LLBLGenProDataSource2_1.SorterToUse = new SortExpression(CountryCodesFields.CountryName | SortOperator.Ascending);

Then the grid will initially load with CountryName sorted in ascending manner, however will revert to descending on any subsequent page load. I apologize if we are missing something obvious outside the scope of LLBLGEN, however we don't think so, so far.

I am assuming we do not have to handle the "ascending" "descending" toggle, and that the LLBLGenProDataSource2 class will manage this.

That's server side sorting (upon fetching from the database). Have you set the SortingMode="ServerSide" for the LLBLGenProDataSource? Have you used ViewState for the LLBLGenProDataSource and/or the GridView?

1. Perhaps this is related to our main problem, you wrote this: Code:

<%# ((MyMainEntity)Eval("MyRelatedEntity")).MyFieldName%>

This would imply by your naming: Code:

<%# ((ClientsEntity)Eval("CountryCodesEntity")).CountryName%>

However this will give compile time error obviously because I am saying "ClientsEntity".CountryName (ClientsEntity has the CountryId and CountryCodes collection properties)

This is what we use: Code:

<%# ((CountryCodesEntity)Eval("CountryCodes")).CountryName %>

Perhaps this is what you were saying and I mis-read it. I just wanted to double check. If this is ok, then we are using the very same code you provided. Back to question #1 then.

I had it wrong, and yours is the correct form.

3. Say I wanted to reference another related table from CountryCodes, "CountryContinents" - field: "ContinentName". How would I reference this in the GridView that is displaying "Clients"? (assume I have done prefetch paths). Is this correct: Code:

<%# (CountryContinentsEntity((CountryCodesEntity)Eval("CountryCodes")).CountryContinents).ContinentName %>

Is that right? Is this how we reference related tables n levels deep?

Yes, I think so.

One more thing, in the GridView HTML I have this: Code: <asp:TemplateField HeaderText="Country Name" SortExpression="CountryName" ><ItemTemplate> <%# ((CountryCodesEntity)Eval("CountryCodes")).CountryName %> </ItemTemplate></asp:TemplateField>

Is SortExpression correct? I have also tried "CountryCodes.CountryName" and "CountryCodesEntity.CountryName"

The heirachy is:

ClientsEntity -> CountryCodes (collection) -> CountryName (EntityField)

That's client-side sorting and I don't think it's gonna work with fields defined in related entities. (That's where a TypedList/DynamicList would have been of much use)

Walaa wrote:

First of all, the best practice for binding data from different entities is to use a TypedList or a Dynamiclist. Our second recommendation is to use "fields mapped on related fields".

If you don't want to use any of the above, we will try to help you.

The reason I am trying to avoid this, is to have one less aspect of the project to manually maintain if there are schema changes later. Running LLBLGen to generate new schema only is nice and simple for maintenance.

In regards to Dynamic Lists, again I would prefer not to add extra layers if possible. I don't mind some of the trickier referencing I have been trying if it can be done. It uses the basic entity layouts which are just want I want.

That's server side sorting (upon fetching from the database). Have you set the SortingMode="ServerSide" for the LLBLGenProDataSource? Have you used ViewState for the LLBLGenProDataSource and/or the GridView?

Yes, server side sorting is set.

I have tried viewstate = enabled for both (and both disabled as well).

One more thing, in the GridView HTML I have this: Code: <asp:TemplateField HeaderText="Country Name" SortExpression="CountryName" ><ItemTemplate> <%# ((CountryCodesEntity)Eval("CountryCodes")).CountryName %> </ItemTemplate></asp:TemplateField>

Is SortExpression correct? I have also tried "CountryCodes.CountryName" and "CountryCodesEntity.CountryName"

The heirachy is:

ClientsEntity -> CountryCodes (collection) -> CountryName (EntityField)

That's client-side sorting and I don't think it's gonna work with fields defined in related entities. (That's where a TypedList/DynamicList would have been of much use)

Then what would you suggest here? Is there a different way I should be referencing that I have not tried?

If it is impossible to sort a related table column without using typed/dynamic/fields-mapped-on , let me know. I just can't believe this is the case.

Then what would you suggest here? Is there a different way I should be referencing that I have not tried?

If it is impossible to sort a related table column without using typed/dynamic/fields-mapped-on , let me know. I just can't believe this is the case

I don't know if it's impossible or not, but I can't find a way to it.

Reading the threads mentioned earlier by David, I found the following reply from Frans:

To sort on the client side, you can use a SortClause, and use new EntityProperty("FieldName") instead of EntityNameFields.FieldName This only works with sorting on client side so the sorter is executed on the view.

posted here: http://www.llblgen.com/TinyForum/Messages.aspx?ThreadID=8219

Maybe you want to try it out. This is used for Entity properties which are not EntityFields, so maybe you can the field from the related entity, using this way.

Although again, I'd suggest you use a field mapped on a related field or a TypedList

I am using the client side sorting that Otis described and same problem. When clicking on the header text, the related column will not toggle sorting between ascending / descending. The main entity columns sort perfectly. (NOTE: This does work if I use a "fields mapped on" approach from the llblgen designer, but this is something i am hoping is not necessary).

When sorting related columns, must we handle this in an event and toggle the sort manually? I thought this would be handled by the llblgenprodatasource control.

Can you run a brief test and try displaying 1 column for an entity and 1 column from a related entity column, and make it sort? If it works, please paste the gridview HTML + the c# and any relevant properties. I think we have toggled every option and come at this a dozen ways at this point. It is a simple issue, but it is a big problem as this is a fundemental building block for all the data binding we are going to do using the llblgenprodatasource control.

The idea of configuring "fields mapped on related..." and "Typed lists" for each view that the application will publish is not what we are hoping for. By finding a solution without using these features, we can have (as I understand) ability to update directly on the entities through the grid, and not have to maintain another dimension of configuration as the app needs to be maintained on-going.

Many thanks for hanging in there with us! BTW - Perhaps Mr. LLBLGENPRO (Otis) can comment?

.

Well this works, but as you see you have to jump through some hoops for the related entities.

Codebehind

namespace Website
{
    using System;
    using System.Data;
    using System.Configuration;
    using System.Collections;
    using System.Web;
    using System.Web.Security;
    using System.Web.UI;
    using System.Web.UI.WebControls;
    using System.Web.UI.WebControls.WebParts;
    using System.Web.UI.HtmlControls;
    using Sandbox.DALAdapter.DatabaseSpecific;
    using Sandbox.DALAdapter.FactoryClasses;
    using Sandbox.DALAdapter.HelperClasses;
    using Sandbox.DALAdapter.EntityClasses;
    using SD.LLBLGen.Pro.ORMSupportClasses;
    using Sandbox.DALAdapter;

    public partial class _Default : System.Web.UI.Page
    {
        private bool Ascending
        {
            get
            {
                bool returnValue = true;
                if (ViewState["ascending"] != null)
                {
                    bool.TryParse(ViewState["ascending"].ToString(), out returnValue);
                }
                return returnValue;
            }
            set { ViewState["ascending"] = value; }
        }

        private ISortExpression Sorter
        {
            get { return (ISortExpression)ViewState["sorter"];}
            set { ViewState["sorter"] = value; }
        }

        private string SortExpression
        {
            // casting to string is used instead of ToString because returning a null string is valid and ToString will throw an exception if the value is null
            get { return (string)ViewState["sortexpression"]; }
            set { ViewState["sortexpression"] = value; }
        }
        protected void Page_Load(object sender, EventArgs e)
        {
            if (!IsPostBack)
            {
                IPrefetchPath2 path = new PrefetchPath2((int)EntityType.EmployeeEntity);
                path.Add(EmployeeEntity.PrefetchPathPosition);
                llblGenDataSource.PrefetchPathToUse = path;
                

                IRelationPredicateBucket filter = new RelationPredicateBucket();
                filter.Relations.Add(EmployeeEntity.Relations.PositionEntityUsingPositionId);
                llblGenDataSource.FilterToUse = filter;
            }
        }

        protected void grdData_Sorting(object sender, GridViewSortEventArgs e)
        {
            if (!string.IsNullOrEmpty(SortExpression) && e.SortExpression == SortExpression)
                Ascending = !Ascending;
            else
                Ascending = true;

            SortExpression = e.SortExpression;

            SortOperator sortOperator;
            if (Ascending)
                sortOperator = SortOperator.Ascending;
            else
                sortOperator = SortOperator.Descending;

            ISortExpression sorter = new SortExpression();
            switch(e.SortExpression)
            {
                case "Position":
                    sorter.Add(PositionFields.Name | sortOperator);
                    break;
                case "FirstName":
                    sorter.Add(EmployeeFields.FirstName | sortOperator);
                    break;
                case "LastName":
                    sorter.Add(EmployeeFields.LastName | sortOperator);
                    break;
                default:
                    sorter.Add(EmployeeFields.FirstName | sortOperator);
                    break;
            }
            Sorter = sorter;
        }

        protected void llblGenDataSource_PerformGetDbCount(object sender, PerformGetDbCountEventArgs2 e)
        {
            using (DataAccessAdapter adapter = new DataAccessAdapter())
            {
                e.DbCount = adapter.GetDbCount(e.ContainedCollection, e.Filter);
            }
        }

        protected void llblGenDataSource_PerformSelect(object sender, PerformSelectEventArgs2 e)
        {
            using (DataAccessAdapter adapter = new DataAccessAdapter())
            {
                adapter.FetchEntityCollection(e.ContainedCollection, e.Filter, e.MaxNumberOfItemsToReturn, Sorter, e.PrefetchPath, e.PageNumber, e.PageSize);
            }
        }

        protected void llblGenDataSource_PerformWork(object sender, PerformWorkEventArgs2 e)
        {
            using (DataAccessAdapter adapter = new DataAccessAdapter())
            {
                e.Uow.Commit(adapter);
            }
        }
    }
}

HTML

<body>
    <form id="form1" runat="server">
    <div>
        <llblgenpro:LLBLGenProDataSource2 ID="llblGenDataSource" runat="server" AdapterTypeName="Sandbox.DALAdapter.DatabaseSpecific.DataAccessAdapter, Sandbox.DALAdapterDBSpecific"
            DataContainerType="EntityCollection" EntityFactoryTypeName="Sandbox.DALAdapter.FactoryClasses.EmployeeEntityFactory, Sandbox.DALAdapter"
            OnPerformGetDbCount="llblGenDataSource_PerformGetDbCount" OnPerformSelect="llblGenDataSource_PerformSelect"
            OnPerformWork="llblGenDataSource_PerformWork" CacheLocation="Session" EnablePaging="True" LivePersistence="False">
        </llblgenpro:LLBLGenProDataSource2>
    
    </div>
        <asp:GridView ID="grdData" runat="server" AllowPaging="True" AutoGenerateColumns="False"
            DataKeyNames="EmployeeId" DataSourceID="llblGenDataSource"
            OnSorting="grdData_Sorting" AllowSorting="True">
            <Columns>
                <asp:BoundField DataField="FirstName" HeaderText="FirstName" SortExpression="FirstName" />
                <asp:BoundField DataField="LastName" HeaderText="LastName" SortExpression="LastName" />
                <asp:TemplateField HeaderText="Position" SortExpression="Position">
                    <ItemTemplate>
                        <%# ((Sandbox.DALAdapter.EntityClasses.PositionEntity)(Eval("Position"))).Name %>
                    </ItemTemplate>
                </asp:TemplateField>
            </Columns>
        </asp:GridView>
    </form>
</body>