A Derived Model is brilliant and very convenient to use. I just don't know if it is possible to filter the fetching of collections in a Derived Model? Something like Customer.Orders can ruin a query by fetching gazillion of orders.

A Derived Model is just a data structure, that you can use to project the resultset of a query. So you can compose a query as usual, with filters on all levels of the graph, and use the Derived Model in projection.

Oh OK. Then I think it will be useful to update this documentation that prefetch path filter does affect the data being projected to the derived model.

The generated projection methods automatically add prefetch path directives to the Linq query and QuerySpec query. It's therefore not necessary to specify prefetch paths to load the related entities for the projection.

https://www.llblgen.com/Documentation/5.8/Derived%20Models/dto_llblgen.htm

The section you're quoting means: if you have a DTO on 'Customer' with nested Order DTO's in it, you can pass a query on Customer to the projection and the fetch for Orders will be added for you by the projection through a system similar to prefetch paths. (it's basically a nested query like in Linq)

So if you pass in a query with prefetch path directives these are basically ignored, as the projection will add its own in the projection.

I re-read Walaa's answer and I see where the confusion comes from. The projection constructed by the Projection method makes the engine to ignore the prefetch paths.

You can project the entities of a graph in-memory to dto's tho, in which case you do control the graph to project:

Normal query and projection, no filter on orders

var q = GetIQueryable<CustomerEntity>().Where(c => c.VisitingAddressCountry == "USA");
var withOrders = q.ProjectToCustomerOrder().ToList();

and the equivalent with a prefetch path first and a filter:

var q = GetIQueryable<CustomerEntity>().Where(c => c.VisitingAddressCountry == "USA")
                                       .With(c=>c.Orders.FilterBy(o=>o.EmployeeId>4).With(op=>op.Employee));
var results = q.ToList();
var withOrders = results.Select(c=>c.ProjectToCustomerOrder()).ToList();

The first will result in 2 queries, no filters:

SELECT [LPLA_1].[CompanyName],
       [LPLA_1].[CustomerID] AS [CustomerId],
       1                     AS [LPFA_2]
FROM   [Northwind].[dbo].[Customers] [LPLA_1]
WHERE  (((((([LPLA_1].[Country] = @p1)))))) 

and

SELECT [LPA_L1].[FirstName],
       [LPA_L1].[LastName],
       [LPA_L2].[OrderDate],
       [LPA_L2].[OrderID]    AS [OrderId],
       [LPA_L2].[CustomerID] AS [CustomerId]
FROM   ([Northwind].[dbo].[Employees] [LPA_L1]
        INNER JOIN [Northwind].[dbo].[Orders] [LPA_L2]
            ON [LPA_L1].[EmployeeID] = [LPA_L2].[EmployeeID])
WHERE  ((([LPA_L2].[CustomerID] IN (@p1, @p2, @p3, @p4,
                                    @p5, @p6, @p7, @p8,
                                    @p9, @p10, @p11, @p12, @p13)))) 

The latter in 3 queries, namely for the prefetch path query

SELECT [LPA_L1].[CompanyName],
       [LPA_L1].[ContactName],
       [LPA_L1].[ContactTitle],
       [LPA_L1].[CustomerID] AS [CustomerId],
       [LPA_L1].[Fax],
       [LPA_L1].[Phone],
       [LPA_L1].[PostalCode],
       [LPA_L1].[Address]    AS [VisitingAddressAddress],
       [LPA_L1].[City]       AS [VisitingAddressCity],
       [LPA_L1].[Country]    AS [VisitingAddressCountry],
       [LPA_L1].[Region]     AS [VisitingAddressRegion]
FROM   [Northwind].[dbo].[Customers] [LPA_L1]
WHERE  ((([LPA_L1].[Country] = @p1))) 

SELECT [Northwind].[dbo].[Orders].[CustomerID] AS [CustomerId],
       [Northwind].[dbo].[Orders].[EmployeeID] AS [EmployeeId],
       [Northwind].[dbo].[Orders].[Freight],
       [Northwind].[dbo].[Orders].[OrderDate],
       [Northwind].[dbo].[Orders].[OrderID]    AS [OrderId],
       [Northwind].[dbo].[Orders].[RequiredDate],
       [Northwind].[dbo].[Orders].[ShipAddress],
       [Northwind].[dbo].[Orders].[ShipCity],
       [Northwind].[dbo].[Orders].[ShipCountry],
       [Northwind].[dbo].[Orders].[ShipName],
       [Northwind].[dbo].[Orders].[ShippedDate],
       [Northwind].[dbo].[Orders].[ShipPostalCode],
       [Northwind].[dbo].[Orders].[ShipRegion],
       [Northwind].[dbo].[Orders].[ShipVia]
FROM   [Northwind].[dbo].[Orders]
WHERE  ([Northwind].[dbo].[Orders].[CustomerID] IN (@p1, @p2, @p3, @p4,
                                                    @p5, @p6, @p7, @p8,
                                                    @p9, @p10, @p11, @p12, @p13)
    AND ([Northwind].[dbo].[Orders].[EmployeeID] > @p14)) 

SELECT [Northwind].[dbo].[Employees].[Address]    AS [AddressAddress],
       [Northwind].[dbo].[Employees].[City]       AS [AddressCity],
       [Northwind].[dbo].[Employees].[Country]    AS [AddressCountry],
       [Northwind].[dbo].[Employees].[Region]     AS [AddressRegion],
       [Northwind].[dbo].[Employees].[BirthDate],
       [Northwind].[dbo].[Employees].[EmployeeID] AS [EmployeeId],
       [Northwind].[dbo].[Employees].[Extension],
       [Northwind].[dbo].[Employees].[FirstName],
       [Northwind].[dbo].[Employees].[HireDate],
       [Northwind].[dbo].[Employees].[HomePhone],
       [Northwind].[dbo].[Employees].[LastName],
       [Northwind].[dbo].[Employees].[Notes],
       [Northwind].[dbo].[Employees].[Photo],
       [Northwind].[dbo].[Employees].[PhotoPath],
       [Northwind].[dbo].[Employees].[PostalCode],
       [Northwind].[dbo].[Employees].[RegionID]   AS [RegionId],
       [Northwind].[dbo].[Employees].[ReportsTo],
       [Northwind].[dbo].[Employees].[Title],
       [Northwind].[dbo].[Employees].[TitleOfCourtesy]
FROM   [Northwind].[dbo].[Employees]
WHERE  ([Northwind].[dbo].[Employees].[EmployeeID] IN (@p1, @p2, @p3, @p4, @p5))