Hi,

I'm using 2.6, self-service.

Binding to Telerik grid with LLBLGenProDataSource.

Paging and sorting is enabled on the grid.

On the LLBLGenProDataSource: EnablePaging="true" SortingMode="ServerSide"

I have two tables. The entity table and the related table.

In this case my entity tables is named "Pages" and the related table is named "MasterPages".

Both the tables contain the column "Title".

In the entity designer, in Fields on Related Fields, I've mapped the Title column of the MasterPages table as "MasterPageTitle".

Both the entity fields and the related field appear in the grid when the page is initially loaded. but when I sort on the related field I get an error: "Invalid column name 'MasterPageTitle'"

I've added the required relation in Form_Load, and this results in the JOIN being added to the SQL produced, but the mapped column itself does not appear in the select list.

LLBLGenProDataSource1.RelationsToUse = new RelationCollection();
LLBLGenProDataSource1.RelationsToUse.Add(PagesEntity.Relations.MasterPagesEntityUsingMasterPageId);

I've even added a prefetch, which seems to have no effect whatsoever on the generated sql.

IPrefetchPath path = new PrefetchPath((int)qd.EntityType.PagesEntity);
path.Add(PagesEntity.PrefetchPathMasterPage);
LLBLGenProDataSource1.PrefetchPathToUse = path;

Any ideas what I'm doing wrong?

Thanks.

This was recently discussed here: http://www.llblgen.com/TinyForum/Messages.aspx?ThreadID=15837

Other relevant threads: http://www.llblgen.com/TinyForum/Messages.aspx?ThreadID=15148 http://www.llblgen.com/TinyForum/Messages.aspx?ThreadID=14843

Thanks for the prompt reply, but those don't really answer my question.

The first two (which I had previously read) are about Adapter, not Self-Service.

They are also about different problems. I'm not even getting far enough to be getting confict errors because the column never gets selected (plus, I had already renamed it in the designer as suggested in the first).

The last was actually a thread I posted a while ago on a similar topic. I am using the exact solution from that thread, but it is not working this time.

My problem is that the sql emitted is:

"ORDER BY MasterPageTitle" (as though it were present in the Pages table)

rather than

"ORDER BY MasterPages.Title".

BTW, client-side sorting is not an option. My data sets on this app can run into the tens of thousands of rows.

Are you sure you have the field mapped on related field? Indeed this should work nice.

What LLBLGen version and runtime library version are you using?

Hi Daelmo,

Yes, I'm quite certain I have a field mapped on related field.

As I mention at the top, 2.6 (final), self-servicing.

To be absolutely certain the issue was with LLBLGen and not Telerik, I put together the simplest possible project using Northwind and a GridView plus the LLBLGenProDataSource control.

I added two entites: Orders and Customers.

On the Orders entity, I created a field mapped on related field pointing to the "Customers.CompanyName" field. I renamed field to be "Company" to have the a simalar situation as my real project.

Generated the project.

Bound the LLBLGenProDataSource to the OrdersCollection. Bound the GridView to the LLBLGenProDataSource.

Enabled paging and sorting on the GridView. Enabled paging and set SortingMode="ServerSide" on the LLBLGenProDataSource.

The ASPX code:

<asp:GridView ID="GridView1" runat="server" 
    DataSourceID="LLBLGenProDataSource1" 
    AllowPaging="True" AllowSorting="true"
    AutoGenerateColumns="False" 
    CellPadding="4" DataKeyNames="OrderId" 
    ForeColor="#333333" GridLines="None">
    <RowStyle BackColor="#F7F6F3" ForeColor="#333333" />
    <Columns>
        <asp:BoundField DataField="OrderId" HeaderText="OrderId" InsertVisible="False" ReadOnly="True" SortExpression="OrderId" />
        <asp:BoundField DataField="CustomerId" HeaderText="CustomerId" SortExpression="CustomerId" />
        <asp:BoundField DataField="OrderDate" HeaderText="OrderDate" SortExpression="OrderDate" />
        <asp:BoundField DataField="Company" HeaderText="Company" SortExpression="Company" />
    </Columns>
    <FooterStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" />
    <PagerStyle BackColor="#284775" ForeColor="White" HorizontalAlign="Center" />
    <SelectedRowStyle BackColor="#E2DED6" Font-Bold="True" ForeColor="#333333" />
    <HeaderStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" />
    <EditRowStyle BackColor="#999999" />
    <AlternatingRowStyle BackColor="White" ForeColor="#284775" />
