Hi

Using LLBLgen Pro 1.0.2005.1 final, released may 2nd, 2006

I have two tables TBLcommerce_item TBLcommerce_itemGroupItem

Both tables contains a column called SortOrder

Now I want to sort my collection on TBLcommerce_itemGroupItem.SortOrder.

So I make this code:

CommerceItemCollection itemCollection = new CommerceItemCollection();
IPredicateExpression filter = new PredicateExpression();
        filter.Add(CommerceItemFields.SiteGuid == 10217);
filter.AddWithAnd(CommerceItemGroupItemFields.ItemGroupGuid == 2704);

IRelationCollection relations = new RelationCollection();
relations.Add(CommerceItemEntity.Relations.CommerceItemGroupItemEntityUsingItemGuid);
itemCollection.GetMulti(filter, 0, sortExpression, relations);

This will give me an SQL query like this:

exec sp_executesql N'SELECT [dbo].[TBLcommerce_item].[guid] AS [Guid], [dbo].[TBLcommerce_item].[siteGuid] AS [SiteGuid], [dbo].[TBLcommerce_item].[itemNo] AS [ItemNo],  [dbo].[TBLcommerce_item].[sortOrder] AS [SortOrder]
FROM ( [dbo].[TBLcommerce_item]  INNER JOIN [dbo].[TBLcommerce_itemGroupItem] [LPA_I1]  ON  [dbo].[TBLcommerce_item].[guid]=[LPA_I1].[itemGuid]) 
WHERE ( ( [dbo].[TBLcommerce_item].[siteGuid] = @SiteGuid1  AND [LPA_I1].[itemGroupGuid] = @ItemGroupGuid1)) 
ORDER BY [LPA_I1].[sortOrder] ASC', N'@SiteGuid1 int,@ItemGroupGuid2 int', @SiteGuid1 = 10217, @ItemGroupGuid2 = 2704

SQL result like this:

42167            10217           Calvin Klein D1257E Bh      0
42200            10217           Calvin Klein D1264E String  0
42209            10217           Calvin Klein D1267E Pants   0

The correct result should have been:

42167            10217           Calvin Klein D1257E Bh      0
42200            10217           Calvin Klein D1264E String  0
42209            10217           Calvin Klein D1267E Pants   0

The sqlquery actually do the sorting on [dbo].[TBLcommerce_item].[sortOrder] instead of [LPA_I1].

If I modify the SQL it will work correctly when: 1. I Remove [dbo].[TBLcommerce_item].[sortOrder] AS [SortOrder] from select. 2. Change [dbo].[TBLcommerce_item].[sortOrder] AS [SortOrder] to [dbo].[TBLcommerce_item].[sortOrder] AS [SortOrderForSomethingElse] 3. Adds [LPA_I1].[sortOrder] to select.

So my question is: Is it possibly to do something so I can execute the LLBLgen code above without changing one of the fieldnames i LLBLgen Designer?

Hi,

SQL result like this: Code:

42167 10217 Calvin Klein D1257E Bh 0 42200 10217 Calvin Klein D1264E String 0 42209 10217 Calvin Klein D1267E Pants 0

The correct result should have been: Code:

42167 10217 Calvin Klein D1257E Bh 0 42200 10217 Calvin Klein D1264E String 0 42209 10217 Calvin Klein D1267E Pants 0

It's just the same

then ORDER BY [LPA_I1].[sortOrder] ASC' should mean it gets sorted by TBLcommerce_itemGroupItem.SortOrder as you wish. The column name should have no reason to interfer. we can't see on your pasted data, and we also don't have the source bit when you define the sortexpression, but your query looks correct so are you sure the order is not the one you wish?

Wrong copy /paste from me, sorry.

This is the right message:

SQL result like this:
Code:

42167 10217 Calvin Klein D1257E Bh 0
42200 10217 Calvin Klein D1264E String 0
42209 10217 Calvin Klein D1267E Pants 0


The correct result should have been:
Code:

42167 10217 Calvin Klein D1257E Bh 0
42209 10217 Calvin Klein D1267E Pants 0
42200 10217 Calvin Klein D1264E String 0

The SortExpression code:

// code taken from memory, as Im at home atm
SortExpression sortExpression = new SortExpression();
sortExpression.Add(CommerceItemGroupItemFields.SortOrder|SortOperator.Ascending);

Believe me this bugs the hell out of me.

That was one of the reasons I copied the query to SQL query analyzer and played around with it.

The sqlquery actually do the sorting on [dbo].[TBLcommerce_item].[sortOrder] instead of [LPA_I1].

I am sure the order is not the order I wish.

What version of sql server are you using? SP?

bclubb wrote:

What version of sql server are you using? SP?

Using SQL server 2000 with SP4 (8.00.2040).

I think my problem is related to what is discussed in this thread: googlegroups link

After reading the discussion on usenet (Google Groups) I tried another test:

Instead of using [dbo].[TBLcommerce_item].[sortOrder] AS [SortOrder] in the select i wrote [dbo].[TBLcommerce_item].sortOrder

This works correctly as expected and I get the ordering I want:

