Hello

I am pulling my hair out as I have no idea what I am doing wrong, I'm sure it must be obvious.. and I have read through the other threads..!

I am using adapter with LLBL version 2.6.

I am trying to call back an entity with a few prefectches and sort on a column in one of the prefetched tables!

The tables are: ProductControl ProductControlControlItem ProductControlItem

ProductControl is my entity, ProductControls contain ProductControlItems, and these are linked using the productcontrolcontrolitem.

ProductControlControlItem has 3 columns, ProductControlID, ProductControlItemID and order.

I want to call back A ProductControl with all its controlItems ordered by Order.

To me this sound simple enough.. but the ordering isn't working.

My first version of the code is here:

  public static ProductControlEntity GetControlWithItems(Guid controlId)
        {

            ProductControlEntity control = new ProductControlEntity(controlId);
            IPrefetchPath2 prefetchPath = new PrefetchPath2((int)EntityType.ProductControlEntity);
            SortExpression sorter = new SortExpression(ProductControlControlItemFields.Order | SortOperator.Ascending);
            IRelationCollection relation = new RelationCollection(ProductControlItemEntity.Relations.ProductControlControlItemEntityUsingControlItemId);

            prefetchPath.Add(ProductControlEntity.PrefetchPathProductControlItemCollectionViaProductControlControlItem, 0, null, relation, sorter);

            using (DataAccessAdapter adapter = new DataAccessAdapter())
            {
                adapter.FetchEntity(control, prefetchPath);
            }
            return control;
        }

This brings back everything but not in the right order.