</asp:GridView>
<llblgenpro:LLBLGenProDataSource ID="LLBLGenProDataSource1" runat="server" 
    DataContainerType="EntityCollection" 
    EntityCollectionTypeName="Northwind.Data.CollectionClasses.OrdersCollection, Northwind.Data"
    EnablePaging="true"
    SortingMode="ServerSide">
</llblgenpro:LLBLGenProDataSource>

Added the following code in code behind:

LLBLGenProDataSource1.RelationsToUse = new RelationCollection();
LLBLGenProDataSource1.RelationsToUse.Add(OrdersEntity.Relations.CustomersEntityUsingCustomerId);

IPrefetchPath path = new PrefetchPath((int)EntityType.OrdersEntity);
path.Add(OrdersEntity.PrefetchPathCustomers);
LLBLGenProDataSource1.PrefetchPathToUse = path;

Ran it. Same problem. This is the SQL emitted:

SELECT TOP 10 
[Northwind].[dbo].[Orders].[OrderID] AS [OrderId], 
[Northwind].[dbo].[Orders].[CustomerID] AS [CustomerId], 
[Northwind].[dbo].[Orders].[EmployeeID] AS [EmployeeId], 
[Northwind].[dbo].[Orders].[OrderDate], 
[Northwind].[dbo].[Orders].[RequiredDate], 
[Northwind].[dbo].[Orders].[ShippedDate], 
[Northwind].[dbo].[Orders].[ShipVia], 
[Northwind].[dbo].[Orders].[Freight], 
[Northwind].[dbo].[Orders].[ShipName], 
[Northwind].[dbo].[Orders].[ShipAddress],
[Northwind].[dbo].[Orders].[ShipCity], 
[Northwind].[dbo].[Orders].[ShipRegion], 
[Northwind].[dbo].[Orders].[ShipPostalCode], 
[Northwind].[dbo].[Orders].[ShipCountry] 
FROM ( 
[Northwind].[dbo].[Customers]  
INNER JOIN [Northwind].[dbo].[Orders]  ON  [Northwind].[dbo].[Customers].[CustomerID]=[Northwind].[dbo].[Orders].[CustomerID]
) 
ORDER BY Company ASC

SQL Server rightly complains that there is no column named "Company".

I found that I can get this to work by changing the SortExpression on the GridView to "CompanyName". Hacky, but ok in this instance.

I found that it does NOT work if I fully qualify it: "Customers.CompanyName". This would work fine in the sql itself, but doesn't work when fed in thru the grid and data control.

This means that it will not work for my real project since the column names on both the main and related tables are the same ("Title").

So I guess my question is:

How do I get sorting to work on SELF-SERVICE when the underlying column name on a field mapped on related field is the same as a column name in the base entity?

As I mention at the top, 2.6 (final), self-servicing.

We need the runtime library version/build number, please check the link posted by David (daelmo).

3.5 Assembly version: 2.6.0.0 3.5 File version: 2.6.8.1001

File version: 2.6.8.1001

That's somehow old, would you please try the latest build/version to see if it sorts things out.

I will try that, but I dont think it is causing my issue.

In your reply in http://www.llblgen.com/TinyForum/Messages.aspx?ThreadID=15837, you mention "SortExpression" objects:

========== Quote: 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.Code: RelationCollection relations = new RelationCollection(); relations.Add(OrderEntity.Relations.PersonEntityUsingCreatedBy, "Owner");

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

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

The problem is that I'm relatively new to LLBLGen and the post was related to Adapter, not self-service and I can't tell (1) if it is relevant to self-service or (2) how the SortExpression gets related to the Relation as the above seems to imply.

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.Code: RelationCollection relations = new RelationCollection(); relations.Add(OrderEntity.Relations.PersonEntityUsingCreatedBy, "Owner");

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

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

The problem is that I'm relatively new to LLBLGen and the post was related to Adapter, not self-service and I can't tell (1) if it is relevant to self-service or (2) how the SortExpression gets related to the Relation as the above seems to imply

