I have these 3 Entities based on views;

OrderHeader OrderLineItem Project

I've mapped a field onto the OrderLineItem Entity from the Project Entity. I use the following code to fetch the graph;

public static EntityCollection GetPurchaseOrders(string projectNumber, string connection)
        {
            EntityCollection orders = new EntityCollection(new OrderHeaderEntityFactory());
            IPrefetchPath2 prefetchPath = new PrefetchPath2((int)EntityType.OrderHeaderEntity);
            prefetchPath.Add(OrderHeaderEntity.PrefetchPathOrderHeaderLineItem).SubPath.
                Add(OrderLineItemEntity.PrefetchPathOrderLineItemProject);

            RelationPredicateBucket bucket = new RelationPredicateBucket();
            bucket.Relations.Add(OrderHeaderEntity.Relations.OrderLineItemEntityUsingPapurordnum);
            bucket.Relations.Add(OrderLineItemEntity.Relations.ProjectEntityUsingPaprojnumber);
            bucket.PredicateExpression.Add(PredicateFactory.CompareValue(OrderLineItemFieldIndex.Paprojnumber, 
                ComparisonOperator.Equal, projectNumber));

            using (DataAccessAdapter adapter = new DataAccessAdapter(connection))
            {
                adapter.FetchEntityCollection(orders, bucket, prefetchPath);
            }
        
            return orders;
        }

This fails to retrieve the project side of the graph and I cannot figure out why. Please help.

Thanks,

KZA

IPrefetchPath2 prefetchPath = new PrefetchPath2((int)EntityType.OrderHeaderEntity);
prefetchPath.Add(OrderHeaderEntity.<<<PrefetchPathOrderHeaderLineItem>>>).SubPath.
Add(<<<OrderLineItemEntity>>>.PrefetchPathOrderLineItemProject);

Are OrderHeaderLineItem and OrderLineItem different entities? That looks strange to me offhand.

Are you getting errors or do you just not see the data you need?

psandler wrote:

IPrefetchPath2 prefetchPath = new PrefetchPath2((int)EntityType.OrderHeaderEntity);
prefetchPath.Add(OrderHeaderEntity.<<<PrefetchPathOrderHeaderLineItem>>>).SubPath.
Add(<<<OrderLineItemEntity>>>.PrefetchPathOrderLineItemProject);

Are OrderHeaderLineItem and OrderLineItem different entities? That looks strange to me offhand.

Are you getting errors or do you just not see the data you need?

OrderHeaderLineItem is the OrderHeader field mapped on the 'OrderHeader-OrderLineItem' relation. OrderLineItem is a child entity of OrderHeader. I am not gettng an error or the ProjectEntity.

Thanks,

Kza

I first had the same idea as psandler, thanks for asking that question

You specify 2 relations, though the second isn't necessary as you don't filter on product's fields and that second relation is just used to add product to the set of entities to join on.

Could you enable SQL tracing for me please to see which queries are generated with which values passed in as parameters? Consult the 'Using the generated code -> troubleshooting and debugging' section in the manual for details how to enable tracing. Please enable tracing for your database on level 4.

It could be the value you pass in as projectnumber isn't correct, as the code seems to be correct to me.

Otis wrote:

I first had the same idea as psandler, thanks for asking that question

You specify 2 relations, though the second isn't necessary as you don't filter on product's fields and that second relation is just used to add product to the set of entities to join on.

Could you enable SQL tracing for me please to see which queries are generated with which values passed in as parameters? Consult the 'Using the generated code -> troubleshooting and debugging' section in the manual for details how to enable tracing. Please enable tracing for your database on level 4.

It could be the value you pass in as projectnumber isn't correct, as the code seems to be correct to me.

The SQL Trace shows these calls;

