I am in need of help in how to write the following query using the predicate factory. I need this to work in a prefetch path for the CustomerOrderEntity. What I am doing is loading the CustomerOrder entity and then prefetching the Build and BuildItem tables. The BuildItem table then has a custom relation with the ProductType table. Is this possible?

Here is the current prefetch path that I have setup and would like to add this additional prefetch/filter to get the Description of the frame.

            IPrefetchPath2 prefetchPath = new PrefetchPath2( (int)EntityType.CustomerOrderEntity );
            IPrefetchPathElement2 buildNode = prefetchPath.Add( MyCustomerOrderEntity.PrefetchPathBuild );
            IPrefetchPathElement2 buildItemNode = buildNode.SubPath.Add( BuildEntity.PrefetchPathBuildItem, new MyBuildItemEntityFactory() );
            buildItemNode.SubPath.Add( MyBuildItemEntity.PrefetchPathProductType );

SELECT
    BI.WebDescription FrameDescription
FROM
    WS2.dbo.CustomerOrder CO
INNER JOIN
    WS2.dbo.Build B ON CO.BuildID = B.BuildID
INNER JOIN
    WS2.dbo.BuildItem BI ON B.BuildID = BI.BuildID AND (BI.ProductTypeID = (SELECT ProductTypeID FROM WS2.dbo.ProductType WHERE TypeName = 'Frame'))
WHERE
    CustomerOrderID = 1

I don't understand your question, sorry.

If I look at your prefetch path, you already fetch the BuildItem, so isn't it already available in your object graph? The trouble I have with your question is mainly with the sql you specified, as that's a fetch of a list of values, and your prefetch path fetches objects in a graph, so I'm not sure what you want to accomplish.

Otis wrote:

I don't understand your question, sorry.

If I look at your prefetch path, you already fetch the BuildItem, so isn't it already available in your object graph? The trouble I have with your question is mainly with the sql you specified, as that's a fetch of a list of values, and your prefetch path fetches objects in a graph, so I'm not sure what you want to accomplish.

Hello Otis, this is hard for me to explain.

I'm trying to query data from 2 tables.

Build BuildItem (BuildItem.BuildId is FK to Build.BuidId)

I want to select all the distinct rows from the build table while adding in a column to this query from the BuildItem table where the column is BuildItem.WebDescription and the ProductType being stored for the record is "Frame". I expect the BuildItem table to have one frame or no frame. So if there is a product type of Frame in the table then that frame's WebDescription will show up in the column from the query. In the case where there is not a frame in the BuildItem table then the column value for WebDescription will be null. This was easy to do using a typed view, but I need to do this using entities. I have tried using a derived entity of Build and in the derived Build entity creating a property that returns Build.BuildItem.WebDescription, but doing this causes a duplicate Build to be displayed for each BuildItem in the Build.

Does this make more sense?

Your prefetch path should fetch the right graph as it seems, although you have to add the frame predicate filter.

If you add the property to the class, which returns builditem.Webdescription, you should get a value back. I fail to see why duplicates in this have any relation with that property, as that property simply should return this.BuildItem.WebDescription. Or is Build-BuildItem an 1:n relation and you need frame filtering on the builditem fetch in the prefecth path?