exec sp_executesql N'SELECT [dbo].[TBLcommerce_item].[guid] AS [Guid], [dbo].[TBLcommerce_item].[siteGuid] AS [SiteGuid], [dbo].[TBLcommerce_item].[itemNo] AS [ItemNo], [dbo].[TBLcommerce_item].sortOrder
FROM ( [dbo].[TBLcommerce_item] INNER JOIN [dbo].[TBLcommerce_itemGroupItem] [LPA_I1] ON [dbo].[TBLcommerce_item].[guid]=[LPA_I1].[itemGuid])
WHERE ( ( [dbo].[TBLcommerce_item].[siteGuid] = @SiteGuid1 AND [LPA_I1].[itemGroupGuid] = @ItemGroupGuid2) AND [dbo].[TBLcommerce_item].VariantofItemguid is null)
ORDER BY [LPA_I1].[sortOrder] ASC', N'@SiteGuid1 int,@ItemGroupGuid2 int', @SiteGuid1 = 10217, @ItemGroupGuid2 = 2704

jacob wrote:

Hi Using LLBLgen Pro 1.0.2005.1 final, released may 2nd, 2006 I have two tables TBLcommerce_item TBLcommerce_itemGroupItem

Both tables contains a column called SortOrder

Now I want to sort my collection on TBLcommerce_itemGroupItem.SortOrder.

So I make this code:

CommerceItemCollection itemCollection = new CommerceItemCollection();
IPredicateExpression filter = new PredicateExpression();
        filter.Add(CommerceItemFields.SiteGuid == 10217);
filter.AddWithAnd(CommerceItemGroupItemFields.ItemGroupGuid == 2704);

IRelationCollection relations = new RelationCollection();
relations.Add(CommerceItemEntity.Relations.CommerceItemGroupItemEntityUsingItemGuid);
itemCollection.GetMulti(filter, 0, sortExpression, relations);

This will give me an SQL query like this:

exec sp_executesql N'SELECT [dbo].[TBLcommerce_item].[guid] AS [Guid], [dbo].[TBLcommerce_item].[siteGuid] AS [SiteGuid], [dbo].[TBLcommerce_item].[itemNo] AS [ItemNo],  [dbo].[TBLcommerce_item].[sortOrder] AS [SortOrder]
FROM ( [dbo].[TBLcommerce_item]  INNER JOIN [dbo].[TBLcommerce_itemGroupItem] [LPA_I1]  ON  [dbo].[TBLcommerce_item].[guid]=[LPA_I1].[itemGuid]) 
WHERE ( ( [dbo].[TBLcommerce_item].[siteGuid] = @SiteGuid1  AND [LPA_I1].[itemGroupGuid] = @ItemGroupGuid1)) 
ORDER BY [LPA_I1].[sortOrder] ASC', N'@SiteGuid1 int,@ItemGroupGuid2 int', @SiteGuid1 = 10217, @ItemGroupGuid2 = 2704

I fail to see why [dbo].[TBLcommerce_itemGroupItem] is aliased in the resultquery. You don't specify an alias for the entity, so no alias should appear. It is aliased however.

The sqlquery actually do the sorting on [dbo].[TBLcommerce_item].[sortOrder] instead of [LPA_I1].

I see it sorts on: ORDER BY [LPA_I1].[sortOrder]

as stated in the query.

If I modify the SQL it will work correctly when: 1. I Remove [dbo].[TBLcommerce_item].[sortOrder] AS [SortOrder] from select. 2. Change [dbo].[TBLcommerce_item].[sortOrder] AS [SortOrder] to [dbo].[TBLcommerce_item].[sortOrder] AS [SortOrderForSomethingElse] 3. Adds [LPA_I1].[sortOrder] to select.

So my question is: Is it possibly to do something so I can execute the LLBLgen code above without changing one of the fieldnames i LLBLgen Designer?

The field aliases are emitted because the table field name differs from the entity field name. This is standard procedure. So if you make the tablefields be SortOrder, the fields won't be aliased. You've to refresh the catalog after that and regen the code.

I fail to see why [dbo].[TBLcommerce_itemGroupItem] is aliased in the resultquery. You don't specify an alias for the entity, so no alias should appear. It is aliased however.

Sorry about that, I took the code from a much larger query and cut it down.

Real code for adding relations is like this:

IRelationCollection relations = new RelationCollection();
        // Add itemGroup relations for filtering
        if (commerceItemGroupGuidList != null) {
          for (int i = 0; i < commerceItemGroupGuidList.Count; i++) {
            relations.Add(CommerceItemEntity.Relations.CommerceItemGroupItemEntityUsingItemGuid, "ItemGroup" + i);
          }
        }

Wanted to simplify the example, but can see that gave some misinformation on the problem.

The field aliases are emitted because the table field name differs from the entity field name. This is standard procedure. So if you make the tablefields be SortOrder, the fields won't be aliased. You've to refresh the catalog after that and regen the code.

Thanks, I'll be looking into that right now.

Going to change a lot of columns to have capital start character.

I'm wondering if that will give some performance enhancements too as LLBLgen wont use aliases that much?

Changing the column names in the database and updating LLBLgen afterwards did the trick.

Many thanks.

It shouldn't have a performance impact.