exec sp_executesql N'SELECT [tp].[dbo].[pa_vw_OrderHeader].[PApurordnum] AS [Papurordnum],[tp].[dbo].[pa_vw_OrderHeader].[ShortNumber] AS [ShortNumber],[tp].[dbo].[pa_vw_OrderHeader].[POSTATUS] AS [Postatus],[tp].[dbo].[pa_vw_OrderHeader].[POTYPE] AS [Potype],[tp].[dbo].[pa_vw_OrderHeader].[USER2ENT] AS [User2Ent],[tp].[dbo].[pa_vw_OrderHeader].[DOCDATE] AS [Docdate],[tp].[dbo].[pa_vw_OrderHeader].[SUBTOTAL] AS [Subtotal],[tp].[dbo].[pa_vw_OrderHeader].[VENDORID] AS [Vendorid],[tp].[dbo].[pa_vw_OrderHeader].[VENDNAME] AS [Vendname],[tp].[dbo].[pa_vw_OrderHeader].[CURNCYID] AS [Curncyid],[tp].[dbo].[pa_vw_OrderHeader].[REMSUBTO] AS [Remsubto],[tp].[dbo].[pa_vw_OrderHeader].[PACONTNUMBER] AS [Pacontnumber],[tp].[dbo].[pa_vw_OrderHeader].[Comment] AS [Comment],[tp].[dbo].[pa_vw_OrderHeader].[REQDATE] AS [Reqdate],[tp].[dbo].[pa_vw_OrderHeader].[DEX_ROW_ID] AS [DexRowId] FROM (( [tp].[dbo].[pa_vw_OrderHeader] INNER JOIN [tp].[dbo].[pa_vw_OrderLineitem] ON  [tp].[dbo].[pa_vw_OrderHeader].[PApurordnum]=[tp].[dbo].[pa_vw_OrderLineitem].[PApurordnum]) INNER JOIN [tp].[dbo].[pa_vw_project] ON  [tp].[dbo].[pa_vw_project].[PAPROJNUMBER]=[tp].[dbo].[pa_vw_OrderLineitem].[PAPROJNUMBER]) WHERE ( [tp].[dbo].[pa_vw_OrderLineitem].[PAPROJNUMBER] = @Paprojnumber1)', N'@Paprojnumber1 char(15)', @Paprojnumber1 = 'tpt007334LP1   '

exec sp_executesql N'SELECT [tp].[dbo].[pa_vw_OrderLineitem].[PApurordnum] AS [Papurordnum],[tp].[dbo].[pa_vw_OrderLineitem].[PAPROJNUMBER] AS [Paprojnumber],[tp].[dbo].[pa_vw_OrderLineitem].[ITEMNMBR] AS [Itemnmbr],[tp].[dbo].[pa_vw_OrderLineitem].[ITEMDESC] AS [Itemdesc],[tp].[dbo].[pa_vw_OrderLineitem].[VNDITDSC] AS [Vnditdsc],[tp].[dbo].[pa_vw_OrderLineitem].[PACOSTCATID] AS [Pacostcatid],[tp].[dbo].[pa_vw_OrderLineitem].[QtyCance] AS [QtyCance],[tp].[dbo].[pa_vw_OrderLineitem].[QtyOrder] AS [QtyOrder],[tp].[dbo].[pa_vw_OrderLineitem].[UNITCOST] AS [Unitcost],[tp].[dbo].[pa_vw_OrderLineitem].[EXTDCOST] AS [Extdcost],[tp].[dbo].[pa_vw_OrderLineitem].[UofM] AS [UofM],[tp].[dbo].[pa_vw_OrderLineitem].[DEX_ROW_ID] AS [DexRowId],[tp].[dbo].[pa_vw_OrderLineitem].[ORD] AS [Ord],[tp].[dbo].[pa_vw_OrderLineitem].[SWordCount] AS [SwordCount] FROM [tp].[dbo].[pa_vw_OrderLineitem] WHERE ( [tp].[dbo].[pa_vw_OrderLineitem].[PApurordnum] IN (SELECT [tp].[dbo].[pa_vw_OrderHeader].[PApurordnum] AS [Papurordnum] FROM (( [tp].[dbo].[pa_vw_OrderHeader] INNER JOIN [tp].[dbo].[pa_vw_OrderLineitem] ON  [tp].[dbo].[pa_vw_OrderHeader].[PApurordnum]=[tp].[dbo].[pa_vw_OrderLineitem].[PApurordnum]) INNER JOIN [tp].[dbo].[pa_vw_project] ON  [tp].[dbo].[pa_vw_project].[PAPROJNUMBER]=[tp].[dbo].[pa_vw_OrderLineitem].[PAPROJNUMBER]) WHERE ( [tp].[dbo].[pa_vw_OrderLineitem].[PAPROJNUMBER] = @Paprojnumber1)))', N'@Paprojnumber1 char(15)', @Paprojnumber1 = 'tpt007334LP1   '

