exception in linq select latest records

Discussion of open issues, suggestions and bugs regarding ADO.NET provider for PostgreSQL
Post Reply
ssnz
Posts: 18
Joined: Mon 20 Aug 2018 21:15

exception in linq select latest records

Post by ssnz » Mon 27 Aug 2018 02:33

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
Site Admin
Posts: 8369
Joined: Thu 14 Aug 2008 12:44

Re: exception in linq select latest records

Post by Shalex » Fri 31 Aug 2018 19:09

We are processing your request.

Shalex
Site Admin
Posts: 8369
Joined: Thu 14 Aug 2008 12:44

Re: exception in linq select latest records

Post by Shalex » Mon 03 Sep 2018 14:45

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

Shalex
Site Admin
Posts: 8369
Joined: Thu 14 Aug 2008 12:44

Re: exception in linq select latest records

Post by Shalex » Thu 20 Sep 2018 15:06

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

Post Reply