A nested query in the projection has no correlation filter to tie its set to the containing parent row. Please add a correlation filter to the where clause of this query to tie the nested query to the parent.

Posts   
 
    
mprothme avatar
mprothme
User
Posts: 34
Joined: 05-Oct-2017
# Posted on: 16-Sep-2019 21:01:58   

We're currently using version 5.5 (5.5.0) RTM, Build Date: 02-Nov-2018

Our application has a date bound pricing data structure (each record has a start and an end date). Additionally, each point is tied to a curve (a collection of points for the same product).

I'm trying to write a query that pulls back the record with the latest start date for each curve. My code is as follows:


priceQueryable
.GroupBy(x => x.PriceCurveId)
.Select(pointGroup => pointGroup.First(point => point.EffectiveFromDateTime == pointGroup.Max(x => x.EffectiveFromDateTime)))
.ToList()

When I run the code I get the following error:

A nested query in the projection has no correlation filter to tie its set to the containing parent row. Please add a correlation filter to the where clause of this query to tie the nested query to the parent.

I'm assuming that the following lines are causing the issue:

point.EffectiveFromDateTime == pointGroup.Max(x => x.EffectiveFromDateTime)

but I'm not sure how to proceed. Is what I want to do possible within the framework?

Thanks!

Otis avatar
Otis
LLBLGen Pro Team
Posts: 38110
Joined: 17-Aug-2003
# Posted on: 17-Sep-2019 11:18:50   

The Linq 'groupby' is not really a SQL group by, but a method which results in a set of sets. Your Select therefore tries to create a set of sets, and this is seen as a set of nested queries (one for each set) and it therefore needs to tie each nested set to a parent row, with a correlated predicate. However, what you want to do isn't creating a set of sets, but a single select with a where that limits the rows based on another query.

You really have to specify how the table or tables involved look like, and e.g. what 'priceQueryable' is. You talk about prices and points, and these aren't the same.

So I assume it's as simple as the Orders table in Northwind where I'll fetch the orders for each customer with the latest orderdate. Your query likely looks the same, more or less. It helps if you write it out in SQL first or at least know where it'll need to go, especially with Linq, as constructs like groupby aren't really mappable in a 1:1 fashion to SQL.

the sql query becomes:

SELECT A.*
FROM Orders A
INNER JOIN (
  SELECT CustomerId, MAX(OrderDate) MaxOrderDate
  FROM Orders 
  GROUP BY Customerid
) B ON A.CustomerId = B.CustomerId AND A.OrderDate= B.MaxOrderDate

and in linq this will become


var metaData = new LinqMetaData(adapter);
var q = from o1 in metaData.Order
        join o2 in (from x in metaData.Order
                    group x by x.CustomerId into g
                    select new { CustomerId= g.Key, OrderDate = g.Max(v=>v.OrderDate)})
            on new { o1.CustomerId, o1.OrderDate}  equals new { o2.CustomerId, o2.OrderDate }
        select o1;
var results = q.ToList();

Notice the same column names in the elements in the on clause in the join. If the names differ, the code won't compile as the anonymous types differ and the C# compiler gives up.

I don't know the specifics of your table, so you might need to adjust it here and there. It helps if you write the query out in sql first in this case, as it's not trivial, plus not all constructs to solve this problem in SQL are available in linq (they are in queryspec, our other high level query system btw).

Btw, you're using an old runtime/version, it's recommended you upgrade to a later runtime if possible.

mprothme avatar
mprothme
User
Posts: 34
Joined: 05-Oct-2017
# Posted on: 17-Sep-2019 19:25:48   

Sorry, for clarity a point is the data structure we use to store prices.

The structure of the point table (called curve point) is as follows:

Curve Point Table (stores prices):

CurvePointId: int, identity, primaryKey
EffectiveFromDateTime: DateTime
EffectiveToDateTime: DateTime
PriceValue: Decimal
CurveId: int, ForeignKey to Curve Table

Curve Table (Groups Prices):

CurveId: int, identity, primaryKey
CurveName: varchar
Description: varchar
ProductId: int, ForeignKey to Product Table

The sql query I'm trying to replicate is as follows:

SELECT *
FROM CurvePoint point
INNER JOIN (SELECT MAX(maxPoint.EffectiveFromDateTime) MaxDate, maxPoint.CurveId
            FROM CurvePoint maxPoint GROUP BY maxPoint.CurveId) AS MaxEffective 
            ON point.CurveId= MaxEffective.CurveId
            AND point.EffectiveFromDateTime = MaxEffective.MaxDate

From what you're saying though this type of query may be something I won't just be able to use an IQueryable<CurvePointEntity> with fluent syntax (method chaining) to solve?

daelmo avatar
daelmo
Support Team
Posts: 8152
Joined: 28-Nov-2005
# Posted on: 18-Sep-2019 08:43:56   

mprothme wrote:

The sql query I'm trying to replicate is as follows:

SELECT *
FROM CurvePoint point
INNER JOIN (SELECT MAX(maxPoint.EffectiveFromDateTime) MaxDate, maxPoint.CurveId
            FROM CurvePoint maxPoint GROUP BY maxPoint.CurveId) AS MaxEffective 
            ON point.CurveId= MaxEffective.CurveId
            AND point.EffectiveFromDateTime = MaxEffective.MaxDate

Your query is very similar to Frans example. Give it a try using your own object names.

mprothme wrote:

From what you're saying though this type of query may be something I won't just be able to use an IQueryable<CurvePointEntity> with fluent syntax (method chaining) to solve?

You can use the fluent syntax (LINQ extension methods) as well:

priceQueryable
.Join(...
.GroupBy(
.Select( 

Although in these complex cases I think it's more redeable the Linq query syntax.

mprothme avatar
mprothme
User
Posts: 34
Joined: 05-Oct-2017
# Posted on: 07-Oct-2019 22:58:09   

Awesome, thank you so much for your help!