Sounds obscure but somehow I hit it. My examples are from LinqPad against Northwind. Filter methods, the top two are logically the same:

    public static IQueryable<EmployeeEntity> FilterByCustomerTypeIdViaOrders(this IQueryable<EmployeeEntity> employees, string customerTypeId)
    {
      return from e in employees
             from c in e.CustomersViaOrders
             from ccd in c.CustomerCustomerDemos
             where ccd.CustomerTypeId == customerTypeId
             select e;
    }   

public static IQueryable<EmployeeEntity> FilterByCustomerTypeId(this IQueryable<EmployeeEntity> employees, string customerTypeId)
    {
      return from e in employees
             from o in e.Orders
             from ccd in o.Customer.CustomerCustomerDemos
             where ccd.CustomerTypeId == customerTypeId
             select e;
    }

    public static IQueryable<EmployeeEntity> FilterByOrder(this IQueryable<EmployeeEntity> employees, int orderId)
    {
      return from e in employees
             from o in e.Orders
             where o.OrderId == orderId
             select e;
    }

This

Employee.FilterByOrder(1).FilterByCustomerTypeId("ALFKI")

gives

DECLARE @p1 Int; SET @p1=1
DECLARE @p2 NChar(10); SET @p2='ALFKI'
SELECT [LPA_L1].[Address],
  [LPA_L1].[BirthDate],
  [LPA_L1].[City],
  [LPA_L1].[Country],
  [LPA_L1].[EmployeeId],
  [LPA_L1].[Extension],
  [LPA_L1].[FirstName],
  [LPA_L1].[HireDate],
  [LPA_L1].[HomePhone],
  [LPA_L1].[LastName],
  [LPA_L1].[Notes],
  [LPA_L1].[Photo],
  [LPA_L1].[PhotoPath],
  [LPA_L1].[PostalCode],
  [LPA_L1].[Region],
  [LPA_L1].[ReportsTo],
  [LPA_L1].[Title],
  [LPA_L1].[TitleOfCourtesy]
FROM
 ((( (SELECT [LPA_L5].[Address],
  [LPA_L5].[BirthDate],
  [LPA_L5].[City],
  [LPA_L5].[Country],
  [LPA_L5].[EmployeeID] AS [EmployeeId],
  [LPA_L5].[Extension],
  [LPA_L5].[FirstName],
  [LPA_L5].[HireDate],
  [LPA_L5].[HomePhone],
  [LPA_L5].[LastName],
  [LPA_L5].[Notes],
  [LPA_L5].[Photo],
  [LPA_L5].[PhotoPath],
  [LPA_L5].[PostalCode],
  [LPA_L5].[Region],
  [LPA_L5].[ReportsTo],
  [LPA_L5].[Title],
  [LPA_L5].[TitleOfCourtesy]
FROM
 ( [dbo].[Employees] [LPA_L5] 
INNER JOIN
 [dbo].[Orders] [LPA_L6]  ON  [LPA_L5].[EmployeeID]=[LPA_L6].[EmployeeID])
WHERE
 ( ( ( [LPA_L6].[OrderID] = @p1)))) [LPA_L1] 
INNER JOIN
 [dbo].[Orders] [LPA_L2]  ON  ( [LPA_L1].[EmployeeId] = [LPA_L2].[EmployeeID]))
LEFT JOIN
 [dbo].[Customers] [LPA_L3]  ON  [LPA_L3].[CustomerID]=[LPA_L2].[CustomerID])
INNER JOIN
 [dbo].[CustomerCustomerDemo] [LPA_L4]  ON  [LPA_L3].[CustomerID]=[LPA_L4].[CustomerID])
WHERE
 ( ( ( ( ( [LPA_L4].[CustomerTypeID] = @p2)))))

with both parameters which is correct but the other variation

Employee.FilterByOrder(1).FilterByCustomerTypeIdViaOrders("ALFKI")

gives

DECLARE @p1 Int; SET @p1=1
DECLARE @p2 NChar(10); SET @p2='ALFKI'
SELECT [LPA_L1].[Address],
  [LPA_L1].[BirthDate],
  [LPA_L1].[City],
  [LPA_L1].[Country],
  [LPA_L1].[EmployeeId],
  [LPA_L1].[Extension],
  [LPA_L1].[FirstName],
  [LPA_L1].[HireDate],
  [LPA_L1].[HomePhone],
  [LPA_L1].[LastName],
  [LPA_L1].[Notes],
  [LPA_L1].[Photo],
  [LPA_L1].[PhotoPath],
  [LPA_L1].[PostalCode],
  [LPA_L1].[Region],
  [LPA_L1].[ReportsTo],
  [LPA_L1].[Title],
  [LPA_L1].[TitleOfCourtesy]
FROM
 ((( [dbo].[Employees] [LPA_L1] 
INNER JOIN
 [dbo].[Orders] [LPA_O2]  ON  [LPA_L1].[EmployeeId]=[LPA_O2].[EmployeeID])
INNER JOIN
 [dbo].[Customers] [LPA_L3]  ON  [LPA_L3].[CustomerID]=[LPA_O2].[CustomerID])
INNER JOIN
 [dbo].[CustomerCustomerDemo] [LPA_L4]  ON  ( [LPA_L3].[CustomerID] = [LPA_L4].[CustomerID]))
WHERE
 ( ( ( ( ( [LPA_L4].[CustomerTypeID] = @p2)))))

which is missing the first filter -@p1. If I swap the order it works

Employee.FilterByCustomerTypeId("ALFKI").FilterByOrder(1)

SD.LLBLGen.Pro.LinqSupportClasses.NET35.dll 3.5.12.1026 SD.LLBLGen.Pro.ORMSupportClasses.NET20.dll 3.5.12.1004

strange! Looks like a merge error (the wrong one also doesn't have a derived table, it's completely gone).

Will look into it.

(edit) It clearly ditches a derived table somewhere, as the projection expects to fetch from the derived table, check the renamed fields. In my testdb, I have renamed the phone field, and it dies with illegal field 'phone', which is expected when the derived table with the first where is not there.

Question now is: does it arrive in low-level api form at the runtime or is the linq provider borked...

(edit) derived table is present in relationships... Might be alias error. This might take a while, hopefully I have more news tomorrow.

(edit) it sees the derived table as an element which is already in the query, so it skips the relationship. This isn't what it should do of course, so we've to tweak the checks to make this one stay and other duplicates between derived tables & normal tables still be weeded out.

It is a problem with the linq provider, it aliases the derived table in relationship at index 2 and the entity table in relationship at index 0 the same: LPLA_5. This means the runtime will see the derived table as equal and skip it, like it should.

So we'll look into how it can happen that the employee entity is aliased as LPLA_5 as well as the query returning employee entities, and whether we can solve that. Aliases...

Ok, this is the problem: The query tree in linq looks like: Left: Query<Employee> Join Right: Customer.

Query<Employee> has alias X. Customer has 2 relationships internally, the ones to make the relation with Employee (also Alias X): Customer - Order and Order - Employee (X)

the current linq join handler tries to join left with right. However that's not going to work in this case, it has to transform everything, as it has to do Query<Employee> Join Order Join Customer.

How this is currently solved is to skip duplicates if they pop up in the joins, in the runtime itself. This works OK, for most situations, except when everything has to be transformed so the joins have to be broken up and re-created.

So I can't fix this at this point. It's an edge case, as it only occurs in your specific situation where the left side is a query with a return type which is part of the correlated relations of the right side. Please rewrite the query.