Home
Help
Register
Log in

Search

 
   Active Threads  

You are here: Home > LLBLGen Pro > LLBLGen Pro Runtime Framework> 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.
 

Pages: 1
LLBLGen Pro Runtime Framework
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.
Page:1/1 

  Print all messages in this thread  
Poster Message
mprothme
User



Location:
Fayetteville
Joined on:
05-Oct-2017 21:57:29
Posted:
21 posts
# 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:

Code:

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:
Quote:
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:
Code:
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!
  Top
Otis
LLBLGen Pro Team



Location:
The Hague, The Netherlands
Joined on:
17-Aug-2003 18:00:36
Posted:
37645 posts
# 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:
Code:
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
Code:

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.


Frans Bouma
LLBLGen Pro / ORM Profiler Lead Developer | Blog | Twitter
 
Top
mprothme
User



Location:
Fayetteville
Joined on:
05-Oct-2017 21:57:29
Posted:
21 posts
# 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):
Code:
CurvePointId: int, identity, primaryKey
EffectiveFromDateTime: DateTime
EffectiveToDateTime: DateTime
PriceValue: Decimal
CurveId: int, ForeignKey to Curve Table


Curve Table (Groups Prices):
Code:
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:
Code:
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?
  Top
daelmo
Support Team



Location:
Guatemala City
Joined on:
28-Nov-2005 23:35:24
Posted:
8088 posts
# Posted on: 18-Sep-2019 08:43:56.  
mprothme wrote:

The sql query I'm trying to replicate is as follows:
Code:
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:
Code:
priceQueryable
.Join(...
.GroupBy(
.Select(

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


David Elizondo
LLBLGen'ing (articles and code snippets) | linkedin | twitter
 
Top
mprothme
User



Location:
Fayetteville
Joined on:
05-Oct-2017 21:57:29
Posted:
21 posts
# Posted on: 07-Oct-2019 22:58:09.  
Awesome, thank you so much for your help!
  Top
Pages: 1  


Powered by HnD ©2002-2007 Solutions Design
HnD uses LLBLGen Pro

Version: 2.1.12172008 Final.