exception in linq select latest records

[ssnz]

Hi all,

I'm using windows 10 version 1803, Entity Developer 6.3.555, Postgres 10.4 to develop a c# desktop app.

I have a table named prices with columns; partno (var char), timecode (timestamptz), wholesale (double precision).
This table contained the price for each part, and the date that the price was applied. For example:
PartNo TimeCode Wholesale
a1 24/01/2016 $10
a1 24/01/2017 $11
a2 24/01/2016 $20
a3 24/01/2016 $39
a3 24/01/2019 $40

Suppose I'd like to extract the prices as of today: 27/08/2017, then I want the records:
PartNo TimeCode Wholesale
a1 24/01/2017 $11
a2 24/01/2016 $20
a3 24/01/2016 $39


This linq query ought to do it:
pricesToDate = context.Prices.GroupBy(n => n.Partno).Select(g => g.OrderByDescending(t => t.Timecode).First());
but this generates an exception:

"Error on executing DbCommand."
Devart.Data.Linq.LinqCommandExecutionException
"syntax error at end of input"
ErrorSql:
SELECT t3.partno, t3.timecode, t3.wholesale
FROM (
SELECT t2.partno AS "Partno"
FROM prices t2
GROUP BY t2.partno
) t1
LEFT JOIN LATERAL (
SELECT t4.partno, t4.timecode, t4.wholesale
FROM prices t4 WHERE t1."Partno" = t4.partno ORDER BY t4.timecode DESC LIMIT 1 ) t3

This query also generates an error when run in a query window. It can be fixed by adding "On True" to the end of the query, or by changing "LEFT JOIN LATERAL" to "CROSS JOIN LATERAL".

Can the linq query be written to generate one of these two working sql queries, or is this a bug?


Thanks

[Shalex]

We are processing your request.

[Shalex]

We have reproduced the issue and are investigating it. We will notify you about the result.

[Shalex]

The bug with applying LEFT JOIN LATERAL in the generated SQL is fixed: viewtopic.php?f=3&t=37713.