1- Yes this solution is relevant to SelfServicing too.

2- it gets related through the alias ("Owner" in the above example). As then an alias for the joined table will be used in the generated sql query, and then that same alias will be used to qualify the field used in the Order By.

Ok, maybe I'm being dense here, but this isn't working.

Both the code above and the documentation refer to some <Whatever>Fields object in the SortExpression constructor.

From the documentation:

SortExpression sorter = new SortExpression(**CustomerFields.Country **| SortOperator.Ascending) & (CustomerFields.CompanyName | SortOperator.Descending);

My generated code seems to have no such construct.

Does the Intellisense show (ISortClause sortClauseToAdd) as one of the contsructor options...?

Matt

Yes. The SortExpression constructor has two overloads. One with no paramaeters and one with (ISortClause sortClauseToAdd).

Ok. I found the CustomersFields in the HelperClasses namespace.

Added the sort expression as indicated, and it did, in fact, apply an initial sort to the grid using the CustomerName field (which wasn't really what I wanted).

But as soon as I clicked the column in the grid, it crashed again, with its usual message about there invalid column "Customer".

So I'm back to square one:

How do I get LLBLGenProDataSource1 to properly qualify ORDER BY clauses on fields mapped on related fields???

Please attach your repro solution.

Here it is.

BTW, I was only able to attach the web site (after deleting the SD.LLBLGen.Pro.* xml files).

You really ought to increase the 512kb limit on attachments since your own dlls are larger than that...

BTW, I was only able to attach the web site (after deleting the SD.LLBLGen.Pro.* xml files).

You really ought to increase the 512kb limit on attachments since your own dlls are larger than that...

We don't want the LLBLGen stuff as we already have it.

Looking into it...

(EDIT)

First thing the following couple of lines looks wierd, and I see nothing useful behind using them:

        IEntityRelation r1 = OrdersEntity.Relations.CustomersEntityUsingCustomerId as IEntityRelation;
        IEntityRelation r2 = LLBLGenProDataSource1.RelationsToUse[0] as IEntityRelation;

Lets make it simple, why don't you re-name the field mapped on the related field to CompanyName, instead of Company. And then all you have to do is to use the following relation:

LLBLGenProDataSource1.RelationsToUse = new RelationCollection(OrderEntity.Relations.CustomerEntityUsingCustomerId);

I'll attach a sample code for you....

(EDIT) Code attached.

Walaa wrote:

First thing the fo;;owing couple of lines looks wierd, and I see nothing useful behind using them:

Sorry. Yes, you are correct -- those lines do nothing. I had just put them in there so that I could put them in the Watch window and see what (if anything) changes on a relation when it is added to a data control's collection. Should have deleted them before sending.

Walaa wrote:

Lets make it simple, why don't you re-name the field mapped on the related field to CompanyName, instead of Company.

If only life were simple...

I know this thead has been going on for a while, so you may have forgotton, but this little Northwind thing isn't my real project. I just created it to illustrate the problem in the simplest possible way.

As I mentioned way up above somewhere, my real project needs to be able to sort on mapped fields that have been renamed because all my real tables have consistant column names (like "Title").

I cannot believe that I am the first person who has ever needed to use an EntityCollection containing renamed mapped fields with a LLBLGenProDataSource control. What is the standard way of doing this?

And more to the point, WHY do I have to jump thru all these hoops to get something as basic as sorting an entity collection with an LLBLGenProDataSource to work? Did the folks who wrote this think nobody would ever need to sort a renamed mapped column with a LLBLGenProDataSource???

If I was using a generic ASP.Net ObjectDataSource control, I would expect stupid behavior. But I am not -- I am using YOUR data control with YOUR Entity layer. Maybe they should try talking to each other.

First, I should not have to specify relations explicitly when using a LLBLGenProDataSource. The Entity Collection should be able to tell it which fields are mapped and which are native. If it sees a mapped field being requested, it should just add the relation automatically because it knows (or should know) that, without the relation, sorting will crash.

Also, why would an LLBLGenProDataSource using an entity collection EVER emit unqualified ORDER BY's? When I write SQL by hand, I qualify EVERYTHING. ALWAYS. Clearly, the entity knows where the column comes from since the data shows up on the screen. Again, maybe the data control and entity layer should try talking to each other.

This is really frustrating. I bought LLBLGen because it promised to same me time. It doesn't do that. It gets me to about 80% of where I need to be very quickly and then begins wasting tremendous amounts of time.

At least for basic stuff like this, IT SHOULD JUST WORK!

I don't think tis would be a problem.

Using my code sample. Say if the CompanyName field was mapped as Cmpny in the Orders Entity.

All you have to modify is the SortExpression used in the databinding to use the original column name which should work provided the relatrion is used (as already shown in the code sample).

All you'll have to change is the following:

<asp:BoundField DataField="Cmpny" HeaderText="Company" SortExpression="CompanyName" />

I know that will work for this SAMPLE PROJECT -- I said so about ten posts ago.

But it won't work for my REAL PROJECT. As I said in the last post:

all my real tables have consistant column names (like "Title").

This will cause SQL to complain about ambiguous columns because the related table's underlying column name is "Title" and the entity's base table already has a column names "Title" -- which is why I had to rename it in the first place.

Could you please stay calm and work towards a solution with us? We have thousands and thousands of users and our work is used in many thousands of projects, sofar we've not seen this before so it might be you want to do something that's not fitting how the framework is designed to be used.

I'll look into this thread and read up on what you wrote so far. I understand that if you think something should do ABC and it does DEF, it can be frustrating to find something to make it do ABC, however if (hypothetically) ABC was never meant to be done, it will not be easy to do so. If you want to rant about our work, fine, but please stay reasonable, OK?

Ok, I'll try to explain what happens. I won't go into your other posts here about our framework and the quality of it. If you want to debate with me about what our work, please formulate this constructively in feature / change requests in the feature request forum. We understand we're not perfect and our work isn't perfect either and sometimes things might not work as intended or not be as easy to use as it should be. That's natural, we're only human . If you run into these things, please formulate a change request to get something added, changed etc. so we can work from there separately from an issue at hand.

Ok! back to the problem on the table. I'd like to point out the documentation about SortingMode. I quote:

Server-side sorting only uses EntityField objects, so if the entity has a field which isn't a field mapped onto a table/view field, it's ignored in the server-side sorting actions because it's not part of the query send to the database. This is also true for fields mapped onto related fields. In these situations, use client-side sorting.

I.o.w.: you can't use serverside sorting when sorting on a field mapped onto a related field. The problem is that this 'field mapped onto a related field' ('Forf' as we call it) isn't part of the entity shown in the grid but part of an entity object in the object graph. So it's not going to be part of the query nor is its source, the related entity. In these cases, you should use client-side sorting (so the data in the grid is sorted, without re-executing the query). This is done this way as it's not possible to determine what to add to the query, as the Forf is simply a property in a class which refers to a property in another class. So it's not something that's known at the query level. (you can also not filter on a forf for example). It's in short, not a meta-data element known at runtime, the forf is used to generate a property in the code which references another property. It's equal to you writing manually a simple property which refers to a property in a referenced object in the graph. That property too doesn't have meta-data but it might refer to a property of an entity class which is a regular entity field (however that's not determinable at runtime).

How do I get sorting to work on SELF-SERVICE when the underlying column name on a field mapped on related field is the same as a column name in the base entity?

You can't do it this way. You can write a workaround, which isn't pretty but it could work: use LivePersistence = false, and in the code behind implement the Perform* methods. For the fetch, use the standard code to fetch the data (see manual about ASP.NET, same page as linked above), however before you fetch the data, you verify if the SortExpression is not null and if not, if it indeed contains a SortClause expression with an entity field which isn't part of Order. You can detect this by checking the type of the field object in the SortClause: normally it's EntityField, but in this case its EntityFieldProperty. If that's the case, you know you're dealing with a sort on a property. In that case, replace the SortClause with a new one on the proper field. Not ideal, and definitely not 'out of the box easy', but it's how the framework is intended to work: what you want isn't supported as a framework feature.

Hope this gets you on track.

Otis, thanks for pointers on how to get started on this. I've going in circles for about a week trying to get someone to understand the problem.

I'll probably have more questions, but at least I can now see how this compares to other methods of getting data into a grid.