Page 1 of 1

exception in linq select latest records

Posted: Mon 27 Aug 2018 02:33
by 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

Re: exception in linq select latest records

Posted: Fri 31 Aug 2018 19:09
by Shalex
We are processing your request.

Re: exception in linq select latest records

Posted: Mon 03 Sep 2018 14:45
by Shalex
We have reproduced the issue and are investigating it. We will notify you about the result.

Re: exception in linq select latest records

Posted: Thu 20 Sep 2018 15:06
by Shalex
The bug with applying LEFT JOIN LATERAL in the generated SQL is fixed: viewtopic.php?f=3&t=37713.