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.

Example

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
                .CorrelatedOver(OrderEntity.Relations.CustomerEntityUsingCustomerId)
                .ToResultset()
             );

The SQL generated by the query above:

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 ((((EXISTS
    (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]))))))