CROSS APPLY causing error

Discussion of open issues, suggestions and bugs regarding ADO.NET provider for PostgreSQL
Post Reply
peterharris
Posts: 13
Joined: Fri 03 Jan 2014 11:13

CROSS APPLY causing error

Post by peterharris » Mon 22 Feb 2016 12:35

I'm not sure if this is a LinqConnect issue or dotConnet for PostgreSQL, but this error is caused while using LinqConnect with PostgreSQL. It seems to be generating SQL that utilizes "CROSS APPLY" syntax which doesn't exist in PostgreSQL. Here are steps that can be taken to reproduce the error:

Given the following example database structure:

Code: Select all

Tree    1 -> *    Branch
Branch  1 -> *    Leaf
Leaf    1 -> 0-1  LeafFallDetails
This following LINQ query should produce the SQL syntax error by trying to use "CROSS APPLY" in PostgreSQL. I've not tested if the error still occurs if you don't project a set of entities to a new anonymous object while querying a nested set of entities, I'd imagine that it would, but I have only tested the following scenario:

Code: Select all

dim result = context.Trees.Select(Function(t) New With {
    .Tree = t,
    .FallenLeaves = t.Branches.SelectMany(
        Function(b) b.Leaves.Where(
            Function(l) l.LeafFallDetails IsNot Nothing
        )
    )
}).ToList()

As a side note, sometimes I also seem to get a weird error when projecting to an anonymous type like in the above where I get an exception of something like:

Code: Select all

Unable to cast object of type 'CacheStorageReader[VB$AnonymousType_0`2[Data.Entities.Tree,System.Collections.Generic.IEnumerable`1[Data.Entities.Leaf]]]' to type 'Devart.Data.PostgreSql.PgSqlDataReader'.
When using the following query with the example database structure:

Code: Select all

dim result = context.Trees.Select(Function(t) New With {
    .Tree = t,
    .Leaves = t.Branches.SelectMany(
        Function(b) b.Leaves
    )
}).ToList()
If this should be in the LinqConnect forum, then I can repost there instead if that helps.
Many thanks for your help,

Peter

MariiaI
Devart Team
Posts: 1472
Joined: Mon 13 Feb 2012 08:17

Re: CROSS APPLY causing error

Post by MariiaI » Tue 23 Feb 2016 09:46

Thank you for the report on this. We have reproduced this issue. We will investigate it more clearly and inform you about the results as soon as possible.

peterharris
Posts: 13
Joined: Fri 03 Jan 2014 11:13

Re: CROSS APPLY causing error

Post by peterharris » Wed 02 Mar 2016 09:29

It's been a over a week and I was wondering if there has been any updates regarding this issue?

Many thanks,

Peter

MariiaI
Devart Team
Posts: 1472
Joined: Mon 13 Feb 2012 08:17

Re: CROSS APPLY causing error

Post by MariiaI » Wed 02 Mar 2016 10:47

The investigation is in progress. We will definitely inform you about the results as soon as any are available.

peterharris
Posts: 13
Joined: Fri 03 Jan 2014 11:13

Re: CROSS APPLY causing error

Post by peterharris » Wed 31 Jan 2018 12:30

It's been almost two years. Is there any more info on this?

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

Re: CROSS APPLY causing error

Post by Shalex » Tue 13 Feb 2018 19:49

1. The generation of CROSS JOIN LATERAL and LEFT JOIN LATERAL when connecting to PostgreSQL Server 9.3 or higher is supported. The functionality will be included in the next public build.
peterharris wrote:Unable to cast object of type 'CacheStorageReader[VB$AnonymousType_0`2[Data.Entities.Tree,System.Collections.Generic.IEnumerable`1[Data.Entities.Leaf]]]' to type 'Devart.Data.PostgreSql.PgSqlDataReader'.
2. We cannot reproduce the error. Please upload a test project with the corresponding DDL/DML script for reproducing to ftp://ftp.devart.com (credentials: anonymous / yourEmail).

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

Re: CROSS APPLY causing error

Post by Shalex » Thu 15 Feb 2018 17:51

New build of dotConnect for PostgreSQL 7.10.1086 is available for download now: viewtopic.php?f=3&t=36600.

peterharris
Posts: 13
Joined: Fri 03 Jan 2014 11:13

Re: CROSS APPLY causing error

Post by peterharris » Wed 21 Feb 2018 09:59

Thank you for your reply.

Interestingly, you were able to reproduce the error 2 years ago, which makes me think that the error is now resolved in the latest build.
I will download the latest and try with the original project to see if it produces the same error again, and if it doesn't then I'd say this is resolved. If the error still persists then I will reply here and try to put an example project together.

Many thanks,

Peter

Post Reply