Specifying correlation predicates / relationships

When creating a correlated sub-query, it's easier to specify the predicates through a specific method, based on a generated relation. This helps avoiding memorizing which FK fields tie which entities together.

Correlation predicates can be specified directly using the .Where() extension method of a query object as with any other predicate for the query or by using the method .CorrelatedOver(predicate). To specify a specific correlation relation for a subquery, use the method .CorrelatedOver(relation).

The CorrelatedOver(relation) method has some overloads to specify aliases for start and/or end entity. The relation specified in the overloads which accept an EntityRelation is used to produce the predicate to correlate the two queries, based on the fk/pk fields in the relationship.

The correlation predicate (either specified directly or constructed from the relation specified) will be appended to the Where clause specified with And as a normal predicate. CorrelatedOver(relation) is only available on an EntityQuery instance.


It's recommended to use CorrelatedOver() instead of formulating your own correlation predicate because using CorrelatedOver() allows the runtime to exclude the relationship from the set of relationships for detecting whether inheritance related information has to be added.


The following example will fetch a list of order collections based on a query on the related customer entity: per customer matching the outer query a query on the orders is executed which is correlated to the outer query.

var qf = new QueryFactory();
var q = qf.Customer
            .Where(CustomerFields.CustomerId.NotIn(new List<string>() { "FISSA", "PARIS" }))
            .Select(() => qf.Order

The SQL generated by the query above:

SELECT [Northwind].[dbo].[Orders].[CustomerID] AS [CustomerId],
    [Northwind].[dbo].[Orders].[EmployeeID] AS [EmployeeId],
    [Northwind].[dbo].[Orders].[OrderID] AS [OrderId],
FROM [Northwind].[dbo].[Orders]
    (SELECT [Northwind].[dbo].[Customers].[CustomerID] AS [CustomerId]
    FROM [Northwind].[dbo].[Customers]
    WHERE (((([Northwind].[dbo].[Customers].[CustomerID] 
            NOT IN ('FISSA' /* @p1 */, 'PARIS' /* @p2 */))))
        AND [Northwind].[dbo].[Orders].[CustomerID] = [Northwind].[dbo].[Customers].[CustomerID]))))))