exec sp_executesql N'SELECT [tp].[dbo].[pa_vw_project].[CUSTNMBR] AS [Custnmbr],[tp].[dbo].[pa_vw_project].[PACONTNUMBER] AS [Pacontnumber],[tp].[dbo].[pa_vw_project].[PAprojname] AS [Paprojname],[tp].[dbo].[pa_vw_project].[PAprojid] AS [Paprojid],[tp].[dbo].[pa_vw_project].[PAPROJNUMBER] AS [Paprojnumber],[tp].[dbo].[pa_vw_project].[PAprjclsid] AS [Paprjclsid],[tp].[dbo].[pa_vw_project].[PAProjectType] AS [PaprojectType],[tp].[dbo].[pa_vw_project].[PAAcctgMethod] AS [PaacctgMethod],[tp].[dbo].[pa_vw_project].[PASTAT] AS [Pastat],[tp].[dbo].[pa_vw_project].[PABBeginDate] AS [PabbeginDate],[tp].[dbo].[pa_vw_project].[PABEndDate] AS [PabendDate],[tp].[dbo].[pa_vw_project].[PABQuantity] AS [Pabquantity],[tp].[dbo].[pa_vw_project].[PABTotalCost] AS [PabtotalCost],[tp].[dbo].[pa_vw_project].[PABProfit] AS [Pabprofit],[tp].[dbo].[pa_vw_project].[PABTaxChargedAmt] AS [PabtaxChargedAmt],[tp].[dbo].[pa_vw_project].[PAprojmngrid] AS [Paprojmngrid],[tp].[dbo].[pa_vw_project].[PADepartment] AS [Padepartment],[tp].[dbo].[pa_vw_project].[PAclosetobillings] AS [Paclosetobillings],[tp].[dbo].[pa_vw_project].[PAcloseProjcosts] AS [PacloseProjcosts],[tp].[dbo].[pa_vw_project].[PABusMgrID] AS [PabusMgrId],[tp].[dbo].[pa_vw_project].[LOCATNID] AS [Locatnid],[tp].[dbo].[pa_vw_project].[SLPRSNID] AS [Slprsnid],[tp].[dbo].[pa_vw_project].[SALSTERR] AS [Salsterr],[tp].[dbo].[pa_vw_project].[NOTEINDX] AS [Noteindx],[tp].[dbo].[pa_vw_project].[DEX_ROW_ID] AS [DexRowId],[tp].[dbo].[pa_vw_project].[PAbillnoteidx] AS [Pabillnoteidx] FROM [tp].[dbo].[pa_vw_project] WHERE ( [tp].[dbo].[pa_vw_project].[PAPROJNUMBER] IN (SELECT [tp].[dbo].[pa_vw_OrderLineitem].[PAPROJNUMBER] AS [Paprojnumber] FROM [tp].[dbo].[pa_vw_OrderLineitem] WHERE ( [tp].[dbo].[pa_vw_OrderLineitem].[PApurordnum] IN (SELECT [tp].[dbo].[pa_vw_OrderHeader].[PApurordnum] AS [Papurordnum] FROM (( [tp].[dbo].[pa_vw_OrderHeader] INNER JOIN [tp].[dbo].[pa_vw_OrderLineitem] ON  [tp].[dbo].[pa_vw_OrderHeader].[PApurordnum]=[tp].[dbo].[pa_vw_OrderLineitem].[PApurordnum]) INNER JOIN [tp].[dbo].[pa_vw_project] ON  [tp].[dbo].[pa_vw_project].[PAPROJNUMBER]=[tp].[dbo].[pa_vw_OrderLineitem].[PAPROJNUMBER]) WHERE ( [tp].[dbo].[pa_vw_OrderLineitem].[PAPROJNUMBER] = @Paprojnumber1)))))', N'@Paprojnumber1 char(15)', @Paprojnumber1 = 'tpt007334LP1   '