I figured I must use a subpath or some sort.. here is one version I have tried (I've tried a few) and this just causes an error:

 public static ProductControlEntity GetControlWithItems(Guid controlId)
        {

            ProductControlEntity control = new ProductControlEntity(controlId);
            IPrefetchPath2 prefetchPath = new PrefetchPath2((int)EntityType.ProductControlEntity);
            SortExpression sorter = new SortExpression(ProductControlControlItemFields.Order | SortOperator.Ascending);
            IRelationCollection relation = new RelationCollection(ProductControlEntity.Relations.ProductControlControlItemEntityUsingControlId);
            IRelationCollection relation2 = new RelationCollection(ProductControlControlItemEntity.Relations.ProductControlItemEntityUsingControlItemId);
            prefetchPath.Add(ProductControlEntity.PrefetchPathProductControlControlItem, 0, null, relation, sorter).SubPath.Add(ProductControlEntity.PrefetchPathProductControlItemCollectionViaProductControlControlItem, 0, null, relation2);
        
            using (DataAccessAdapter adapter = new DataAccessAdapter())
            {
                adapter.FetchEntity(control, prefetchPath);
            }
            return control;
        }

I have tried other sub paths, as I am sure this must be the way to do it, but am getting either errors or nulls for some of the prefetches!

Please help!!

Bex

Why do you need the "relation" object - you don't need to join the link table to anything.

The sorter needs to go in the subpath.add to sort the Control items, which are linked back correctly using "relation2"

In addtion I would fetch the ProductControlItems using the pre-fetch path from the ProductControControlItem, rather than the via collection...

prefetchPath.Add(ProductControlEntity.PrefetchPathProductControlControlItem, 0, null, relation, sorter).SubPath.Add(ProductControlControlItemEntity.PrefetchPathProductControlItem, 0, null, relation2)

Hopefully the diagram below wil make sense...

ProductControl | | | Prefetch Path (ProductControlEntity.PrefetchPathProductControlControlItem) | | ProductControlControlItem | | | Prefetch Path (ProductControlControlItemEntity.PrefetchPathProductControlItem) | | "relation2" and "sorter" ProductControlItem ---------------------------> ProductControlControlItem

Matt

Hi MTrinder

Thanks for the reply, it is still not working in the way I want.. I have tried this:

   ProductControlEntity control = new ProductControlEntity(controlId);
            IPrefetchPath2 prefetchPath = new PrefetchPath2((int)EntityType.ProductControlEntity);
            SortExpression sorter = new SortExpression(ProductControlControlItemFields.Order | SortOperator.Ascending);
            IRelationCollection relation2 = new RelationCollection(ProductControlControlItemEntity.Relations.ProductControlItemEntityUsingControlItemId);
            prefetchPath.Add(ProductControlEntity.PrefetchPathProductControlControlItem).SubPath.Add( ProductControlControlItemEntity.PrefetchPathProductControlItem,0,null,relation2,sorter);

            using (DataAccessAdapter adapter = new DataAccessAdapter())
            {
                adapter.FetchEntity(control, prefetchPath);
            }

Is that what you meant?

I am then going

 EntityCollection<ProductControlItemEntity> controlItems = productControlEntity.ProductControlItemCollectionViaProductControlControlItem;

and this brings back nothing. When I look at the properties thing that pops up when debugging when you hover over the ProductControlEntity, the ProductControlItemCollection is null.

Am I completely missing the point? This seems such a simple thing to do, butI just can't get it to work?

Thanks

Bex

I think your first code version is the correct one (at least this is what I would have tried).

Which LLBLGen Pro runtime library version are you using?

Hi Walaa

I am using adapter and version 2.6 final.

Is this what you mean by library version?

Have you any other ideas?

Thanks Bex

Please echeck the following link to get the correct RTL version number: http://www.llblgen.com/TinyForum/Messages.aspx?ThreadID=7722

Walaa wrote:

Please echeck the following link to get the correct RTL version number: http://www.llblgen.com/TinyForum/Messages.aspx?ThreadID=7722

Sorry!

The version I am using is 2.6.08.0606

That's a very old version (a pinoeer one ) And I think this issue was reported before and have been solved.

Would you please check this out, by downloading and trying the latest available version?

Really!!?

Oh that may explain it! I don't remember the last time we downloaded a new one..!

Will do that now and let you know how it goes!

Ok.. upgraded it and it still doesn't work!

Help!

Mmmm.. weird. Could you reproduce the same thing with some Northwind m:n (via) relation?

(Edit) What is the SQL generated you see?

Also try to explain a bit more (perhaps I missed it in the posts, if so, please forgive me) what 'sorting doesn't work' means. If sorting means that you get a 'sorted' result but not the way you expected it, it might be that you sort on a field which contains duplicates and in the case of duplicates, the set order is undefined (by definition of SQL)

Hello Both

I have just tested this against the northwind database, and it works. Using exactly the same code (obv substituting the entities)

I am now trying to work out what the difference is between my database and the northwind one.

When I say sorting doesnt work, I mean it just come back in the order that it is in the DB and not ordered by the "order" column.

hmm..!

Bex

Please enable tracing and check the generated SQL query, see if the ORDER BY was correctly emitted, and then try to run this query manually against the database and examine the returned resultset.

Walaa wrote:

Please enable tracing and check the generated SQL query, see if the ORDER BY was correctly emitted, and then try to run this query manually against the database and examine the returned resultset.

Sorry it has taken me so long but I have now turned on tracing, and found the query it's sending is:

SELECT [dbo].[ProductControlItem].[ControlItemId], [dbo].[ProductControlItem].[Name], [dbo].[ProductControlItem].[ItemLocXML] AS [ItemLocXml], [dbo].[ProductControlItem].[PortalId] FROM ((( [dbo].[ProductControl] [LPA_P1] INNER JOIN [dbo].[ProductControlControlItem] [LPA_P2] ON [LPA_P1].[ControlId]=[LPA_P2].[ControlId]) INNER JOIN [dbo].[ProductControlItem] ON [dbo].[ProductControlItem].[ControlItemId]=[LPA_P2].[ControlItemId]) INNER JOIN [dbo].[ProductControlControlItem] ON [dbo].[ProductControlItem].[ControlItemId]=[dbo].[ProductControlControlItem].[ControlItemId]) WHERE ( ( ( [LPA_P2].[ControlId] = @ControlId1))) ORDER BY [dbo].[ProductControlControlItem].[order] ASC

which is very strange.. it seems to be joining too many times, it has got the order by, but because of the actual query it doesn't work.

running it in a query window bring back lots of duplicates.. so is definitly wrong.

Have you any ideas why it is doing this, as I can't see anything wrong with my database structure?

Just incase you need.. the code I am running is this:

 ProductControlEntity control = new ProductControlEntity(controlId);
            IPrefetchPath2 prefetchPath = new PrefetchPath2((int)EntityType.ProductControlEntity);
            SortExpression sorter = new SortExpression(ProductControlControlItemFields.Order | SortOperator.Ascending);
            IRelationCollection relation = new RelationCollection(ProductControlItemEntity.Relations.ProductControlControlItemEntityUsingControlItemId);

            prefetchPath.Add(ProductControlEntity.PrefetchPathProductControlItemCollectionViaProductControlControlItem, 0, null, relation, sorter);

            using (DataAccessAdapter adapter = new DataAccessAdapter())
            {
              adapter.FetchEntity(control, prefetchPath);
               }

Bex

Would you please check this thread (exact same issue): http://www.llblgen.com/TinyForum/Messages.aspx?ThreadID=15372

Please post back here if you still can't solve it.

Hello!

Thank you for all your help.

I now have it working using the following code:

  string alias = "LPA_P2";
            ProductControlEntity control = new ProductControlEntity(controlId);
            IPrefetchPath2 prefetchPath = new PrefetchPath2((int)EntityType.ProductControlEntity);
            ISortExpression sorter = new SortExpression(ProductControlControlItemFields.Order.SetObjectAlias(alias) | SortOperator.Ascending);

            prefetchPath.Add(ProductControlEntity.PrefetchPathProductControlItemCollectionViaProductControlControlItem, 0, null, null, sorter);

            using (DataAccessAdapter adapter = new DataAccessAdapter())
            {
                adapter.FetchEntity(control, prefetchPath);
            }

I am a little confused by the alias' though, am I correct in thinking if I leave it as "LPA_P2" this will be ok as it always remains this, or do I need to change it to something else?

It did explain it somewhat in a link on that suggested post, but it left me confused.

Thanks

Bex

You should be able to use any alias of your choosing - could you try this please ?

Matt

MTrinder wrote:

You should be able to use any alias of your choosing - could you try this please ?

Matt

It does not work if I set the alias in this line

(ProductControlControlItemFields.Order.SetObjectAlias(alias) | SortOperator.Ascending);

to anything else because all this does is go "order by [alias].order" but leaves the join to the table as LPA_P2, I would need to set the alias on the join table which I am not sure how to do, but if the alias always remains the same it doesnt matter!

BexMed wrote:

I am a little confused by the alias' though, am I correct in thinking if I leave it as "LPA_P2" this will be ok as it always remains this, or do I need to change it to something else?

That's ok.

Please use the alias workaround. If possible, please prepare a tiny repro solution with your DB structure and your LGP so we can reproduce the problem and see whether or not this is something that needs to be fixed. If you prepare this repro solution, please zip it and don't include binaries (just code).