Thanks,

Kevin

There's a space at the end of project number: "tpt007334LP1 ".

It could be that is the issue. Could you first trim the value and then run the code you wrote?

Otis wrote:

There's a space at the end of project number: "tpt007334LP1 ".

It could be that is the issue. Could you first trim the value and then run the code you wrote?

All three queries return the correct data. Another question: does a mapped field fetch the entire related object or jus the field?

Thanks,

kza

kza wrote:

Otis wrote:

There's a space at the end of project number: "tpt007334LP1 ".

It could be that is the issue. Could you first trim the value and then run the code you wrote?

All three queries return the correct data.

They return the correct data, so project entity data is fetched though no project entity turns up in the graph?

Another question: does a mapped field fetch the entire related object or jus the field?

You mean a field mapped onto a related field? In adapter, it doesn't fetch anything, you have to prefetch the related object, and then the field will return the value of the related field.

Otis wrote:

kza wrote:

Otis wrote:

There's a space at the end of project number: "tpt007334LP1 ".

It could be that is the issue. Could you first trim the value and then run the code you wrote?

All three queries return the correct data.

They return the correct data, so project entity data is fetched though no project entity turns up in the graph?

Another question: does a mapped field fetch the entire related object or jus the field?

You mean a field mapped onto a related field? In adapter, it doesn't fetch anything, you have to prefetch the related object, and then the field will return the value of the related field.

That is correct, no project entity. Also, It would be nice to just get the mapped field without fetching the entire related entity.

Thanks,

kza

kza wrote:

Otis wrote:

kza wrote:

Otis wrote:

There's a space at the end of project number: "tpt007334LP1 ".

It could be that is the issue. Could you first trim the value and then run the code you wrote?

All three queries return the correct data.

They return the correct data, so project entity data is fetched though no project entity turns up in the graph?

Another question: does a mapped field fetch the entire related object or jus the field?

You mean a field mapped onto a related field? In adapter, it doesn't fetch anything, you have to prefetch the related object, and then the field will return the value of the related field.

That is correct, no project entity.

And the query as executed on the db does return a project entity's data...

when I try to mimic your query on northwind, by using a filter on product instead of project, I can't reproduce it, this succeeds:

[Test]
public void PrefetchPathTestMultiLevel()
{
    using(DataAccessAdapter adapter = new DataAccessAdapter())
    {
        EntityCollection orders = new EntityCollection(new OrderEntityFactory());
        RelationPredicateBucket filter = new RelationPredicateBucket();
        filter.Relations.Add(OrderEntity.Relations.OrderDetailsEntityUsingOrderId);
        filter.Relations.Add(OrderDetailsEntity.Relations.ProductEntityUsingProductId);
        filter.PredicateExpression.Add(PredicateFactory.CompareValue(ProductFieldIndex.SupplierId, ComparisonOperator.Equal, 3));
        PrefetchPath2 path = new PrefetchPath2((int)EntityType.OrderEntity);
        path.Add(OrderEntity.PrefetchPathOrderDetails).SubPath.Add( OrderDetailsEntity.PrefetchPathProduct);

        adapter.FetchEntityCollection(orders, filter, path);

        foreach(OrderEntity order in orders)
        {
            foreach(OrderDetailsEntity orderDetails in order.OrderDetails)
            {
                Assert.IsNotNull(orderDetails.Product);
            }
        }
    }
}

Also, It would be nice to just get the mapped field without fetching the entire related entity.

That's not supported because what to do if you change the value? In which entity should it be saved?